[postgis-users] Spatial intersect optimization

[Bill Binko]

You absolutely need GiST indexes for this to run effectively.

Two additional things you could do for both sanity and performance:

1) Run the query on Centroids  instead of actual areas.  You can do this 
two ways, but in addition to being faster, it is actually more accurate 
since your parcels will only fall within one of your polygons.
    a) Create a separate table or column that holds the results of the 
Centroid() function
    b) Easier: Create a functional index like so:
CREATE INDEX cent_idx on parcels USING GIST (Centroid(the_geom) 
GIST_GEOMETRY_OPS);
   Then you would write the query below as:

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


2) If this is your main use for the tables, you can try ordering your 
data by Clustering: read the archives of this list for a long discussion 
on whether or not it actually helps (but it does :)
CLUSTER cent_idx on parcels;

Hope this helps

Stephen Woodbridge wrote:

> 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
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>