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.

1 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.

  • Right, I plan to script this off eventually, but right now I'm just trying to find out how to do it. When you say to SELECT FROM other_table_name, are you referring to the geometry table created in the schema that contains the various geometry items that go into each saved item? Basically, I'm trying to figure out that SELECT statement. As far as i can tell, it just gets saved in as a collection of geometry objects into a table and schema which is then saved in the geometry schema. I found the unions, differences, etc. utility subs, but I'm not sure how I SELECT the saved geometry to use it.
    – Oesor
    Mar 5, 2012 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.