Postcode from latitude and longitude or even IP – fun with Geo APIs and YQL

One of the more complex things about GeoFill was to get postcode information from an IP. However with a collection of APIs and a collated YQL statement even this was possible.

The first thing I needed to get was the IP of the user. This is done with the GeoIP API based on the GeoLite API from MaxMind. This is available as an open table in YQL and can be used thus:

select * from ip.location where ip=""

Try the lookup in the console or check the lookup result

Response": {
"Ip": "216.39.58.17",
"Status": "OK",
"CountryCode": "US",
"CountryName": "United States",
"RegionCode": "06",
"RegionName": "California",
"City": "Sunnyvale",
"ZipPostalCode": "94089",
"Latitude": "37.4249",
"Longitude": "-122.007",
"Gmtoffset": "-8.0",
"Dstoffset": "-7.0"
}

This gives us a lot of information. What’s really important here is latitude and longitude, as this can be used in the flickr.places API to get a where on earth ID which is a much more defined identifier:

select * from flickr.places where (lat,lon) in (
select Latitude,Longitude from ip.location where ip=""
)

Try the flickr places call in the console or check the flickr result

"places": {
"accuracy": "16",
"latitude": "37.4249",
"longitude": "-122.007",
"total": "1",
"place": {
"latitude": "37.371",
"longitude": "-122.038",
"name": "Sunnyvale, California, United States",
"place_id": "P_ls_fybBJwdHP8t",
"place_type": "locality",
"place_type_id": "7",
"place_url": "/United+States/California/Sunnyvale",
"timezone": "America/Los_Angeles",
"woeid": "2502265"
}
}

Here the interesting part is the woeid which we can use to dig deeper into geo.places:

select * from geo.places where woeid in (
select place.woeid from flickr.places where (lat,lon) in (
select Latitude,Longitude from ip.location where ip=""
)
)

Try the geo places call in the console or check the geo places result

The result is all the information you’d ever want.

"place": {
"lang": "en-US",
"xmlns": "http://where.yahooapis.com/v1/schema.rng",
"yahoo": "http://www.yahooapis.com/v1/base.rng",
"uri": "http://where.yahooapis.com/v1/place/28751237",
"woeid": "28751237",
"placeTypeName": {
"code": "22",
"content": "Suburb"
},
"name": "Fairgrounds",
"country": {
"code": "US",
"type": "Country",
"content": "United States"
},
"admin1": {
"code": "US-CA",
"type": "State",
"content": "California"
},
"admin2": {
"code": "",
"type": "County",
"content": "Santa Clara"
},
"admin3": null,
"locality1": {
"type": "Town",
"content": "San Jose"
},
"locality2": {
"type": "Suburb",
"content": "Fairgrounds"
},
"postal": {
"type": "Zip Code",
"content": "95112"
},
"centroid": {
"latitude": "37.326611",
"longitude": "-121.878441"
},
"boundingBox": {
"southWest": {
"latitude": "37.275379",
"longitude": "-121.89254"
},
"northEast": {
"latitude": "37.330879",
"longitude": "-121.808723"
}
}
}

Tags: conversion, flickr, geo, geoip, hack, latlon, places, postcode, yql, zip