robsmart.co.uk

On a project I’m working on at the moment (Meedan) we need to be able search for content by geographic area as well as by topic …. e.g. give me all the blog articles from the Middle east that relate to the Iraq war.

Luckily we already have a means to geo-locate all this content (handy eh ? ) so we’ve assigned latitude and longitude values to each article/ blog post /conversation participant.

All thats needed then is a means to search by Geographic area.

We’re using PostgreSQL a pretty heavy weight database baring a much closer resemblance to Db2 rather than MySQL. It has some pretty neat geographic search features courtesy of the PostGIS extension. This allows you to perform searches inside lat/lon boundaries of varying complexity as well as many other neat geo functions. These boundaries are defined in a markup called Well Known Text (WKT), for our purpose we search by polygons that are made up of lat/lon points that mark out the geographic search areas, both PostGIS and DB2 spatial extender uses this format.

So that’s all the big pieces in place, the only missing piece is a set of lat/lon points that define the areas we wish to search. I may have been blind or searching for the wrong terms but i couldnt find a set of this data anywhere on the Internet (please point me to a source if you know one). As we only needed the rough continent outlines I decided it would be a fairly trivial effort to make a tool to create the sets of lat/lon points in the correct WKT format for our searches.

Basically you click around the area you want to define and the hit ‘generate polygon’ this will give a nice WKT formatted polygon ready to use in an SQL query. If you want to delete a point just click on it a second time.

At the moment you just create an area generate the polgon and do the next one.

Next up I may add some storage so that people can share these datasets with each other or come back and change them at a later date.

For now here is the mapping tool.