[postgis-users] ST_Union and 8.3?

[Obe, Regina]

Just tested by upgrading to 8.3.1 - doesn't make a difference.  Actually
for example

When I do something like

SELECT ST_Union(the_geom)
FROM boszip

which glues all the boston zips into one MULTIPOLYGON it crashes the
whole PostgreSQL service forcing me to have to restart the service (or
at least on my windows dev box) (this worked fine on 8.2 isntall).  I
think it actually did that running 8.3.0 too (although can't be
absolutely sure - since I think I tested that but maybe not).

Doing 
SELECT ST_MemUnion(the_geom)
FROM boszip 

still works fine and finishes in 579 ms.  Although I haven't looked at
the results, it returns the same number of geometries as my 8.2 install
doing ST_Union.

I was looking at the difference between the 2 functions.  It looks like
MemUnion - skips the whole accum unit process - and just glues the
geometries together 2 at a time with ST_Union (non-agg function).
Wouldn't this make it useful for that whole sort by geometry cascade
thing people were talking about.

ST_Union on the other hand - uses st_geom_accum to form an array and
then does a st_unite_garray call at the end. ST_Collect has issues as
well, but not quite as serious as ST_Union.

If I do these

--Works fine
SELECt ST_Collect(the_geom)
FROM boszip where zip5 IN('02109', '02110')

--Crashes service
SELECT ST_Collect(the_geom)
FROM boszip 

--Works fine
SELECt ST_MemCollect(the_geom)
FROM boszip 


--Gives  - ERROR:  Operation on mixed SRID geometries

********** Error **********
ERROR: Operation on mixed SRID geometries
SQL state: XX000

SELECt ST_Union(the_geom)
FROM boszip where zip5 IN('02109', '02110')

Crashes service
SELECt ST_Union(the_geom)
FROM boszip

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Thursday, March 20, 2008 11:58 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ST_Union and 8.3?

On Thursday 20 March 2008 13:37:03 Obe, Regina wrote:
> Mark,
>
> Can you think of anything wrong with using ST_MemUnion as a temporary
> fix aside from the fact that I guess for larger unioning jobs it is
> probably less efficient?

I can't say that I've had much to do with that section of code - I think
it 
was probably strk that came up with the idea and implementation.

> Has anyone tried the new 8.3.1 to see if it changes the below issue.
I
> don't see anything in the list of changes
> http://www.postgresql.org/docs/8.3/static/release-8-3-1.html that
would
> suggest it would, except possibly for
>
> "Fix memory leaks in certain usages of set-returning functions"
>
> I'm planning to try it out in the next day or so.

The discussion on -hackers was that objects created within each SRF were

living longer than intended. Now in the case of a buggy SRF this could
cause 
excess memory usage during execution that would only be freed at the end
of 
the SRF. However I have a feeling that most of the memory in PostGIS is
being 
held by GEOS so this may/or may not make a difference. But please feel
free 
to experiment :D


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.