The conventional wisdom about PostgreSQL being better than MySQL is true. MySQL's spatial support is getting better, but PostGIS, the spatial extension for PostgreSQL, is just plain awesome for all kinds of spatial queries. As J. Heasly mentioned, GeoDjango provides a great API for doing spatial queries, but there are libraries for most languages that provide a wrapper to PostGIS that you can use without being inside a framework. If PostgreSQL isn't an option, SQLite has the SpatiaLite project, which is an excellent choice, too.

Derek Willis

305

Since version 1.3.4 MongoDB has geospatial indexing with support for near and bounds queries (circle and rectangle) like:

location = [50,50]
db.places.find( { loc : { $near : location } } )

box = [[40, 40], [60, 60]]
db.places.find({"loc" : {"$within" : {"$box" : box}}})

center = [50, 50]
radius = 10
db.places.find({"loc" : {"$within" : {"$center" : [center, radius]}}})

Maurício Maia

40

If you're married to having the data in a database (and not, say, in a desktop GIS app like ArcView or QGIS) postgis + postgresql is, as others have said, the way to go.

Check out the geodjango comparison of spatial function availability (and note footnote 7); it shows why postgis is great:

http://docs.djangoproject.com/en/dev/ref/contrib/gis/db-api/#compatibility-tables

Database backends that only support bounding box comparisons are basically useless for journalism (but, if you need to go that route here's a hack to get you there: http://www.chasedavis.com/2008/mar/17/point-poly-using-mysql-and-python/ ).

You'll also probably wanna read the postgis docs pretty closely (stuff on bostongis is pretty good too). For instance, the && operator only tests "whether the bounding box of one geometry intersects the bounding box of another." For the example you mention you probably wanna use st_contains. And, of course, if you want the output to end up on the web, you'll probably wanna look into geodjango. Google shp2pgsql, gist indexes, st_setsrid and, for python, psycopg2.

FWIW, you can do a heck of a lot with ArcView if you've got it. If you're trying to produce maps for print this is not such a bad way to go. Moreover, you can actually write python scripts to interact with ArcView directly, though the syntax for actually doing so is not intuitive.

jacobfenton

180

GeoDjango has a couple dozen ways to slice/dice the geospatial pie.

John Heasly

472

I'm sure there's about a hundred ways to do this, but I would use postgis and do something like this:

select point.id_field from point, polygon where point.the_geom && polygon.the_geom

the conventional wisdom is that postgres is ahead of mysql when it comes to spatial. I have no idea if that's true, but no reason to doubt it either.

aidianholder

40