4

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?

7

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.

  • Thank you! I followed an tutorial from boundless where they put the geometry to compare directly in the ST_Within Forgot that it is just a column which can be joined/from... Is there any significant speed difference between the join and the first? – stUrb Nov 18 '13 at 15:26
  • I recall that they are similar in efficiency, but check the query planner. Joins can make things tidyer when more than two tables are joined together, so I normally prefer this style. – Mike T Nov 18 '13 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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