I've got two tables with geometry in it.
items holds items with a point geometry; while the second table
property_zones holds polygons.
CREATE TABLE public.items ( id integer NOT NULL DEFAULT nextval('item_id_seq'::regclass), .... geom geometry(Point,4326), .... )
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?