[postgis-users] Spatial join optimisation
Rhys Ickeringill
rhysi at omnilink.com.au
Thu May 12 19:19:22 PDT 2005
Hi List,
I have two large tables I wish to spatially join - one which contains
polygons, the other points of latitude and longitude. The join criteria is
on polygons containing points.
At present I have the query:
SELECT pntid, polyid
INTO PolygonsContainingPoints
FROM Polygons INNER JOIN Points
ON contains( Polygons.wkb_geometry, SetSRID( MakePoint(
Points.longitude, Points.latitude ), 32767 ) )
This has now been running for approximately 20hrs, admittedly on some quite
underspec hardware.
My question is whether there is any way I can optimise this query? I know
constructing the point geometry on the fly is slowing things a bit, but
surely it only has to be computed once for each row of the Points table...
I have a GIST geometry index on Polygons.wkb_geometry, but according to the
query planner it is not being used, presumably because this query does not
involve any bounding box related computations. It has to be a
contains/within query, using the @ operator would give the results I am
after.
Any suggestions for me to try?
Thanks
Rhys
More information about the postgis-users
mailing list