[postgis-users] How to simplify and complex (i.e. slow) geometry

[Mike Dvorak]

Hi Martin,

I must absolutely thank you for your assistance.  I got things working
just fine now and one, simple shape is being created to use as a mask.
I just dropped the geotype constraint before running the
buffer(the_geom, 0.0) query.  Things also run in only a few minutes,
which is perfectly acceptable for this one-time initialization operation.

Just for the record, here's the before and after results:
http://www.stanford.edu/~dvorak/tmp/how-to-simplify-geom-example.png
http://www.stanford.edu/~dvorak/tmp/yay-it-works.png (zoomed all the way
out)

Here's the SQL I used to create the mask:
CREATE TABLE buffered_mask_12( key SERIAL PRIMARY KEY );

SELECT AddGeometryColumn('buffered_mask_12', 'the_geom', 3310,
'POLYGON', 2);

ALTER TABLE buffered_mask_12 DROP CONSTRAINT enforce_geotype_the_geom;

INSERT INTO buffered_mask_12 (the_geom)  SELECT
Buffer(Transform(Buffer(Collect(the_geom), 0.0), 3310), 2500) AS
the_geom FROM usgs_bathy_mm5_20_50_200m

Thanks again Martin!

Cheers,
Mike

Martin Davis wrote:
> Well, don't go by what the documentation says...  8^)  AFAIK (and on
> good authority) buffer should work fine for *any* geometry type.  In
> particular, it will work fine for a MultiPolygon (even one that's
> invalid) - which is what you should have.
> 
> The error you're getting is due to the constraint on the table limiting
> it to POLYGON geometries, which was generated by the AddGeometryColumn
> function.  Probably the simplest thing to do is to simply drop this
> constraint.  (You could also change to using MULTIPOLYGON - but
> apparently this will then prevent storing POLYGONs, which may or may not
> be an issue).
> 
> Let me know how it goes...
> 
> Mike Dvorak wrote:
>> Hi Martin/PostGIS users,
>>
>> Thanks for your response Martin.  Although I tried your suggestion, I
>> met some problems.  For one, the documentation for ST_Buffer says that
>> you *cannot* use ST_Buffer on a collection.  Isn't that exactly what I'm
>> doing by trying to buffer something from ST_Collect ?  I got an error
>> when I tried your suggestion (see below):
>>
>> Here's what I tried:
>>
>> CREATE TABLE buffered_mask_10( key SERIAL PRIMARY KEY );
>>
>> SELECT AddGeometryColumn('buffered_mask_10', 'the_geom', 3310,
>> 'POLYGON', 2);
>>
>> INSERT INTO buffered_mask_10 (the_geom) SELECT
>> ST_Buffer(Transform(ST_Buffer(ST_Collect(the_geom), 0.0), 3310), 2500)
>> AS the_geom FROM usgs_bathy_mm5_20_50_200m
>>
>> Exception in thread "main" org.postgresql.util.PSQLException: ERROR: new
>> row for relation "buffered_mask_10" violates check constraint
>> "enforce_geotype_the_geom"
>>
>> Any more ideas?
>>
>> Thanks!
>> -Mike
>>
>> Martin Davis wrote:
>>  
>>> Mike,
>>>
>>> If I understand your query, it looks like you are running buffer(0) on
>>> *each* polygon in your query mask.  This is my bad - what I should have
>>> said was that you need to ST_Collect all your query polys into a single
>>> MultiPolygon, and then run buffer(0) on that to get your final query
>>> geometry.  (The MultiPoly you create will be invalid, but buffer()
>>> doesn't care).
>>> Actually, I'm told that ST_Union can act as an aggregator itself (when
>>> given a single argument).  However, I suspect that it is not using an
>>> efficient method of unioning the polygons.
>>>
>>> Once you have your query polygon, another thing you might try is
>>> simplifying it using a small tolerance.  This depends on your need for
>>> accuracy, but it can reduce the number of points (and hence speed things
>>> up) significantly.
>>>
>>>
>>> Mike Dvorak wrote:
>>>    
>>>> Thanks for the response Martin.  Unfortunately, that produces about the
>>>> same results, but maybe I'm misinterpreting your advice.  Here's what I
>>>> tried most recently:
>>>>
>>>> CREATE TABLE buffered_mask_5( key SERIAL PRIMARY KEY );
>>>>
>>>> SELECT AddGeometryColumn('buffered_mask_5', 'the_geom', 3310,
>>>> 'POLYGON',
>>>> 2);
>>>>
>>>> INSERT INTO buffered_mask_5 (the_geom) SELECT
>>>> ST_Buffer(Transform(ST_Buffer(the_geom, 0.0), 3310), 2500) AS the_geom
>>>> FROM usgs_bathy_mm5_20_50_200m
>>>>
>>>> Any more advice?
>>>>
>>>> Cheers,
>>>> Mike
>>>>
>>>>
>>>> Martin Davis wrote:
>>>>  
>>>>      
>>>>> Try using ST_Buffer(mask, 0.0).  That should union all the geometries
>>>>> into a single (Multi)polygon.
>>>>>
>>>>> You could also help us to find some funding to support porting the new
>>>>> JTS optimized predicates to GEOS  ;^)
>>>>>
>>>>> Martin
>>>>>
>>>>>
>>>>>
>>>>> Mike Dvorak wrote:
>>>>>           
>>>>>> Dear PostGIS users,
>>>>>>
>>>>>> First off, I must say that I think the PostGIS project is absolutely
>>>>>> great.  I've been using PostGIS daily for my research for about 6
>>>>>> months
>>>>>> now and have come to love it.  I deal with rather larger geospatial
>>>>>> databases (as large as 200 GB), so I think I have been really pushing
>>>>>> PostGIS (and Postgres) to its limits but having mostly success in
>>>>>> doing so.
>>>>>>
>>>>>> Here's my current problem.  I have a complex 2D polygon geometry that
>>>>>> I'm using as a mask, to limit what areas of wind fields from a
>>>>>> weather
>>>>>> model I insert into my database.  You can find an example mask of the
>>>>>> "shallow" ocean waters near the San Francisco Bay here:
>>>>>> http://www.stanford.edu/~dvorak/tmp/how-to-simplify-geom-example.png
>>>>>>
>>>>>> The problem is that this 2D polygon, contains a lot of redundancy
>>>>>> for a
>>>>>> mask and it takes forever (i.e. tens of hours) to create the
>>>>>> intersection of the 2D mask polygon and the grid of model points
>>>>>> that I
>>>>>> want to mask (approximately 200 x 240 points).
>>>>>>
>>>>>> I tried using ST_ConvexHull and ST_Union (with the mask for both
>>>>>> arguments) without luck to simplify this example mask in the URL.  Is
>>>>>> there a PostGIS function that will get rid of all redundant mask
>>>>>> objects
>>>>>> i.e. objects that are completely contained within another object,
>>>>>> or do
>>>>>> I have to program this myself?
>>>>>>
>>>>>> Thanks for any advice!
>>>>>>
>>>>>> Cheers,
>>>>>> Mike
>>>>>>
>>>>>>
>>>>>>                   
>>>>         
>>
>>   
> 

-- 
==========================================
Mike Dvorak
Atmosphere/Energy PhD student
Dept. of Civil & Environmental Engineering
Stanford University
Phone: (773) 936-8053
==========================================