[postgis-users] Spatial intersect optimization

[Bill Binko]

Ack   Small error below
Here's the correct select:

 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, Centroid(p.the_geom))
 group by a.name
 order by parcel_area;

Bill Binko wrote:

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