6

I imported a shapefile with 3000 polygons in UTM 25832 into both databases MS SQL Server 2019 and PostgreSQL/PostGIS. Then I calculated a buffer of 1000 meters around these geometries and transferred the result to a new table. (Again in both databases with their integrated STBuffer() functions)

Result:

PostGIS: 9 seconds
MS SQL 2019: 1 minute
MS SQL 2017: 1:09 minutes

All databases are running on the same PC and both MSSQL Server and PostgreSQL/PostGIS have been freshly set up. No optimizations were deliberately carried out and no spatial indices were used.

Is it really possible that PostgreSQL/PostGIS is so much faster than MS SQL Server 2019 as far as geodata is concerned?

Improve this question
4
  • 3
    Were all databases optimally tuned? Were they running on the same hardware? And I think you would have to look at what PostGIS uses as default parameters for its ST_Buffer function because I'm not sure it's an apples-to-apples comparison. It's an interesting thread to pull on for sure, but based on your question there isn't enough information to draw a conclusion.
    – Jay Cummins
    Commented Feb 23, 2021 at 12:40
  • Thanks for the hint I have adjusted the question. The difference of ST_Buffer to STBuffer in detail, I can not answer unfortunately. However, the result is the same as I could check both tables in QGIS. As an addition, if I perform the calculations in QGIS, the result on the Postgres/PostGIS-DB is also faster, although the advantage is not so great anymore.
    – Omegon
    Commented Feb 23, 2021 at 13:44
  • 1
    Is it really so hard to believe? Only thing I can think of is if you used the "geography" type in MSSQL by accident that would force them to calculate their buffers in geodetic, which would be quite expensive. But you say they are both in UTM, so ... PostGIS wins. Move on. (Also, PostGIS and QGIS use the same engine for buffer, so any difference is just QGIS overhead.)
    – Paul Ramsey
    Commented Feb 23, 2021 at 18:34
  • @PaulRamsey Thanks for the answer, exactly both columns are geometry type. It's actually a bit hard for me to believe, as I've used both databases for different apps before and found little difference in speed for everyday queries. For geodata I have only used PostGIS so far. Since I'm currently planning a new app, I thought why should I use two DBs when MS SQL can also handle geodata. I am now surprised to see that some functions are obviously much slower. So I thought I would ask if others have noticed this as well.
    – Omegon
    Commented Feb 24, 2021 at 7:59

2 Answers 2

Reset to default
4

My take on the spatial data component of MSSQL Server echoes the documentation - basic spatial capabilities, and anything more complex is handled by 3rd parties.

What this means to us is that MSSQL Server can handle some basic Spatial SQL functions that get a bit of information from an overlapping polygon, a quick distance or area of few features, etc.

It isn't built and doesn't perform well for doing massive spatial operations on thousands or millions of features - what is otherwise called "geoprocessing". This is where ESRI has picked up and said 'if you're going to store spatial data in a database, it is best managed/edited/analysed by our GUI'.

PostGIS, on the other hand, has been built not just for the simple stuff, but for the complex bulk processing of spatial and non-spatial data in the database, and as such, performs much faster. This has been true for all student/boundary/demographic analysis we perform in our system using postgis.

To its credit, however, MSSQL Server was a great 'gateway' into SQL and spatial data. PostgreSQL with PostGIS is just the best.

Improve this answer
0
4

Spatial algorithms such as buffer are complex, with a lot of moving parts. It only takes one inefficient design decision or data structure to reduce performance. Conversely, sometimes it's possible to find a different approach that can improve performance dramatically.

The PostGIS buffer code (which originates in JTS/GEOS) has some optimizations which really help to improve performance. For example, since buffers tend to "blur out" fine detail along boundaries, the algorithm simplifies boundaries before running the actual buffer. This can dramatically reduce the number of points being computed, and hence increase performance.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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