For my awesome super secret web 2.0 app, I decided that I wanted to display the county that contains the point somebody clicked on using the Google Maps API.
For a while, I’ve known about PostGIS, an extension to PostgreSQL that allows all sorts of cool geographic queries. PostGIS was one of the super essential technologies of my project, but I finally got around to getting all my data working.
So, assuming you have a table loaded with the counties in the U.S., you can use a query like this to determine what county a given latitude and longitude is in:
select name from counties where within(makepoint(-93.51957321166992, 45.24467860800642), geom);
In this code, the geom column is the column containing the actual geometry of the given county. I have a table with 3489 counties in the U.S, and the query returns pretty quickly on my Powerbook. The result is:
name
----------
Hennepin
(1 row)
Getting back to geometries, you can do some pretty complicated spatial queries — union, intersection, contains, within, touches, etc — which enables you do to this kind of query. Likewise, you can also select the points of a geometry, simplify geometry using the Douglas-Peucker algorithm, which makes digesting 50 or 100 points for the outline of a county very easy for Google Maps:
Coming soon: Where to get county data, how to load it into PostGIS, and how to get it into Google Maps.