ESRI Shapefile not loading into SQL Server
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?
I've managed to run the Vertices Counter plugin and the first feature that causes an issue has 5,558,760 vertices.
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"
I eventually gave up trying to load the large files - it does not seem to be possible with any tool. The other side of the coin was that the features I did manage to load took over 1 hr to do a simple
I cut up the large shapefiles using
ogr2ogrbased on county geometries and then uploaded them and associated them with the county data in order to create a smaller set of geography objects to query.
This method worked like a charm and
STIntersectsquery performance for the most complex shapefile is still in the region of 600ms which is acceptable for our purposes.
I resolved with the command: -FieldTypeToString All
The problem in my case was a column with numeric conversion problem. So I turned all columns (except for the geography, of course), and it resolved.
I used to have the same problem, the fastest way was to use this tool.
It's quick and easy to work with.
I found that I had this error with a map of the administrative areas of the world.
The solution that worked for me was to downgrade from QGIS 2.18 (GDAL Version 2.1.1) to GGIS 2.12 (GDAL Version 1.11.3). Then it just worked fine.