Saturday, 25 January 2020

postgis - Spatial joining syntax



A standard PostGIS point-in-polygon query might look like this. (example tutorial)


SELECT p.*,b.polyname
FROM points p, shapes s
WHERE ST_Within(p.the_geom,s.the_geom)

Imagine this was not a spatial join. Then it would look like this:


SELECT i.*,c.categoryname
FROM items i, categories c
WHERE i.zipcode = c.zipcode


Most SQL folks would advise rewriting that query using a variety of the JOIN keyword syntax:


SELECT i.*,c.categoryname
FROM items i
JOIN categories c ON i.zipcode = c.zipcode

Does PostGIS offer this sort of syntax for spatial joins? I can't find it. All I see are a lot of functions that return true or false, leading to the workaround syntax. Ideally, I would think a spatial join would look like:


SELECT p.*,b.polyname
FROM points p
JOIN shapes s
ON

p.the_geom IS WITHIN s.the_geom

Answer



To use [FULL|INNER|LEFT|RIGHT|OUTER|CROSS] JOIN syntax supported by PostgreSQL, the query would still need ST_Within(). I've never seen any spatial database use IS WITHIN, since there could be parameters for the spatial relation, such as ST_DWithin. It should look like this:


SELECT p.*
FROM points p
INNER JOIN shapes s ON ST_Within(p.the_geom, s.the_geom)

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...