Sybase iAnywhere SQL AAnywhere Mobile and Embedded Database

Rows and Columns


SQL Anywhere perspective on technology

header image

Reverse Geocoding in SQL Anywhere

July 28th, 2009 · No Comments

In my previous post I explained how one could create a webservice call from within SQL Anywhere to geocode addresses that might be stored in the database.
A somewhat less used, but nonetheless useful ability, is reverse geocoding. That is where, given a lattitude/longitude pair, we look up the address and other location information associated with those coordinates.

Similar to the geocoding service, Google has a reverse geocoding service. As before, we can create a procedure in SQL Anywhere to call out to this web service:

CREATE PROCEDURE GoogleReverseGeoCode( point VARCHAR(1024) )
URL 'http://maps.google.com/maps/geo?q=!point&output=json&oe=utf8&sensor=true_or_false&key=myapikey
TYPE 'HTTP'

Then we can call the webservice as follows to get the result. Once again, we will use the coordinate location of the Sybase iAnywhere building in Waterloo, Ontario, Canada:
SELECT “value” from GoogleReverseGeoCode( ’43.479143,-80.55227′ ) WITH (Attribute LONG VARCHAR, Value LONG VARCHAR) where attribute = ‘body’;

Reverse geocoding gets a little more complicated, due to the fact that the web service does not know exactly what we are looking for with those coordinates. Are we looking for an address, a city name, province/state or just the country location of the coordinate?
Because this information is not provided, we are often returned several result rows from the web service call. Unfortunately, the reverse geocode service does not have a csv output format, so we cannot use the same trick as before (see the end of the post for the complete JSON response). To deconstruct this response inside the database, we are going to have to leverage some more of the unique SQL Anywhere features to deconstruct the response into something meaningful from the database perspective. Stay tuned!

{
  "name": "43.479143,-80.55227",
  "Status": {
    "code": 200,
    "request": "geocode"
  },
  "Placemark": [ 
  {
      "id": "p1",
      "address": "400-498 Wes Graham Way, Waterloo, ON, Canada",
      "AddressDetails": {
          "Country": {
              "CountryNameCode": "CA",
              "CountryName": "Canada",
              "AdministrativeArea": {
                  "AdministrativeAreaName": "ON",
                  "SubAdministrativeArea": {
                      "SubAdministrativeAreaName": "Waterloo Regional Municipality",
                      "Locality": {
                          "LocalityName": "Waterloo",
                          "Thoroughfare":{
                              "ThoroughfareName": "400-498 Wes Graham Way"},
                              "PostalCode": {
                                  "PostalCodeNumber": "L0H"
                              }
                          }
                      }
                  }
              },
              "Accuracy": 8
      },
      "ExtendedData": {
          "LatLonBox": {
              "north": 43.4822918,
              "south": 43.4759966,
              "east": -80.5491232,
              "west": -80.5554184
          }
      },
      "Point": {
          "coordinates": [ -80.5522708, 43.4791442, 0 ]
      }
  },  
  {
      "id": "p2",
      "address": "Ontario, Canada",
      "AddressDetails": {
          "PostalCode": {
              "PostalCodeNumber": "N2L"
          },
          "Accuracy": 5
      },
      "ExtendedData": {
          "LatLonBox": {
              "north": 43.5033790,
              "south": 43.4427770,                                                                                                                                              
              "east": -80.5149010,
              "west": -80.5723490
          }
      },
      "Point": {
          "coordinates": [ -80.5494007, 43.4714875, 0 ]
      }
  }, 
  {
      "id": "p3",
      "address": "Waterloo, ON, Canada",
      "AddressDetails": {
          "Country": {
              "CountryNameCode": "CA",
              "CountryName": "Canada",
              "AdministrativeArea": {
                  "AdministrativeAreaName": "ON",
                  "SubAdministrativeArea": {
                      "SubAdministrativeAreaName": "Waterloo Regional Municipality",
                      "Locality": {
                          "LocalityName": "Waterloo"
                      }
                  }
              }
          },
          "Accuracy": 4
      },
      "ExtendedData": {
          "LatLonBox": {
              "north": 43.5310610,
              "south": 43.4329440,
              "east": -80.4670280,
              "west": -80.6261840
          }
      },
      "Point": {
          "coordinates": [ -80.5223720, 43.4651870, 0 ]
      }
  }, 
  {
      "id": "p4",
      "address": "Waterloo Regional Municipality, Ontario, Canada",
      "AddressDetails": {
          "Country": {
              "CountryNameCode": "CA",
              "CountryName": "Canada",
              "AdministrativeArea": {
                  "AdministrativeAreaName": "ON",
                  "SubAdministrativeArea": {
                      "SubAdministrativeAreaName": "Waterloo Regional Municipality"
                  }
              }
          },
          "Accuracy": 3
      },
      "ExtendedData": {
          "LatLonBox": {
              "north": 43.6895640,
              "south": 43.2668060,
              "east": -80.1876230,
              "west": -80.8690370
          }
      },
      "Point": {
          "coordinates": [ -80.5438450, 43.4757989, 0 ]
      }
  }, 
  {
      "id": "p5",
      "address": "Ontario, Canada",
      "AddressDetails": {
          "Country": {
              "CountryNameCode": "CA",
              "CountryName": "Canada",
              "AdministrativeArea": {
                  "AdministrativeAreaName": "ON"
              }
          },
          "Accuracy": 2
      },
      "ExtendedData": {
          "LatLonBox": {
              "north": 56.8523980,
              "south": 41.6763290,
              "east": -74.3393830,
              "west": -95.1559190
          }
      },
      "Point": {
          "coordinates": [ -85.3232139, 51.2537750, 0 ]
      }
  }, 
  {
      "id": "p6",
      "address": "Canada",
      "AddressDetails": {
          "Country": {
              "CountryNameCode": "CA",
              "CountryName": "Canada"
          },
          "Accuracy": 1
      },
      "ExtendedData": {
          "LatLonBox": {
              "north": 85.7918100,
              "south": 41.6763290,
              "east": -51.0535190,
              "west": -141.5379910
          }
      },
      "Point": {
          "coordinates": [ -106.3467710, 56.1303660, 0 ]
      }
  } 
  ] 
}
Be Sociable, Share!

Tags: GIS · Spatial · SQL Anywhere · Uncategorized