[postgis-users] Spatial intersect optimization

[Stephen Woodbridge]

Josh,

select a.name, sum(p.area) a parcel_area
   from polygons a, parcels p
   where a.the_geom && p.the_geom
   and intersects(a.the_geom, p.the_geom)
   group by a.name
   order by parcel_area;

There is not much more you can do to make this faster. If parcels can 
overlap polygons and you want only the part of the parcel that is in the 
polygon then you will want the intersection of the two and then you will 
need to compute the area for the intersection, etc.

-Steve W.

Josh Livni wrote:
> I am fairly inexperienced with optimizing spatial queries in PostGIS, 
> and am trying to do something similar to the example 4.6.2.4 in the 
> documentation at http://postgis.refractions.net/docs/ch04.html
> 
> It notes that this will be slow due to summarizing.  I have over 500k 
> parcels, and I'd like to do some summary stats in each of about 15k 
> polygons.
> 
> My question is, could the example mentioned above become faster if it 
> took better advantage of the gist indices?  or is it already?  How would 
> I go about adding some kind of 'AND the_geom && (bbox of m.geom)' to the 
>  WHERE clause?
> 
> Thanks for any hints,
> 
>   -Josh
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>