[postgis-users] Spatial join optimisation

[Paul Ramsey]

ALTER TABLE Points ADD COLUMN geom geometry;
UPDATE Points SET geom = SetSRID(MakePoint(longtitude,latitude),32767);
CREATE INDEX PointsGix ON Points USING GIST (geom);
SELECT pntid, polyid FROM Polygons, Points
   WHERE Points.geom && Polygons.wkb_geometry
   AND Contains(Polygons.wkb_geometry, Points.geom);

Gotta get the index into play, or you are just asking for a world of 
hurt.

Paul

On 12-May-05, at 7:19 PM, Rhys Ickeringill wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users