Australian suburb boundaries database released

Hello Its been a great pleasure for me to release Australian suburb boundaries database, A suburb boundary is represented in a polygon of longitude and latitude points. More details can he found on product page which is located here Product will have two files mySQL inserts CSV file for a quick import Database schema is shown below Columns: city and org_name pretty much has same values, use city column value for corrected spelling mistakes. Polygons Data is represented in polygons. In above picture, boundaries column will have polygon data for a suburb, if there are mulitple boundaries for a suburb then boundaries column will have mulitple polygons data delimited by a semicolon (;) As you see that there is no country field because this is not a multi country suburb boundary database and only applies to Australia. Currently there are over 7899+ records in this database covering major suburbs in all different states. However more will be added and will be released as upgrades priced around $99 for each upgrade and each upgrade will have at least 100 records. Download size is around 100MB.  Very important Download demo data below   If you don't like the demo data, Do Not Buy this product, because NO REFUNDS are given in case of accidental purchase syndrome or I didn't like it syndrome Corrections If you find any issue please let me know by contacting me I will sure fix it up in next version. Versioning This product is not versioned, what you download is the latest version at the time of download. however Upgrades are sold separately and will be included on this page as a reference when they are available. Its a good idea if you end up purchasing this product, bookmark this page to check if there are any upgrades available Release date First release of this database is: 16th July 2014 Upgrades None just yet. Current status. Upgrade due around 4 weeks time. KMLs Please let me know if you want a KML … [Read more...]

Installing Apache thrift, Cassandra 2.x and YACassandra-PDO on CentOS 6.5+

Hello, Well after a long time this is my in length article, the reason I am writing is because of all the stuff I have been gone through building thrift and cassandra-pdo on centOS 6.5 Versions I will be writing about installing latest version of thrift that at time of writing and RnD was 0.9.1, CentOS is version 6.5 64 bit, php 5.3.3,  Apache/2.2.15, Cassandra is at version 2.06 which is most stable version for production at the time of writing.   Prerequisite Most of the commands will need elevated privileges. You should prefix sudo with every command, I am doing it on test VM so doing it as privileged user root Packages wget, nano are available if not then yum install them as shown below. Of course if you use vi or other util other than wget to download files then skip this [crayon-53d2d758a2195100979233/] Ok so lets get started. When I started building thrift, or cassandra pdo I was getting all sort of errors such as [crayon-53d2d758a21ad231892827/]   Install Cassandra Installing cassandra is the simplest one, We will be installing it from rpm provided by datastax Open datastax.repo for editing with following command [crayon-53d2d758a21bf900113182/] paste following content in editor [crayon-53d2d758a21cf862251419/]  Install cassandra [crayon-53d2d758a21e0308312949/] Above command will also install openJDK but cassandra guys recommend that we should use latest java from Oracle so lets do that now Get into opt directory You can actually choose to be any other directory of your liking [crayon-53d2d758a21f0609063218/] Get RPM path for latest jre  from http://www.oracle.com/technetwork/java/javase/downloads/index.html I am using jre 8 from http://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html   [crayon-53d2d758a2201096658974/] output of java --version would be  something like [crayon-53d2d758a2213911380054/] Lets now install Development … [Read more...]

New Zealand Street Names Database along with Suburb, Postcode, District, Longitude and Latitude value

Hi Guys, I just introduced a new Product in Database category that enables you to store New Zealand Street names and other details in your database so that you can help you users filling their information on Web forms etc. Benefits The 3 main benefits of such database You can pin point a street address on Google Maps, Open Street maps or Bing Maps Site Usability: You can help your visitors to fill their street addresses correctly. Even though this database does not have the Street numbers but other details offered through autofill could be beneficial in getting a correct address from User. It can also be used for calculating distance from one street to another. If you have access to some Places database you can then you are able to draw proximity POI around the Streets for New Zealand. Those are the uses that I can think of but there are possible a lot of stuff that you can do once you have longitude and latitude values for an Address. What do you get once you purchase You will be able to download an archive, which once extracted will have A CSV file A mySQL file with INSERT Queries A License file Pricing and Purchase   Format Below schema table shows format of data NO WARRANTIES. THE Database(s), DATA AND PROGRAMS ARE FURNISHED ON AN "AS IS", AS-AVAILABLE BASIS. Jaspreet Chahal MAKES NO WARRANTY, EXPRESS OR IMPLIED, WITH RESPECT TO THE CAPABILITY OF THE Database(s), DATA AND PROGRAMS OR THE ACCURACY OR THE COMPLETENESS OF THE DATA. ALL WARRANTIES OF ANY TYPE, EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT OF THIRD PARTY RIGHTS ARE EXPRESSLY DISCLAIMED. Jaspreet Chahal DOES NOT WARRANT THAT THE SERVICES WILL MEET ANY PARTICULAR CRITERIA OF PERFORMANCE OR QUALITY, OR THAT THE SITE IS FREE OF OTHER HARMFUL COMPONENTS. NEVERTHELESS, Jaspreet Chahal SHALL MAKE COMMERCIALLY REASONABLE EFFORTS TO MAINTAIN THE SITE FREE OF VIRUSES AND MALICIOUS CODE. Demo … [Read more...]

Councils in Western Australia A Local Government Database

Hi Guys, This is a follow up post to the last post and there will be another 5 of these. If you haven't read my previous post or want to downloads Council names for New South Wales then you should read this post http://jaspreetchahal.org/council-local-government-database-for-new-south-wales-australia/ I am offering these dbs for FREE and a donation will be much appreciated. I am thinking of launching a commercial database as well with Council Addresses and Longitude and Latitude values so let see how I go with that. First lets check the schema Schema     So, what will you be downloading? A zip file that has the following CSV files Countries  (For this data only Australia is listed) States (Australia States) Local Govs for Western Australian License Data source i.e. http://jaspreetchahal.org must be mentioned on your website Copyright (C)  Jaspreet Chahal Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. Download Compilations for other states are … [Read more...]

Council Local Government Database for New South Wales Australia

Hi Guys, While working on a project I have to create a Auto Completion box that will auto fill the Local Government Body name for States in Australia, While I live in Melbourne I would just know about Darebin Council and Whittlesea at most so I did some research and found that I was missing on over 450+ :) Now I will share each of them with you in different posts but lets check on the schema diagram to what I will be offering for FREE and a donation will be welcome. I am thinking of launching a commercial database as well with Council Addresses and Longitude and Latitude values so let see how I go with that. Schema     So what can you download A zip file that has the following CSV files Countries  (For this data only Australia is listed) States (Australia States) Local Govs License Data source i.e. http://jaspreetchahal.org must be mentioned on your website Copyright (C)  Jaspreet Chahal Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE … [Read more...]

How to remove duplicate rows based on duplicate values of column in mySQL

Hi Guys, The quickest one is to write a query that does that or else write a script that will remove your duplicates. Lets check on how we can achieve this using a query and introducing a UNIQUE index on our solumn Say for example I have a table as shown below And I have data such as the one shown below (lat, lng columns removed) id street_name city_id state_id display_name last_modified_on 1434419 Moorefields Rd 224 2 Moorefields Road, Sydney NSW 2209, Australia 1359946138 1434420 Kingsgrove Rd 701 2 Kingsgrove Road, Clemton Park NSW 2206, Australia 1359946139 1434421 Dewar St 13260 8 Dewar Street, Armadale WA 6112, Australia 1359946140 1434423 Dewar St 13151 8 Dewar Street, Morley WA 6062, Australia 1359946142 1434424 Dewar St 9793 4 Dewar Street, Point Arkwright QLD 4573, Australia 1359946143 1434425 Dewar St 13929 8 Dewar Street, Lake Grace WA 6353, Australia 1359946144 1434426 Dewar St 681 2 Dewar Street, Campsie NSW 2194, Australia 1359946145 1434428 Dewar St 10990 4 Dewar Street, Mission Beach QLD 4852, Australia 1359946147 1434429 Anglo Rd 342 2 Anglo Road, Greenwich NSW 2065, Australia 1359946148 1434430 Anglo Rd 681 2 Anglo Road, Campsie NSW 2194, Australia 1359946150 1434445 Anglo Rd 681 2 Anglo Road, Campsie NSW 2194, Australia 1359946150 1434450 Anglo Rd 681 2 Anglo Road, Campsie NSW 2194, Australia 1359946150 As you can see that entry Anglo Road, Campsie NSW 2194, Australia is repeated three times and data other than my row ID is pretty much the same so how do I get rid of duplicates Here is a quick query to do that   mySQL query [crayon-53d2d758a5445843202843/] This will remove my duplicates rows. Most important part of above query is using ALTER with IGNORE More details can be found here But to wrap things up this here is a quick explanation from above … [Read more...]

PHP Timezones select box

Hey guys, Just in case you need a list with timezones with GMT values, here is what I've compiled from various places. I hope that it will be helpful. Just in case if I missed putting a timezone up please leave your comment and I'll update this list.   [crayon-53d2d758a675e108023804/] Or you can have this as strings [geshi lang="html5" nums="1" target="_self" ] [crayon-53d2d758a6791782520992/]   By the way if you are using mySQL as your database then you can download timezones from the link below http://dev.mysql.com/downloads/timezones.html If you operating system is either of these then it does have zoneinfo available Linux FreeBSD Sun Solaris Read this article to import zoneinfo into mysql tables http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html I hope this helps Cheers … [Read more...]

mySQL Export select to CSV File Example

Hi Guys, Just a quick tip here I thought you could be interested in knowing something like this. If you are using likes of SQLYog then you use its feature called Export to CSV. You can do pretty much same thing with native select command too lets see how you do it Consider the following table structure Table Name: States Field Type state_id int(5) unsigned NOT NULL state_code varchar(5) NOT NULL state_name varchar(50) NOT NULL country_id int(2) NOT NULL Now a simple select will be like select * from States; [crayon-53d2d758a81df367592821/] Above will print resultset on mysql console. Now lets guide the resultset to a CSV file Change above command to look like tihs select * from States INTO OUTFILE '/path/to/states.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; The CSV file will look like this for my above command. (first 6 rows)   Your select can have any number of custom fields. It doesn't matter at all. That's it now the result will be copied to your CSV file. Its pretty straight forward what's shown above. We are instructing mySQL to product a CSV equivalent of the Selected result. To know more about OUTFILE please look into this documentation page http://dev.mysql.com/doc/refman/5.0/en/select-into.html   I hope this helps Cheers, Advertisement     … [Read more...]

mySQL CSV to Database Table using SQLYOG

Hi Guys, Today I am gonna show you one really cool feature of SQLYOG, a must have software for mySQL administrator or users. We will be covering how we can import a CSV into a database and create a table automatically. You can rename the table after the import if you like. I now can't think of my life without SQLYOG. All right lets get started. First Fire up SQLYog, have your CSV file ready I believe you are connected to your mysql database where you would like to import your CSV (A connection is not required as such but I always want to see what available before I make a call to import ) Below is what I am going to import I created a folder called importfolder and pasted CSV to be importent in there to keep it simple. There is no limit however you can use any URL. This is me who does few things like that to make my life easy so that I know what I am doing. Ok so Goto Powertools > Import External Data You will get this screen Now Keep "Start New Job" selected and click Next. After you click Next you will see the screen below. Keep the CSV option checked and browse to your CSV file. Here is the real deal. you can actual import more than 1 CSV in one go and convert them to a mySQL table. Ok so Click Next and you will see the screen below Now make sure that you've chosen the right connection and you select the right database. It does not really matter because you are creating something not dropping anything, but I always recommend that you should know where the changes are being made. So with the database selected Click Next Below is the screen that will show up This is important, Now because we are importing our CSV to create a table we will keep the first option checked. If you select the second option you will be asked to input a query that will be used to import or select specific data to be imported. With the first option checked click Next. You will see the screen below This is … [Read more...]

Australian Street Types and Abbreviations database

Hi Guys, So while working on my current project I had a situation where I needed street types in Australia. I end up compiling a list and thus I am sharing that with you guys, Data has been compiled from these resources Wikipedia Australian Street Directory Open Street Maps Below is the list If you would like to just copy it STREET … [Read more...]