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