[postgis-users] Spatial intersect optimization
Carl Anderson
carl.anderson at vadose.org
Mon Feb 6 18:36:04 PST 2006
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>
More information about the postgis-users
mailing list