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.
uid
I'm assuming "user id" I've seen some examples that say username though.