Finding out where I am without an external API.

My work is incredibly interesting. I do a LOT of mapping and location software, often it’s easy to plug in to something like Google Maps who provide their awesome API for programmers to hack. But sometimes this isn’t possible.

Of of my projects involves a world map with a plot of different devices locations. However in this case, as it was run in a back end query it would not be covered by any web api’s license. My own solution was needed. The first thing I did was to get some data, a cities table with 2.6 million entries is available free on the internet, and a countries table, I modified the cities table to include the country name.

Then I simply used a nested query on the cities table.

select * from cities where (lat>4.3142004 and lat<4.7142) 
and (cities.long>-74.0833 and cities.long<-73.683304) limit 1

This effectively is a box surrounding the point I’m looking for. Note that if the box fails to find anything I can just increase the parameters and rerun the query.

You can see that the figures form a box around the point I’m looking for, in this case I did not need the address, just what country it was in. (returned the whole record for testing).

Incidentally the cities table is pretty interesting, you wouldn’t believe how many London’s there are!

The cities file can be downloaded here


About Paul C

I am a Senior Java programmer/Web Technologies, Play in a band called Mandolin Monday, I have a cat and I live on a boat.

Posted on November 16, 2011, in Howto. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: