[postgis-users] Spatial intersect optimization

[Josh Livni]

Thanks to both of you for the prompt responses.  I am going w/centroids, 
and am liking PostGIS more and more.

   -Josh

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