Import shapefile in SQL Server (OGR2OGR)

Question

I'm importing a shapefile into SQL Server '12 via the OGR2OGR tool and am getting a database connection error.

I followed the example from this article.

What I ran:

ogr2ogr -overwrite -f ESRI Shapefile "MSSQL:Server=.\MSSQLSERVER2012;database=ap;trusted_connection=yes" "C:\Users\johndoe\Desktop\myshapefile.shp"

The error:

SQL Server does not exist or access denied

---

I'm assuming I need to pass some type of credentials? and the server string is probably wrong where would it be located.

Question: Why am I getting this error?

Answer 1

One thing that I found was that I was not able to upload shapefiles directly to an MSSQL instance that was on a separate server; my workaround was to download sql express to my local hard drive, do the OGR2OGR script there, then use SQL Server Management studio to import the data from my local computer to the server on the network domain. My syntax was (from the command prompt in the directory that ogr2ogr.exe is in):

ogr2ogr -progress -f "MSSQLSpatial" "MSSQL:server=(your server name here);database=(database name on server to publish shapefile to);trusted_connection=yes" "(directory and actual name of shapefile with .shp extension)" -a_srs "(the ESPG projection code of the shapefile)" -lco PRECISION=NO

Note: the "trusted connection" parameter is used when you are using windows authentication (e.g., when you are on a domain network) as MSSQL login credentials. You may want to check if your mssql server is set up to accept forms of login other than username/pw (you may need to install another instance of sql server; the only place I saw that option was when I ran the original setup.exe)

Answer 2

I've managed to import successfully on remote server. Here goes procedure

1. Install QGIS Desktop 3.10.5..or whatever version is actual.
2. How to find out what goes to parameter -a_srs, in my casa that is : -a_srs "EPSG:3765":

Open QGIS Desktop 3.10.5, open new project and drag-drop your shape file to Layers left sidebar. Right click on that layer and go to Properties. There you will be able to read your -a_srs information.

3. Run command in CMD:

   C:\Program Files\QGIS 3.10\bin>ogr2ogr -progress -f "MSSQLSpatial" "MSSQL:server=xxx.xxx.xxx.xxx,yyyy;database=yourDB;UID=yourUsername;PWD=yourPassword" "C:\Users\Username\Desktop\ShapeFiles\Name_of_shape_file.shp" -a_srs "EPSG:3765" -lco PRECISION=NO

Output: 0...10...20...30...40...50...Warning 1: Ring Self-intersection at or near point 664451.64629999956 5053409.8562000087 60...70...80...90...100 - done.

Where xxx.xxx.xxx.xxx - IP address of your server, and

yyyy - port number of your server if you are running SQL Server on different port than standard (I think you can omit that in case you are)

Answer 3

To upload a shapefile to a local or remote Microsoft SQL Server database (or Azure SQL) using GDAL's ogr2ogr, open a new command prompt and use this:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=your_server_url,1433;database=your_database_name;uid=your_username;pwd=xxxxxxx" -lco SCHEMA=dbo -nln new_table_name "C:\path_to_file\shapefile.shp" -nlt POLYGON -lco GEOMETRY_NAME=geometry_column_name? -a_srs "EPSG:27700" -overwrite -progress -lco OVERWRITE=YES

Note that the default port for SQL Server databases is 1433. Also I have set the desitination schema as the default 'dbo' schema. As I am working with data within the British National Grid projection, I am using the EPSG/SRID of 27700. In my example I am uploading a polygon shapefile, so I set the -nlt flag to POLYGON. You can change all of these parameters to match your setup.

If you are a python user, you can pass the ogr2ogr command into a python variable and use subprocess to call it. It is much easier to modify GDAL processes within a script than within a cmd prompt:

import os
from subprocess import call

os.environ['PROJ_LIB'] = r'C:\Program Files\GDAL\projlib'  # not essential

in_file = r'C:\path_to_file\shapefile.shp'
out_schema = 'dbo'
out_nln = 'new_table_name'
out_gname = 'shape'
out_gtype = 'POLYGON'

host = 'your_server_url'
port = '1433'
db = 'your_database_name'
user = 'your_username'
pw = 'xxxxxxx'
command = fr'ogr2ogr -f "MSSQLSpatial" "MSSQL:server={host},{port};database={db};uid={user};pwd={pw}" -lco SCHEMA={out_schema} -nln {out_nln} "{in_file}" -nlt {out_gtype} -lco GEOMETRY_NAME={out_gname} -a_srs "EPSG:27700" -overwrite -progress -lco OVERWRITE=YES'

print(command)
call(command)

I find it useful to print the ogr2ogr command out in the python console so I can quickly copy it for use in command line if I so desired. In this example I 'f' string the variables (ie.{variable}), but you may wish to %s string them instead.