From Microsoft SQL server to PostGIS

06.2024 / Category: / Tags: |

PostGIS is getting more and more popular and for good reasons. It benefits from a great community and offers a huge set of cool and stable features. Nowadays, a lot of customers want to migrate their spatial datasets from various data sources to PostGIS, where they can easily work with them.
Today I want to take a closer look at how this migration could look like for those transitioning from Microsoft SQL server to PostGIS.  

My article is structured as follows:

  1. Requirements
  2. Datasets
  3. Migration
  4. Results
  5. Final thoughts


To replay this demonstration easily, let’s look at my environment first.
To go through the upcoming steps, a Linux machine is needed, which hosts two independent database servers (Ubuntu 22.04 in my case).

Microsoft SQL Server 2022 Express Edition serves as a source database, where spatial datasets are stored. Fortunately, Microsoft offers dockers containers for various MSSQL server versions, which are quite easy to work with e.g.

PostgreSQL (including PostGIS) serves as the target database. Various docker images do exist, so I decided to pick a recent version from e.g.

Now it’s time to talk about our tooling. How are we going to migrate our datasets from MSSQL to PostGIS? Let’s take it easy and ask GDAL for help. According to its homepage, GDAL is a translator library for geospatial raster and vector data. Fortunately, it comes with a bunch of command tools that require no further coding. To tackle our job, we’ll use a very popular tool from this library called ogr2ogr, which is part of the gdal-bin package in Ubuntu. 

Even though ogr2ogr supports a great list of data sources out of the box, MSSQL server requires us to install database drivers separately. From the Microsoft homepage, installing Microsoft ODBC 18 under Ubuntu can be accomplished as follows:


Today we want to migrate OpenStreetMap datasets covering the Azores. Querying the geometry_columns table in our source database spatial_demo gives us a good overview of our “spatial” tables and their characteristics.


You might ask – where do these datasets stem from 😊? As preparation, I downloaded ESRI shapefiles covering Azores from Geofabrik and utilized ogr2ogr for the upcoming import. 

Migration Microsoft SQL to PostGIS

The migration job is delegated to ogr2ogr respectively GDAL as follows:

This command will migrate the complete database spatial_demo from MSSQL to PostGIS.
Some of our datasets contain both simple features and collections. To force subtype modifiers in PostGIS, we’ll turn simple features to collections with the parameter PROMOTE_TO_MULTI. To further fine tune the migration process, e.g. selecting a subset of datasets or columns, check out the documentation and the individual driver’s section within ogr2ogr, e.g. for PostgreSQL/PostGIS.


How smart is GDAL? At first glance, the contents of our beloved geometry_columns view in PostGIS look promising. 


Let us take a closer look at the structure of one of our tables. Buildings are represented as MultiPolygons, the spatial reference identifier is set to 4326 and finally a gist index is being created. Nice!

Does our buildings table contain data? Indeed.

40846216Aeroporto João Paulo IIMULTIPOLYGON(((-25.7114992 37.7492618,-25.7112851…
48996741Igreja de Nossa Senhora da AlegriaMULTIPOLYGON(((-25.3112127 37.7728547,-25.3112019…
73563989Casa FlorestalMULTIPOLYGON(((-27.975745 39.0298535,-27.975745 3…
73565318Information Centre Furna do EnxofreMULTIPOLYGON(((-27.9718388 39.0250458,-27.9716749…
73694789Termas do CarapachoMULTIPOLYGON(((-27.9596121 39.0126662,-27.959168 …
74891751Aeródromo da GraciosaMULTIPOLYGON(((-28.0286869 39.0908536,-28.0286464…
92934924Aeroporto João Paulo II - Terminal de CargaMULTIPOLYGON(((-25.6953691 37.7426318,-25.6952219…

With the given results I leave further tests to the reader's creativity!

Final thoughts about migrating from Microsoft SQL to PostGIS

Today we migrated a MSSQL server database containing various vector datasets to PostGIS via ogr2ogr – with one parameterized command. Wow. It’s not the first time I’m impressed by this feature-rich tool, which serves as part of the popular GDAL library. 

In this article we only scratched the surface by touching a limited dataset containing simple features. Quite often it’s not that easy and straightforward! In case you have questions, get in touch with our nice folks at Cybertec. Stay tuned for further related posts!

Read more about PostGIS here.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram