I have been thinking to write about this subject a while back when project Spincloud was still under development. I was even thinking about making this the first post on my blog.
The idea is simple: you have location-based data (POIs for instance) stored in some database (preferably a spatial DB) and now you want to perform a select statement that will indicate the area that should include the points we want. In case of Spincloud’s weather map, we want the weather reported by the stations located within a given area determined by the Google Map viewport that the user is currently browsing.
In all my examples I’ll use SQL Spatial Extensions support, specifically MSQL spatial extensions.
Here’s a visual representation of the spatial select (the red grid is the area where we want to fetch the data):
This is quite easy to accomplish by issuing a spatial select statement on the database:
select * from POI where Contains(GeomFromText
('POLYGON ((-30 32, 30 -8, -89 -8, -89 32, -30 32))', LOCATION))
But what about selecting an area that crosses the 180 degrees longitude? Let’s say we want to select data in an area around New Zealand that starts at 170 degrees latitude and ends at -160 degrees latitude going East. The selected area will look like this:
Continue reading “Selecting location data from a spatial database”