3

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.

Improve this question
3
  • It could be basic issues with the spatial data such as very short lines, very small polygons or records without spatial data. Could you run a query on the dataset to see if it contains small features? Try having a look at the feature referred to in the error message
    – Peter Horsbøll Møller
    Nov 23, 2015 at 14:40
  • The first feature that it crashes out on is quite large. I'm not sure how to view details of features in QGIS but I'll investigate and update with an edit.
    – toadflakz
    Nov 24, 2015 at 9:12
  • That's a lot of vertices - I don't know if ogr2ogr or SQL would run into an issue with that. Maybe try running it with -skipfailures and see what happens - you should get a set of features imported, and you can compare that to the full set to see if the number of vertices, or something else, is a common feature of the skipped features.
    – karl
    Apr 22, 2016 at 14:01

6 Answers 6

Reset to default
2

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"
Improve this answer
3
  • This did not work.Same error as before unfortunately.
    – toadflakz
    Nov 20, 2015 at 17:37
  • Do you have permission to write to the SQL server?
    – Rita A.
    Nov 20, 2015 at 19:04
  • Yes. As stated in the question, I have been using this method on a number of other files to this specific database and server without issues up until now. I think the issue is with the file, not the server. I'm looking for a way to diagnose why it's not loading.
    – toadflakz
    Nov 23, 2015 at 9:15
1

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.

Improve this answer
1

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.

Improve this answer
1

for me resolved by adding --config MSSQLSPATIAL_USE_BCP NO

Improve this answer
0

I used to have the same problem, the fastest way was to use this tool.

http://www.dknezevic.com/blog/2014/10/import-shapefiles-into-sql-server.html

It's quick and easy to work with.

Improve this answer
2
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.