3

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)
Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
4
  • Skipfailures should not be needed and if you need it you'll lose some data. Commented Sep 10, 2018 at 20:57
  • @user30184 for some reason that solved an issue I had, but I've taken it out of the above. Thanks! Commented Sep 10, 2018 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. Commented Sep 11, 2018 at 12:53
  • @RAVIBANSAL glad we figured it out!!! Commented Sep 12, 2018 at 15:47

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.