I can see my house from here - Part 1

On a recent project, I was looking at Geocoding with Google Maps.

Searching with a simple string would sometimes produce odd results, like places in America when I really wanted one in Australia, or one in Victoria when I expected it in Sydney. Looking further, I've found that you can tell Google Maps to limit its results to a specific 'box' specified by latitude and longitude. The trick is knowing what latitude and longitude to use as the limits.

In Australia, we can get boundaries from the Australian Bureau of Statistics here. It's a few years out of date, but will be sufficiently accurate for most purposes. The drawback is that this is in a shapefile (which is actually a set of files - go figure).

But a Googlehunt directed me to the GDAL/OGR, which can convert shapefiles into Oracle spatial/locator geometries. I downloaded the source to my ubuntu box (which has the database installed), then uncommented and set the line in nmake.opt regarding ORACLE_HOME and installed it (don't forget to set the LD_LIBRARY_PATH).

Unzip the the data files, and gdal/ogr can load the shape files straight into the database, even creating the table. That's the ease of use I like.
ogr2ogr -f OCI OCI:dev/[email protected]/db11r1 SSC06aAUST_region.shp ogr2ogr -f OCI OCI:dev/[email protected]/db11r1 POA06aAUST_region.shp

From there, I want to summarize up a 'box' for each postcode/suburb/state that tells me the outer limits of the area. Luckily for me, Australia is positioned such that it is pretty easy to determine whether a value is a latitude or longitude.

create table aus_postcode as
 select a.poa_2006,
       decode(state_2006 ,1,'NSW',2,'VIC', 3, 'QLD',
             4,'SA', 5,'WA', 6,'TAS',7,'NT',8,'ACT','Other') state,
       min(case when b.column_value between 80 and 160 then b.column_value end) west_longitude,
       max(case when b.column_value between 80 and 160 then b.column_value end) east_longitude,
       min(case when b.column_value between -50 and -20 then b.column_value end) south_latitude,
       max(case when b.column_value between -50 and -20 then b.column_value end) north_latitude
 from POA06AAUST_REGION a,  table(a.ora_geometry.SDO_ORDINATES) b
 group by a.poa_2006, state_2006;

create table aus_localities as
 select a.name_2006 locality, ssc_2006 locality_number,
        decode(state_2006 ,1,'NSW',2,'VIC', 3, 'QLD',
                    4,'SA', 5,'WA', 6,'TAS',7,'NT',8,'ACT','Other') state,
        min(case when b.column_value between 80 and 160 then b.column_value end) west_longitude,
        max(case when b.column_value between 80 and 160 then b.column_value end) east_longitude,
        min(case when b.column_value between -50 and -20 then b.column_value end) south_latitude,
        max(case when b.column_value between -50 and -20 then b.column_value end) north_latitude
 from SSC06aAUST_region a,  table(a.ora_geometry.SDO_ORDINATES) b
 group by a.name_2006, ssc_2006, state_2006;

create table aus_states as
 select state, min(west_longitude) west_longitude, max(east_longitude) east_longitude,
        min(south_latitude) south_latitude, max(north_latitude) north_latitude
 from aus_localities
 group by state;

So now, before I geocode an address with suburb, postcode or state, I can query the bounding box and pass it to Google to get a more accurate answer. For an example of the code for this, look at the util_geocode package here. You'll need to plug in your own Google Maps API key before using it. Also, if you want to borrow the code for parsing the returned KML/XML, you may need to make adjustments for the address fields returned for searches in your country of interest.