[postgis-users] Spatial join optimisation

[Rhys Ickeringill]

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