Running database operations against a stored GIS geometry

Question

I'm using Quantum GIS and Postgres with the GIS extensions to store shapefiles that I'd like to run point in geometry and point in union geometries against. While it's easy to save and load the vector layers to the database, I'm having problems figuring out how I can run operations against them. How do I use the database to SELECT WHERE lat/long values in another table are contained with a saved polygon geometry? How do I union a couple of geometries and use that in the WHERE clause? I'm having a lot of trouble figuring out what the schema of the saved polygons actually is.

Edit: The shapes have a number of holes in them, if it matters for performing the selects and the unions.

Answer 1

As always with GIS, there are several ways to do this. You mention you are using QGIS but also want to do the operation in the database. Well, either way is fine. In QGIS select Spatial Query from the Vector menu for your point-in-geometry query. You can also do your union operation (Vector->Geoprocessing tools->Union) creating a separate geometry (possibly as a temporary shapefile) and repeat your spatial query. This is crude but very effective (depending on how many union operations you need to do).

In the database use 'ST_Contains'. When doing this type of SELECT in the database, you need to compare one geometry against another geometry. So, your second geometry is defined by another SELECT statement with a 'FROM other_table_name' comment in there somewhere.

For your point-in-union use ST_Union to create the comparison geometry.

The holes shouldn't make any difference. They are handled by the magic of spatial enablement :)

Finally, there is a third way, and that is to write a script to do this. If you need to do it a lot it could be useful and I personally find it often a bit clearer than nesting SQL statements.