[postgis-users] ST_Union and 8.3?

[Mark Cave-Ayland]

On Thu, 2008-03-20 at 12:20 -0400, Obe, Regina wrote:
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
>

Hi Regina,

Thanks for the bug report. Since this is a fairly serious issue, I had a
quick look at the code in question, and it was fairly obvious that I had
missed several macros when getting ready for PostgreSQL 8.3 :(

I've committed a fix to SVN which should resolve the outstanding issues,
although the bug affected a lot more functions than I expected. The
complete list of functions that are likely to have crashed under
PostgreSQL 8.3 is included below:

ST_force_2d()
ST_force_3dz()
ST_force_3dm()
ST_force_4d()
ST_Collect()
ST_MakeLine()
ST_Polygonize()
ST_Union()/ST_GeomUnion()
ST_MakePolygon()/ST_Polygon()

I've tested with PostgreSQL 8.2 and 8.3 and the regression tests look
good; please can other people test and feedback?

Paul: please can you make me an administrator on the Google Bug Tracker?
I've done a quick move over of all the issues on the wiki, but I can't
assign issues to myself and other people. You should also be able to close
issues #2, #3 and #9.


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063