Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

Use MSSQL layers with QGIS 2.18 in Debian Stretch 9.6

Prerequisites

  • Install Microsof repositories
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/9/prod.list  > /etc/apt/sources.list.d/mssql-release.list
apt update
ACCEPT_EULA=Y apt-get install -y mssql-cli unixodbc-dev mssql-tools msodbcsql17 odbcinst libqt4-sql-tds libqt4-sql-odbc unixodbc-dev iodbc freetds-bin libdbd-freetds freetds-dev freetds-common tdsodbc
  • Test connection to your database with mssql-cli tool
# https://github.com/dbcli/mssql-cli
# https://www.mytecbits.com/microsoft/sql-server/mssql-cli
mssql-cli -S DB_HOST -U DB_USER -P DB_PASSWORD -d DB_DB
  • Modify ODBC ini file and add sections as described below. It seems the "SQL Server" section is needed for ogr2ogr, and the "FreeTDS" section is needed for QGIS MSSQL native provider
nano /etc/odbcinst.ini # add at the end :

[SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
Trace=Yes
TraceFile=/tmp/mssql.log

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1

[FreeTDS]
Description = tdsodbc
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5
  • You can also edit the /etc/odbc.ini file
[ODBC Data Sources]
testsqlserver = ODBC Driver 17 for SQL Server

[testsqlserver]
Driver       = ODBC Driver 17 for SQL Server
Description  = Test SQL Server
Server       = mssql
  • After that, you may need to add symbolic links so that QT can use the files
ln -s /etc/odbcinst.ini /usr/local/etc/odbcinst.ini
ln -s /etc/odbc.ini /usr/local/etc/odbc.ini
echo 'PATH="$PATH:/opt/mssql-tools/bin"' > /etc/profile.d/mssql-path.sh

Import data into MSSQL

With ogr2ogr

At present, I think you can only use ogr2ogr to import your data. See for example https://nathanw.net/2011/06/07/opening-ms-sql-server-2008-spatial-tables-in-qgis-correctly/

Using ogr2ogr will help creating and filling in the 2 needed tables geometry_columns and spatial_ref_sys

You can do it for example with this kink of command line:

# Import
ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;" test.shp

# Test imported data
ogrinfo -al "MSSQL:server=DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;tables=test" -fid 1

Sometime you need to set the Driver before running the ogr2ogr command

set driver="SQL Server"
ogrinfo -al "MSSQL:driver=SQL Server;server=DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;tables=test" -fid 1

With QGIS

Drag and drop of layers to MSSQL works via the QGIS browser.

Open data in QGIS

Here are the recommandations to open data from MSSQL in QGIS

Here are the information about the optionnal geometry_columns and spatial_ref_sys table to create and populate to allow better performances:

  • geometry_columns:
CREATE TABLE [dbo].[geometry_columns](
[f_table_catalog] [varchar](50) NULL,
[f_table_schema] [varchar](50) NULL,
[f_table_name] [varchar](100) NULL,
[f_geometry_column] [varchar](50) NULL,
[coord_dimension] [int] NULL,
[srid] [int] NULL,
[geometry_type] [varchar](50) NULL
);

with entries like:

f_table_catalog f_table_schema  f_table_name    f_geometry_column   coord_dimension srid    geometry_type
GISDB   dbo Accuracy    SP_GEOMETRY 1   4326   LineString
GISDB   dbo AssetAreas  SP_GEOMETRY 2   4326   Polygon

You can insert data manually with something like:

INSERT INTO "dbo"."geometry_columns" VALUES ('GISDB', 'dbo', 'AssetAreas', 'SP_GEOMETRY', 2, 4326, 'Polygon');
  • spatial_ref_sys
CREATE TABLE [dbo].[spatial_ref_sys](
[srid] [int] NOT NULL,
[auth_name] [varchar](256) NULL,
[auth_srid] [int] NULL,
[srtext] [varchar](2048) NULL,
[proj4text] [varchar](2048) NULL
) ON [PRIMARY];

with entries like:

srid    auth_name   auth_srid   srtext  proj4text
94326   epsg    4326   +proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs      GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]

Note that you can get these from spatialreference.org, like this: http://spatialreference.org/ref/epsg/4326/postgis/ just modify it for SQL Server syntax.

With PostgreSQL ogr_fdw

Install PostgreSQL foreign data wrapper ogr2ogr

apt install postgresql-10-ogr-fdw

See https://github.com/pramsey/pgsql-ogr-fdw for the best reference about ogr_fdw, the spatial swiss army knife for PostgreSQL !

Use it in your database ! If you have no spatial tables, but only geometryless tables to retrieve, specify the config_options with value MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=NO.

-- Create extension
CREATE EXTENSION ogr_fdw;

-- Create server
CREATE SERVER IF NOT EXISTS fdw_mssql_myname
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
    datasource 'MSSQL:driver=SQL Server;server=DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;tables=a_test,b_test',
    format 'MSSQLSpatial', 
    config_options 'MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=YES'
);

-- Create schema
DROP SCHEMA IF EXISTS fdw_myname;
CREATE SCHEMA IF NOT EXISTS fdw_myname;

IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER fdw_mssql_myname
INTO fdw_myname
OPTIONS (
    launder_table_names 'true',
    launder_column_names 'true'
)
;

-- Test
SELECT * FROM fdw_semerap."a_test" LIMIT 1;
@nyalldawson

This comment has been minimized.

Copy link

@nyalldawson nyalldawson commented Dec 5, 2018

FYI: Drag and drop of layers to mssql works via the qgis browser

@mdouchin

This comment has been minimized.

Copy link
Owner Author

@mdouchin mdouchin commented Dec 11, 2018

@nyalldawson @NathanW2 could your read my updated "Open data in QGIS" chapter and tell me if it seems ok ? I would like to propose a PR for QGIS documentation based on this gist

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.