[postgis-users] Faster point polygon query

[Burgholzer,Robert]

Make sure that you have spatial indices on the geom columns in each
table. If that is already the case, the most likely culprit is extremely
complex polygon shapes, which the containment algorithms are not suited
to do quickly (although it might be the fastest algorithm possible).
Suggestions that have come up in the past were to use the "simplify()"
function to remove extraneous edges from your polygons, making the query
faster.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Dan
Erikson
Sent: Wednesday, January 23, 2008 4:34 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Faster point polygon query

I need to relate two spatial datasets.  One dataset is a point dataset 
(~200 Million records), the other is a polygon dataset (~500,000 
records).  The result of these queries is to be a non-spatial table 
listing the id's ("tid" in this case) from each table where there is a 
spatial join.  I have tried both ST_Within and ST_Distance, and both 
queries are incredibly slow (> 100 hours).   Running on a very capable 
server.  Any ideas on faster methods to complete this query?

Thanks!

CREATE TABLE public.step2 as
    (SELECT mp.pntgrid_10_tid AS pntgrid_10_tid,
        vri.tid AS vri_tid
    FROM grids.tbl_mtsa_pnts mp, vri.tbl_vri vri
    WHERE mp.geom && vri.geom
        AND ST_Within(mp.geom, vri.geom));

CREATE TABLE public.step2 as
    (SELECT mp.pntgrid_10_tid AS pntgrid_10_tid,
        vri.tid AS vri_tid
    FROM grids.tbl_mtsa_pnts mp, vri.tbl_vri vri
    WHERE mp.geom && vri.geom
        AND ST_Distance(mp.geom, vri.geom) = 0);

-- 
Dan Erikson BNRSc


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users