[postgis-users] Faster point polygon query

[Dan Erikson]

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
> 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);
>
>