2

I'm trying to import a shapefile into SQL Server 2019, running his command:

ogr2ogr -progress -f "MSSQLSpatial" "MSSQL:server=XXXX;database=test;UID=XXXXX;PWD=XXXX" "MYFILE" -geomfield "geom" -a_srs "EPSG:4326" -lco PRECISION=NO

it is actually importing everything (all columns) except the geometry column. I've also tried omitting the geomfield parameter with the same result.

1
  • I suspect that your geomfield is the_geom
    – Ian Turton
    Dec 3, 2020 at 18:47

3 Answers 3

2

Ok so it seems I've found the solution. The problem is that the current version of QGIS (GDAL version is the real problem) has a bug and it doesn't create the geography\geometry column in MSSQL...

Installing QGIS 3.4.11 that has GDAL 2.4.1 and using the following command worked:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=[your server];database=[Your DB];tables=[your table name];UID=[user];PWD=[pwd];driver={SQL Server}" "[your shp file]" -lco GEOMETRY_NAME=GEOM -lco GEOM_TYPE=GEOMETRY -nln "[your table name again]" -a_srs "EPSG:4326" -overwrite -progress -skipfailures -lco UPLOAD_GEOM_FORMAT=wkb

part of the solution was found here (unfortunately this result was far down in the google search): https://github.com/OSGeo/gdal/issues/3140

1

I am having similar problems

qGIS 3.18 on Windows 10 64bit. ogr2ogr --version GDAL 3.1.4, released 2020/10/20

SQL Server version: 15.0.xxxx.xx (SQL Server 2019)

When executing ogr2ogr for a shapefile I get all the non-spatial attributes and rows but all the geography columns are null.

I then updated to qGIS 3.20.1 with GDAL 3.3.1, released 2021/06/28 and now I get no rows.

How do I set up SQL tracing of the ogr2ogr command?

Can anyone suggest what is going on?

regards Simon

0

If your shapefile is in EPSG:4326 (geographic cs) and you want to upload it then command is:

ogr2ogr -progress -f "MSSQLSpatial" "MSSQL:server=XXXX;database=test;UID=XXXXX;PWD=XXXX" -a_srs "EPSG:4326" -lco "GEOM_TYPE=geography" "C:\folder\your_file.shp" -nln "your_sql_table_name"

If your shapefile is in some projected CRS and you want to upload it into SQL as EPSG 4326 then command is:

ogr2ogr -progress -f "MSSQLSpatial" "MSSQL:server=XXXX;database=test;UID=XXXXX;PWD=XXXX" -t_srs "EPSG:4326" -lco "GEOM_TYPE=geography" "C:\folder\your_file.shp" -nln "your_sql_table_name" 
5
  • Hi, Thank you for your answer. Unfortunately both of your commands give the same result (no geometry column is inserted in SQL). My column in SQL is of data type "geography", that's correct right? I used to load these tables using Shape2Sql.exe when we were running 2008 SQL. Now it's not working with 2019 SQL so I started looking at OGR. I don't know if this is relevant but while OGR is inserting data into SQL I get warnings similar to this one: Warning 1: Ring Self-intersection at or near point 12.514441398000201 41.96990805900009
    – James
    Dec 4, 2020 at 7:44
  • @James Then I would look at the version of GDAL/OGR you are using and check if there are some issues with SQL Server 2019. I am using OGR2OGR quite often to upload SHP to SQL Server 2012 and 2016. I have no 2019 version to check
    – sys49152
    Dec 4, 2020 at 8:35
  • @James those are the warnings for invalid geometries which not follow OGC rules. Try to fix them with QGIS tools and then perform upload again.
    – sys49152
    Dec 4, 2020 at 8:40
  • I re ran the commands but this time running a SQL trace too and I see that it's actually sending null in the geom field. I'm expecting it to load something like this: 0xE6100000010480000000F87B9BFEECD74340C0C6681D55BD214078E1404816D8434060BB61DBA2BC214080BC749318D84340009E7B0F97BC2140E04B378941D84340808AABCABEBB2140F880B22957D84340207250C24CBB21401847C9AB73D84340C0024356B7BA2140B86D516683D843402043739D46BA2140384F92AE99D8434040ADDD76A1B92140C006B64AB0D84340C06FD39FFDB82140E0166536C8D84340A0105839B4B8214020FDF675E0D843408062F3716DB82140600DA661F8D84340A0B48EAA26B8214038718...
    – James
    Dec 11, 2020 at 14:10
  • ok so I've started a different approach and downloaded QGIS3. I can correctly see my shapefile and I was able to connect to SQL too. I then tried dragging the layer containing my shape file onto the SQL connection but i get a message saying: Failed to import some layers! CAPZONE: Not a valid layer!
    – James
    Dec 11, 2020 at 14:36

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.