Tag: ubuntu

My goal for today was to populate a record with all of the Australian postcodes and their corresponding suburb and state descriptions. To do this I had to complete the following:

• 1: Download the postcode/location csv
• 2: Create new model to contain locations
• 3: Create a script to import a csv
• 4: Run the script in order to populate the database
• 5: Cleanse the data

Step #1: Download the .csv
I came across the following files after browsing a few of the Australian tech forums. I chose to use the Corra ones due to the claim that there are no license restrictions.

• Corra.com also provides a few PHP function templates for tasks such as distance estimation. I’m sure these could quite easily be converted to rails for anyone who’s chasing something similar.
  http://www.corra.com.au/downloads/Australian_Post_Codes_Lat_Lon.zip
• If for some reason you’d prefer to use an Australia Post .csv, they’re available at the following link:
  http://auspost.com.au/products-and-services/download-postcode-data.html

Once you’ve chosen your .csv, just save it in your project folder i.e. C:my_app

Step #2: Create a Model for your Locations
Secondly, you’ll need to create a new migration for your locations. I’ve simply called mine Code:

def change
      create_table :codes do |t|
        t.string "Pcode"
        t.string "Locality"
        t.string "State"
        t.string "Comments"
        t.string "DeliveryOffice"
        t.string "PresortIndicator"
        t.string "ParcelZone"
        t.string "BSPnumber"
        t.string "BSPname"
        t.string "Category"
        t.timestamps
     end
end

Step #3: Create a Script to Import the .csv
The third step is to create a script that allows you to import the csv. This is made simple thanks to a comment left by “Gianluca” on the following blog post:
http://erikonrails.snowedin.net/?p=212

Simply create a new file within your tasks folder and add the following code:

#my_app/lib/tasks/import.rake

require "csv"
desc "Import CSV file into an Active Record table"
task :csv_model_import, [:filename, :model] => :environment do |task,args|
  firstline=0
  keys = {}

  CSV.foreach(args[:filename]) do |row|
    if (firstline==0)
      keys = row
      firstline=1
      next
    end

    params = {}

    keys.each_with_index do |key,i|
      params[key] = row[i]
    end

    Module.const_get(args[:model]).create(params)
  end
end

Step #4: Run the script in order to populate the database
To run the script simply run the following command:

chris@chris-VirtualBox:~/my_app$ rake csv_model_import[codes.csv,Code]

Step #5: Cleanse the data
Cleansing the data is a little tedious, however one tip is to remove all locations that do not have a category value of “Delivery Area”.

Ahwell, that’s all I’ve got for now – let me know if you have any trouble.