From Microsoft SQL server to PostGIS

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.

Table of Contents

My article is structured as follows:

Requirements
Datasets
Migration
Results
Final thoughts

Requirements

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. https://hub.docker.com/_/microsoft-mssql-server.

docker run -e "ACCEPT_EULA=Y" -e 
"MSSQL_SA_PASSWORD=SecretKey2024#" -e "MSSQL_PID=Express" -p 1434:1433 -d 
mcr.microsoft.com/mssql/server:2022-latest

docker run -e "ACCEPT_EULA=Y" -e

"MSSQL_SA_PASSWORD=SecretKey2024#" -e "MSSQL_PID=Express" -p 1434:1433 -d

mcr.microsoft.com/mssql/server:2022-latest

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

docker run -p 5433:5432 -e POSTGRES_PASSWORD=secret -d 
postgis/postgis:16-3.4

1 2	docker run -p 5433:5432 -e POSTGRES_PASSWORD=secret -d postgis/postgis:16-3.4

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.

sudo apt-get install gdal-bin

1	sudo apt-get install gdal-bin

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:

if ! [[ "18.04 20.04 22.04 23.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc

if ! [[ "18.04 20.04 22.04 23.04" == *"$(lsb_release -rs)"* ]];

then

echo "Ubuntu $(lsb_release -rs) is not currently supported.";

exit;

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18

# optional: for bcp and sqlcmd

sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc

source ~/.bashrc

Datasets

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.

spatial_demo	dbo	gis_osm_buildings_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_landuse_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_natural_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_natural_free_1	ogr_geometry	2	4326	POINT
spatial_demo	dbo	gis_osm_places_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_places_free_1	ogr_geometry	2	4326	POINT
spatial_demo	dbo	gis_osm_pofw_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_pofw_free_1	ogr_geometry	2	4326	POINT
spatial_demo	dbo	gis_osm_pois_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_pois_free_1	ogr_geometry	2	4326	POINT
spatial_demo	dbo	gis_osm_railways_free_1	ogr_geometry	2	4326	LINESTRING
spatial_demo	dbo	gis_osm_roads_free_1	ogr_geometry	2	4326	LINESTRING
spatial_demo	dbo	gis_osm_traffic_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_traffic_free_1	ogr_geometry	2	4326	POINT
spatial_demo	dbo	gis_osm_transport_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_transport_free_1	ogr_geometry	2	4326	POINT
spatial_demo	dbo	gis_osm_water_a_free_1	ogr_geometry	2	4326	POLYGON
spatial_demo	dbo	gis_osm_waterways_free_1	ogr_geometry	2	4326	LINESTRING

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:

ogr2ogr -overwrite -nlt PROMOTE_TO_MULTI -f "PostgreSQL" 
PG:"host=10.0.0.217 port=5433 user=postgres dbname=mssql_migration 
password=secret" "MSSQL:driver=ODBC Driver 18 For SQL Server; 
server=10.0.0.217,1434;trustservercertificate=yes;uid=sa;pwd= 
SecretKey2024#;database=spatial_demo;

ogr2ogr -overwrite -nlt PROMOTE_TO_MULTI -f "PostgreSQL"

PG:"host=10.0.0.217 port=5433 user=postgres dbname=mssql_migration

password=secret" "MSSQL:driver=ODBC Driver 18 For SQL Server;

server=10.0.0.217,1434;trustservercertificate=yes;uid=sa;pwd=

SecretKey2024#;database=spatial_demo;

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. https://gdal.org/drivers/vector/pg.html for PostgreSQL/PostGIS.

Results

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

gis_osm_buildings_a_free_1	ogr_geometry	2	4326	MULTIPOLYGON
gis_osm_landuse_a_free_1	ogr_geometry	2	4326	MULTIPOLYGON
gis_osm_natural_a_free_1	ogr_geometry	2	4326	MULTIPOLYGON
gis_osm_natural_free_1	ogr_geometry	2	4326	MULTIPOINT
gis_osm_places_a_free_1	ogr_geometry	2	4326	MULTIPOLYGON
gis_osm_places_free_1	ogr_geometry	2	4326	MULTIPOINT
…	...	...	...	...

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!

create table if not exists public.gis_osm_buildings_a_free_1
(
    ogr_fid      serial
        primary key,
    osm_id       varchar(12),
    code         numeric(4),
    fclass       varchar(28),
    name         varchar(100),
    type         varchar(20),
    ogr_geometry geometry(MultiPolygon, 4326)
);

create index if not exists gis_osm_buildings_a_free_1_ogr_geometry_geom_idx
    on public.gis_osm_buildings_a_free_1 using gist (ogr_geometry);

create table if not exists public.gis_osm_buildings_a_free_1

(

ogr_fid serial

primary key,

osm_id varchar(12),

code numeric(4),

fclass varchar(28),

name varchar(100),

type varchar(20),

ogr_geometry geometry(MultiPolygon, 4326)

);

create index if not exists gis_osm_buildings_a_free_1_ogr_geometry_geom_idx

on public.gis_osm_buildings_a_free_1 using gist (ogr_geometry);

Does our buildings table contain data? Indeed.

select osm_id, name, st_astext(ogr_geometry)
from public.gis_osm_buildings_a_free_1

1 2	select osm_id, name, st_astext(ogr_geometry) from public.gis_osm_buildings_a_free_1

40846216	Aeroporto João Paulo II	MULTIPOLYGON(((-25.7114992 37.7492618,-25.7112851…
48996741	Igreja de Nossa Senhora da Alegria	MULTIPOLYGON(((-25.3112127 37.7728547,-25.3112019…
73563989	Casa Florestal	MULTIPOLYGON(((-27.975745 39.0298535,-27.975745 3…
73565318	Information Centre Furna do Enxofre	MULTIPOLYGON(((-27.9718388 39.0250458,-27.9716749…
73694789	Termas do Carapacho	MULTIPOLYGON(((-27.9596121 39.0126662,-27.959168 …
74891751	Aeródromo da Graciosa	MULTIPOLYGON(((-28.0286869 39.0908536,-28.0286464…
92934924	Aeroporto João Paulo II - Terminal de Carga	MULTIPOLYGON(((-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!

From Microsoft SQL server to PostGIS

Requirements

Datasets

Migration Microsoft SQL to PostGIS

Results

Final thoughts about migrating from Microsoft SQL to PostGIS

Leave a Reply Cancel reply

Florian Nadler

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts