Friday, 1 November 2019

qgis - 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?


Update:


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



Answer



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 STIntersects query.


Solution:



I cut up the large shapefiles using ogr2ogr based 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 STIntersects query performance for the most complex shapefile is still in the region of 600ms which is acceptable for our purposes.


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...