Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
related question: gis.stackexchange.com/questions/1387/… –  underdark Oct 4 '12 at 6:36
    
What setup is both the SQL Server 2012 and PostGIS 2.0 on? –  Mapperz Oct 5 '12 at 2:03

1 Answer 1

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

share|improve this answer
1  
Yeah I did this and got about 15 seconds –  Nathan W Oct 4 '12 at 7: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 4 '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 5 '12 at 0:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.