[postgis-users] Efficient within / intersection of large polygons

[Christopher Mutel]

Ralf-

I think what you are suggesting is a version of option 4: Storing some
pre-calculated structure which is already present. Currently the
database has no concept of country/region/etc. This is certainly a
possibility, especially as there are a few relatively standardized
levels, i.e. Continent, region, country, sub-country. It would involve
several trips between the application code and the database, or
hard-coding some row structure into a pl/pgsql function.

I wonder if there are other suggestions or comments?

On 30 July 2010 13:59, Ralf Suhr <Ralf.Suhr at itc-halle.de> wrote:
> Hi Chris,
>
> its look like you query all at ones cantons and stats. That does not make
> sense. You can query one polygon with all districts from the world and secound
> group by a attribut to get the country(s).
>
> If you want to know if a polygon is in a country you can build sum of area
> intersections from districts. The result have to be equal to the polygon
> aerea.
>
> Gr
> Ralf
>
> Am Freitag 30 Juli 2010, 13:41:55 schrieb Christopher Mutel:
>> Hello all:
>>
>> I am humbly asking for help in dealing with slow st_within &
>> st_intersection queries with large (multi)polygons.
>>
>> In my use case, I need to allow users to enter geographic areas (mosty
>> polygons) through a web interface, and then determine if these areas
>> lie completely with various continent or regional shapes (e.g. Europe,
>> South Asia). The database also includes all countries in the world, as
>> well as stats/oblasts/cantons/etc. for large countries. As the
>> precision in these areas is relatively high (Europe is a multipolygon
>> defined by ~1e6 points), this takes quite a long time.
>>
>> The Postgis manual says: "Just because you *can* store all of Europe
>> in one polygon doesn't mean you *should*." However, in my case I do
>> need to store the entire polygon, as all polygons defined in the
>> system will be regularly exported.
>>
>> In my case, simplifying polygons is not really an option, as user
>> inputs can be points (e.g. factories), and it is quite important to
>> know whether they are on the Rhein in Germany or in France, for
>> example. This affects the electricity mix they use, the markets they
>> buy their inputs from, etc.
>>
>> Looking through the mailing list & the web, it seems like there are
>> several ways to improve database performance:
>>
>> 1) Performing queries initially against a simplified set of polygons.
>> However, I am not sure how this is faster than using the bounding box
>> that is already indexed, and queries would still be made against the
>> most complex shapes, as they are quite large.
>>
>> 2) "Denormalizing" data, i.e. splitting polygons up into each separate
>> UTM zone, and then running queries against input polygons which are
>> similarly separated. This is recommended by the user manual, but I am
>> not sure if it makes sense in my case. Is there a working code sample
>> somewhere that does these calculations automatically? Is the easiest
>> way simply to create polygons that define each UTM zone, and then do
>> an intersection query for each one?
>>
>> 3) Adding extra indices. I am not sure what else could be indexed, but
>> maybe there is something that I am not seeing. The only queries I need
>> are within and intersects/intersection.
>>
>> 4) Caching results. As the base data set does not change often, and
>> the number of total shapes is relatively small (<1000 even after user
>> input), calculations could be done once, slowly, and then referenced,
>> with the appropriate triggers to flush the cache upon updates or
>> deletes.
>>
>> Any ideas, comments, or questions would be greatly appreciated.
>>
>> Yours,
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
############################
Chris Mutel
Ökologisches Systemdesign - Ecological Systems Design
Institut f.Umweltingenieurwissenschaften - Institute for Environmental
Engineering
ETH Zürich - HIF C 42 - Schafmattstr. 6
8093 Zürich

Telefon: +41 44 633 71 45 - Fax: +41 44 633 10 61
############################