1

I'd like to perform a select by location analysis in PostGIS that first selects the geometry of ONE point out of a table (of many points). Creates then a circumference around it and checks which geometries (polygons) of a second table lie within that circumference.

I came up with this but am somehow stuck..

SELECT t2.name, t2_area
FROM ( SELECT geom
       FROM views.sites
       WHERE site_id LIKE 'AB_10003' ) t1, spatial.land_use t2
WHERE ST_DWithin(t1.geom, t2.geom, 1000) -- radius of 1000m

the SRID of my geometries is 31467. Any help?

  • how are you stuck? Are you getting an error? – kttii Aug 2 '16 at 16:00
  • One immediate observation is that LIKE 'AB_10003' will only match AB_10003, as there is no wildcard, %, so it is possible that your subquery, t1, returns nothing? Hard to know, as somehow stuck isn't wildly informative. – John Powell Aug 2 '16 at 18:01
  • OK. Sry, I just realized that my t2.geom has 0 as SRID value. – andrasz Aug 2 '16 at 19:40
  • So problem solved? – John Powell Aug 2 '16 at 20:40
  • yes, problem solved – andrasz Aug 8 '16 at 9:01
2

While your SQL looks logically correct, it's not well formed. You are thinking procedurally, and you should think more schematically. In "correct" SQL your logic would be:

SELECT sl.name, sl.area
FROM views.sites vs
JOIN spatial.land_use sl
ON ST_DWithin(vs.geom, sl.geom, 1000)
WHERE vs.site_id = 'AB_10003'

Use "join" clauses for conditions that use values from two tables, and put other conditions into the "where" clause, and then let the database sort out the most efficient execution path: it will do better than you.

  • thanks for the input! Since I have selected only one row (one site_id) of the vs table, I can't add additional columns of it referring to other site_ids (sl.site_id) than 'AB_10003'. Is there a possibility to use 'AB_10003' only for the ST_DWithin() - argument and also add a JOIN argument like vs.site_id = sl.site_id? E.g. adding a column vs.stream that contains the stream name of each site_id that was selected – andrasz Aug 8 '16 at 9:29
  • I wrapped the query above in another query and joined the vs table again to be able to get the stream names for each site_id. – andrasz Aug 8 '16 at 10:49

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.