Geek asks and answers

Postgresql Spatial query is too slow

I have two tables that I would like to cross. The first table is about 50 million points, and the second is a polygonal layer of all countries of the world. I want to get all the points that intersect with this polygon.

SELECT d.id, d.geom 
FROM export d, world_boundaries b 
WHERE (b.cntry_name = 'UK') 
  AND d.date_inserted >= '2012-06-01' 
  AND d.geom && b.wkb_geometry 
  AND intersects(d.geom, b.wkb_geometry);

This request is very simple, but it takes more than 4 hours. I have GIST indexes built on a geometry column for each table, and they have VACUUM ANALYZE's. There is still no performance increase. I am running CENTOS 6 with Postgres 8.4 and PostGIS 1.5. Can anyone shed some light on how to speed up the process? I get the results very quickly, LIMITING the query to 1000 to 10000 entries. When I try to grab a complete set of results, it drags. Thoughts?

UPDATE: now I see that I need to refine my query as a first step in this process. I get such an envelope

select astext(st_envelope(wkb_geometry)) as e 
from world_borders 
where cntry_name = 'UK'

Now, what is the most efficient way to enable / execute this as part of the whole request?

+5
source share
1 answer

Try running it with EXPLAIN (and LIMIT) to see if indexes are used at all.

Since the actual intersection check is the slowest operation there, perhaps it works against the ST_Collect subquery (everything except the ST_Intersects check) would help. Thus, there will be only one call, and if the multi-geometry design is fast enough, the result may be better.

edit1: , , , , 3d ( ), :

SELECT d.id, d.geom
FROM
(
    SELECT *
    FROM
    ( 
        SELECT ST_Collect(d.geom)
        FROM export d, world_boundaries b 
        WHERE (b.cntry_name = 'UK') 
        AND d.date_inserted >= '2012-06-01' 
        AND d.geom && b.wkb_geometry
    ) as c, world_boundaries b 
    WHERE (b.cntry_name = 'UK')
    AND ST_Intersection(c.geom, b.wkb_geometry);
) as e, export d
WHERE (ST_Dump(e.geom)).geom = d.geom
+1

More articles:


All Articles