Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have 2 vector data in my Postgis DB: one is a point layer (punti) with about 200000 elements and the other is a polygon (province) with 4 elements. They cover the same area and I need to find the number of points for each polygon. This is the query:

province.gid, count(*) AS totale FROM punti, province WHERE 
st_intersects(province.the_geom,punti.the_geom) GROUP BY province.gid;

This is the EXPLAIN ANALYZE

HashAggregate (cost=35.35..35.36 rows=1 width=4) (actual time=23660.787..23660.789 rows=4 loops=1) -> Nested Loop (cost=0.00..35.34 rows=1 width=4) (actual time=0.307..23532.479 rows=199977 loops=1) Join Filter: _st_intersects(province.the_geom, punti.the_geom) -> Seq Scan on province (cost=0.00..1.04 rows=4 width=36) (actual time=0.003..0.009 rows=4 loops=1) -> Index Scan using punti_the_geom_idx on punti (cost=0.00..8.31 rows=1 width=100) (actual time=0.038..76.254 rows=69541 loops=4) Index Cond: (province.the_geom && punti.the_geom) Total runtime: 23660.850 ms

What do you think about this total runtime? Thx!

share|improve this question
    
As of PostGIS 1.3, ST_Intersection does implicit bounding box checks to utilize the GIST index. So I'm not sure how to enhance this... –  Crischan May 20 '12 at 0:35

1 Answer 1

I think 23 seconds ain't bad. If you want to make it even smaller, slice your polygons up into a larger collection smaller objects, by intersecting with a grid, for example.

share|improve this answer
    
Why would a larger collection of smaller polygons be faster? Wouldn't that just mean more intersection operations? Are you implying simpler as well as smaller? –  naught101 May 20 '12 at 14:12
2  
Because it will increase the index selectivity. –  Nicklas Avén May 20 '12 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.