[GIS] SELECT geometry FROM spaces WHERE geometry ARE WITHIN OR OVERLAP polygons FROM OTHER table

postgispostgresql

I've got two tables, one containing LINESTRINGs and one containing POLYGONEs.

I would like to select all the LINESTRINGs that are containing within the outer boundary of all the POLYGONs combined.

I'm running PostgreSQL 9.3 and PostGIS 2.1.

Is that possible from within the WHERE statement?

SELECT lines.*
FROM lines, polygons
WHERE ST_Contains(polygons.geom, lines.geom)

The subquery is completely unnecessary. Flatten the query:

SELECT i.id
FROM
  items AS i,
  property_zones AS z
WHERE prop_id = :propid AND ST_Within(i.geom, z.geom)

You could also rewrite this using a JOIN:

SELECT i.id
FROM items AS i
JOIN property_zones AS z ON prop_id = :propid AND ST_Within(i.geom, z.geom)

Be sure to also have an index on prop_id.

Instead of

ST_Intersects(ST_Buffer((p.geom::geography),2),pr.geom::geography)

you should use

ST_DWithin(p.geom::geography,pr.geom::geography,2)

ST_Buffer buffers EVERY geometry (slow) before intersecting (fast). ST_DWithin makes of use of indexes and should be much faster.

ST_DWithin — Returns true if the geometries are within the specified distance of one another