Speed differences between SQL Server 2012 and PostGIS UnionAggregate

Question

There seems to be a massive difference in speed between PostGIS and SQL Server 2012 with a union aggregate function.

SELECT geometry::UnionAggregate(the_geom)
FROM loc
GROUP BY LocalityCo

vs

SELECT ST_Multi(ST_Union(the_geom))
FROM dummydata.loc 
GROUP BY "LocalityCo"

I get less then a second in SQL Server 2012 but around 25 seconds in PostGIS.

PostGIS version is: 1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Is there anything I can do to speed up the PostGIS query or it's just the way it is?

Note: Both have spatial indexes.

related question: http://gis.stackexchange.com/questions/1387/is-there-a-dissolve-function-in-postgis-other-than-st-union — underdark, Oct 04 '12 at 06:36

score 2 · Answer 1 · answered Oct 04 '12 at 07:17

2

You should probably upgrade your postgis version to 2.0.1 including the latest dependancy libraries (geos 3.3,...) then test it again.

answered Oct 04 '12 at 07:17

U2ros

1

Yeah I did this and got about 15 seconds – Nathan W Oct 04 '12 at 07:53
1

That's a pretty simple query, so it's just the way it is. Would you share your data for profiling and improvement? You could add and enhancement ticket to trac.osgeo.org/postgis and attach the data (is it points, lines, or polygons?) or a link to it, that would be great. – Paul Ramsey Oct 04 '12 at 17:09
I'll find out if I can share the data. If I can I'll create a ticket for it. – Nathan W Oct 05 '12 at 00:35

1 Answers1