[postgis-users] Spatial intersect optimization

[Carl Anderson]

Additional (advanced) tuning can be made by limiting the number of 
vertices in each geometry of the "polygons" table as well.

There are two basic way to do that:
    lossy way
           simplify the geometries in the "polygons" file.  The 
simplified geometries will not be exactly the same as the original and 
if simplified too aggressively may give odd results

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

    lossless way
             dice the "polygons" table into a series of smaller objects 
(tiles).  Literally slice each geometry in "polygons" into smaller 
parts.  (plpgsql script attached)
             In other instances you may want to summarize on the the 
tiled "polygons" table to reconstitute an equivlaent of the original 
intersection

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

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


-------------- next part --------------
A non-text attachment was scrubbed...
Name: chopintogrid.sql
Type: text/x-sql
Size: 2391 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060206/939cbead/attachment.bin>