Thanks for the replies already. I do have clustered spatial indexes on
each of the tables. I'll check out the simplify, and do some testing on
that.
Thanks again.
Dan Erikson BNRSc
-------------------------------------
Timberline Natural Resource Group
(250)-314-0875 ext 240
www.timberline.ca
-------------------------------------
Burgholzer,Robert wrote:
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);
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
