[postgis-users] Spatial join optimisation

[Rhys Ickeringill]

Thanks Paul, Bill.

HUGE speed improvement. According to the postgres ANALYSE command your
suggestions cut the estimated query cost by a factor of 1000 (!). My query
hasn't finished yet - judging by how long it took to compute 100 records, my
estimate is one hour for the whole lot.

Bill, the difference between your "quick and dirty" approach and your final
approach is a factor of two or so - purely due to there being no geometry
index on the points, hence the sequential scan in the join goes to the
[larger] points table (and the polygon geometry index is used). If I create
an index on the points table...

CREATE INDEX GeoIdx ON Points USING GIST( SetSRID( MakePoint(
Points.longitude, Points.latitude ), 32767 ) )

this index gets used and your "quick and dirty" approach runs perhaps 10%
slower than your final (and Paul's) approach - I guess this is the overhead
of making points on the fly.

Is it worth (for the benefit of the list) beginning a discussion on what
"geometry operators" are best used with each "geometry processing function",
with an eye to including a "tip" associated with each geometry processing
function's entry in the PostGIS manual? (I'd be happy to do the
documentation changes)

Regards,

Rhys


----- Original Message ----- 
From: "Paul Ramsey" <pramsey at refractions.net>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Sent: Friday, May 13, 2005 12:40 PM
Subject: Re: [postgis-users] Spatial join optimisation


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