2

I am trying to import a Shapefile with polygon features into MSSQL database with this command:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=***;uid=***;pwd=***;database=***" "D:\GeoData\processed\usage_land.shp" -a_srs "EPSG:4326" -nlt "POLYGON" -nln "import_usage" -progress -skipfailures

Import does work so far but the geometry column ogr_geometry stays NULL. Any ideas how to get this feature working? Which command am I missing?

5
  • what does ogrinfo say about the shapefile - most likely issue is multipolygon to polygon column
    – Ian Turton
    Jan 20 at 15:10
  • Polygon - tried other geometry types in -nlt command as well (geometry, multipolygon) Jan 20 at 15:15
  • do you end up with a geometry column called the_geom?
    – Ian Turton
    Jan 21 at 8:21
  • No, its ogr_geometry initially, but I added -lco "GEOMETRY_NAME=geometry" for my purpose Jan 21 at 8:51
  • I would try without --skipfailures and see if there is an error
    – Ian Turton
    Jan 21 at 9:14
2

Similar symptoms, but I had to add --config MSSQLSPATIAL_USE_BCP NO to my ogr2ogr instruction. I think a consequence of this parameter is that the ogr2ogr process runs slower than it could under ideal circumstances.

Of note: I installed GDAL using the OSGEO installer, which for some reason didn't include anything in the gdalplugins folder. Meanwhile, that 'USE BCP' parameter is true/yes by default, and it seems to need something from the plugins folder. Hopefully this will help someone.

5
  • Ogr2ogr stopped working for me when I updated to 3.3.1. It created the Table in MSSQL but imported no rows and reported no error. Adding --config MSSQLSPATIAL_USE_BCP NO fixed the issue. But I have no idea why....
    – steloe
    Sep 27 at 8:30
  • 1/2 @steloe, do you have anything in your gdalplugins directory? I'm curious how you installed GDAL. I don't usually use the OSGEO installer. But in this case I did because it was a Windows system (which makes sense considering I was using it for MSSQL activity). Anyway it seems a byproduct of the way I used the OSGEO installer was that I didn't get the plugins, which might include something necessary to support the default --config MSSQLSPATIAL_USE_BCP NO option.
    – elrobis
    Sep 28 at 13:12
  • 2/2 I suspect that--had I anticipated this scenario--I probably could have made different selections when running the OSGEO installer to make sure I got the gdalplugins I needed. I'm not sure if there is a standard set of plugins, or if they need to be deliberately selected one at a time. I forget where I learned this, but that USE_BCP seems to depend on some sidecar library that GDAL ropes-in when necessary, and (if I understand this correctly), the absence of this library breaks the default option. The default should arguably not require the external lib, but that's another convo. :)
    – elrobis
    Sep 28 at 13:22
  • Ah. Ok, I think it's ogr_MSSQLSpatial.dll. See here.
    – elrobis
    Sep 28 at 13:25
  • Oh, interesting. I have to admit, I diddn't dive that deep into the gdal internals... I used the osgeo installer, advanced setup and chose Qgis 3.20 and some minor tools. My gdalplugins directory ist empty.
    – steloe
    Sep 30 at 8:14
1

Looks like I found the answer: https://stackoverflow.com/questions/52259696/ogr2ogr-does-not-create-tables

I was missing the -lco UPLOAD_GEOM_FORMAT=wkt and this seemed to do the job!

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.