Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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:… – 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

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
Yeah I did this and got about 15 seconds – Nathan W Oct 4 '12 at 7:53
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 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


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.