2

I am trying to need Electoral District boundary data available at the below-mentioned link to SQL Server.

https://www.elections.on.ca/en/voting-in-ontario/electoral-district-shapefiles/open-use-data-product-licence-agreement/download-shapefiles.html

I downloaded the 2018 General Election files and checked the CRS through QGIS 2.10.1 as well as the http://prj2epsg.org/search website. QGIS shows the CRS as EPSG:5320, NAD 83. Whereas the prj2epsg.org shows a number (4540!) of EPSG matches!

Anyways, I tried to change the EPSG:5320 to EPSG:4326 using ogr2ogr as well as QGIS.

For changing the ESPG through QGIS, I followed the steps through 10 as mentioned in the link below.

https://gist.github.com/peter-rose/838a8fe3a725261527334f04cfc3dc00

I also tried to convert the EPSG:5320 to EPSG:4326 using ogr2ogr as well.

ogr2ogr -f "ESRI Shapefile" -t_srs EPSG:4326 -s_srs EPSG:5320 ELECTORAL_DISTRICT_MODIFIED.shp ELECTORAL_DISTRICT.shp

But, I get number of warnings in ogr2ogr with message, even though the New file is created and :

Warning 1: Value ...... of field structural of feature ..... not successfully written. Possibly due to too larger number with respect to field width

Afterward, in both cases, I confirm the EPSG in QGIS. It shows the EPSG:4326, WGS84.

But, when I try to load this converted shapefile to local DB in SQL Server, using the below-mentioned command:

ogr2ogr -f MSSQLSpatial "MSSQL:server=RBANSAL\SQLEXPRESS;database=MySpatial;trusted_connection=yes" C:\Users\rbansal\Desktop\ShapeFile\FED_CA_2_2_ENG_Modified.shp -nln "Spatial_Data" -progress

I get the following error.

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)

I need help with 2 questions,

Q1: Am I converting the EPSG:5320 to 4326 correctly?

Q2(a) If not, is there any other way?

Q2(b) If I am converting it correctly then why am I getting the error while loading the shapefile to SQL Server?

I tried as per @DPSSpatial's solution:

ogr2ogr -overwrite -nln "Spatial_Data" -f MSSQLSpatial "MSSQL:server=RBANSAL\SQLEXPRESS;database=MySpatial;trusted_connection=yes" "ELECTORAL_DISTRICT.shp" -s_srs EPSG:5320 -t_srs EPSG:4326 -lco geom_name=shape -lco UPLOAD_GEOM_FORMAT=wkt

But, again, got the same error:

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)
4

First, delete the SHAPE_AREA, and SHAPE_LENGTH columns, as those are causing the error with INTEGER data.

Next, I have found success with the following syntax of ogr2ogr when loading SHP to MSSQL Server.

Note you can simply skip the coordinate system conversion in QGIS and set the s_srs to 5320 and the t_srs to 4326 and that should work.

ogr2ogr -overwrite -nln "schema.new_table_name" -f MSSQLSpatial "MSSQL:driver={SQL Server};server=servername;database=databasename;trusted_connection=yes" "shapefile_name.shp" -s_srs EPSG:5320 -t_srs EPSG:4326 -lco geom_name=shape -lco UPLOAD_GEOM_FORMAT=wkt 

Make sure you examine the entire line, as there are several options you want to ensure are set correctly.

  • Skipfailures should not be needed and if you need it you'll lose some data. – user30184 Sep 10 '18 at 20:57
  • @user30184 for some reason that solved an issue I had, but I've taken it out of the above. Thanks! – DPSSpatial Sep 10 '18 at 22:11
  • @DPSSpatial, I tried as per your suggestion but still getting the same "Insert command for new feature failed" error messages. I have updated my original question with the new query and the results. Please suggest. – RAVI BANSAL Sep 11 '18 at 12:53
  • @RAVIBANSAL glad we figured it out!!! – DPSSpatial Sep 12 '18 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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