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 [EMAIL PROTECTED] 804-698-4405 Open Source Modeling Tools: http://sourceforge.net/projects/npsource/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
