[postgis-users] Need help concatentating 60, 000 polygon geometries

[Shane Spencer]

60,000 polygon objects will be harder to fit into memory than a single
polygon from that set would.  I would think doing a query on each
individual polygon would be faster than doing it on one large
multipolygon for the following reason.

Bounding boxes are typically used to see if a point even exists within
the extents of a polygon.  When searching the large 60,000 polygon
object you will only use one bounding box to help optimize your search
making it much slower to find a single polygon that the point exists
within.  Doing this row by row will be a speedy method of quickly
discovering if a point exists in a polygon

If you need to find out if an object exists within a bunch of polygons
then whats the issue with using a fast select statement like the
following, which then enabled you with the knowledge of what polygons
contain the point.  If rows returned > 0, yay.

parcels_muni in my sample data countains

select * from parcels_muni where ST_Contains(wkb_geometry,
GeomFromText('SRID=32767;POINT(1680729.269 2623817.919)'));

The average points (probably a bit low) for my data set is measured in
the select statement below, like it says I have over 80k rows and it
didn't take long at all for the above query to run.

select count(*), avg(points) from (select
ST_NumPoints(ST_ExteriorRing(ST_GeometryN(wkb_geometry, 1))) as points
from parcels_muni) as pointdb;
 count |         avg
-------+---------------------
 83316 | 35.3173858248814739

On 10/4/07, Scott Schulthess <scott at topozone.com> wrote:
>
>
>
>
> Hello everyone,
>
>
>
> I have a table with 60,000 rows.
>
>
>
> Each row has a geometry field that is a polygon.
>
>
>
> I want to concatenate all of these geometry objects into one big multipolygon, or at the very least, a polygon, so I can query easily on one geometry object to see if a point lies within that multipolygon.
>
>
>
> I have tried using geomuion, something like below
>
>
>
> update layerindex set geom= (select multi(geomunion(transform(geom,4326))) from stockdrgmeta where sourcescale > 40000 and sourcescale < 80000) where layername='DRG50'
>
>
>
> 18 hours into this query I figured it didn't work.   A similar query worked after a while on far less rows.
>
>
>
> I then tried using the collect() function, but I couldn't figure out a way to turn the geometry collection that the function returns into a multipolygon.
>
>
>
> Please help.
>
>
>
> Thanks!
>
>
>
> ____________________________________
>
> Scott Schulthess | Web Developer
>
> Maps a la carte, Inc / TopoZone.com
>
> http://www.topozone.com
>
> http://www.linkedin.com/in/scottschulthess
>
> scott at topozone dot com
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>