Postgis: find geometry within multiple polygons

Question

I've got two tables with geometry in it. Table 1:items holds items with a point geometry; while the second table property_zones holds polygons.

The Items:

CREATE TABLE public.items
(
  id integer NOT NULL DEFAULT nextval('item_id_seq'::regclass),
  ....
  geom geometry(Point,4326),
  ....
)

The zones

CREATE TABLE public.property_zones
(
  id integer NOT NULL DEFAULT nextval('property_zones_id_seq'::regclass),
  prop_id integer NOT NULL,
  ....
  geom geometry(Polygon,4326)
)

Both tables have GIST indexes on the geom column.

I now want to query these tables and find every item within the zones op a particular property (denoted with prop_id). I came up with this SQL, which works but has a flaw in it:

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

This works perfectly, but some properties have multiple zones, and off course the query within the ST_Within delivers more than 1 tuple. And of course I can return to a php solution where I split the query up in a couple of query's; but I really want a query based solution. (If possible)

How can I rewrite this code to find every item within the zones (1 or more) of each property?

Answer 1

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.