[GIS] ESRI Shapefile not loading into SQL Server

gdalogr2ogrqgisshapefile

I have been loading a number of shapefiles into SQL server 2012 using ogr2ogr.exe and the MSSQLSpatial driver. Up until this point, I have been very successful with doing so.

However, I have now run into difficulty with certain files and the errors thrown by various tools are not terribly helpful in identifying what exactly is wrong with the shapefile.

The files in questions are between 270MB and 290MB big and most have a single layer with more than 80,000 features in that layer. However, size and feature count is not itself an issue, I believe, as a file that is part of this data set is 280MB big and has 111,000(ish) features in it's particular layer.

The problem files all load correctly and are viewable in QGIS, including any files/features thrown out by ogr2ogr.exe as causing an issue. They also load into MapInfo successfully and can be edited there just fine. Re-exporting the layer from QGIS as a shapefile does not fix the issue with loading via ogr2ogr.exe

I've also attempted to load it using Shape2SQL and that program just hangs outright after it reaches a certain point with no error messages thrown at all.

The specific message thrown by ogr2ogr.exe is:

ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

ERROR 1: Unable to write feature < feature no > from layer < layer name >.

ERROR 1: Terminating translation prematurely after failed
translation of layer < layer name > (use -skipfailures to skip errors)

The command line statement for ogr2ogr.exe I'm using is:

ogr2ogr.exe -overwrite -f MSSQLSpatial "MSSQL:server=…;database=…;trusted_connection=yes" "< filename >.shp" -t_srs "EPSG:4326" -lco "GEOM_TYPE=geography" -lco "SCHEMA=ETL" -lco "OVERWRITE=YES" -progress

The shapefiles are in WGS84 projection.

Does anyone know what tool I might be able to use from the GDAL toolkit, QGIS or MapInfo to fix the errors in these shapefiles so that they can be loaded by ogr2ogr.exe into SQL Server 2012? Or alternately is it my ogr2ogr.exe arguments?

Update:

I've managed to run the Vertices Counter plugin and the first feature that causes an issue has 5,558,760 vertices.

Best Answer

This worked for me in the ogr shell.

First change your directory to the folder that contains your shapefile (cd full path to shapefile folder)

ogr2ogr -overwrite -f "MSSQLSpatial" "MSSQL:server=...;database=...;trusted_connection=yes" test.shp -a_srs "EPSG:4326" -nln "test"
Related Question