[postgis-users] Faster point polygon query

[Dan Erikson]

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