OGR2OGR import to SQL from geometry to geography

Question

I'm trying to import a shapefile to SQL Server 2019. This command works:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=[myserver];database=test;tables=CAPZONE;UID=[myuser];PWD=[mypassword];driver={SQL Server}" "[myshapefile]" -lco GEOMETRY_NAME=GEOM -lco GEOM_TYPE=GEOMETRY -nln "CAPZONE" -a_srs "EPSG:4326" -overwrite -progress -skipfailures -lco UPLOAD_GEOM_FORMAT=wkt 

But it creates the column with the data in SQL of type GEOMETRY if I change the parameter GEOM_TYPE=GEOMETRY to GEOM_TYPE=GEOGRAPHY it also works (as in I don't get any errors) but the difference is that with the GEOMETRY type the shapes are correct when I check in SQL instead with the GEOGRAPHY type I only get a full page of color in the "spatial results" pane.

I need the GEOGRAPHY type to be able to run some SQL functions not available with the GEOMETRY type. I've also tried importing the GEOMETRY type and then using some SQL functions to convert to GEOGRAPHYbut the result is the same.

this is my [spatial_ref_sys] table:

Any ideas?

-----UPDATE------------

Adding parameter -nlt "LINESTRING" or "MULTILINESTRING" or "COMPOUNDCURVE" finally draws the right shapes but they aren't filled with colour for some reason:

anyone know why it isn't filling the up?

Answer 1

Most likely the problem when you import as Geography is the inversion of polygons. The issue is, geography polygons on sphere have complimentary ones taking all other area on sphere. MS SQLServer and other databases that support Geography type usually use some orientation rule to distinguish between the two complimentary polygons that can be described by a linestring boundary. I could not find Microsoft documentation, but here is Google BigQuery description (they use same orientation rule):

https://cloud.google.com/bigquery/docs/gis-data#polygon_orientation

Unfortunately, ogr2ogr is not smart enough yet to handle this correctly, it just converts formats without looking deeply into semantics, here is the bug I've found:

https://github.com/OSGeo/gdal/issues/1128

You can fix this using ST_Reverse call as described at the bottom of this bug, or after importing by calling ReorientObject method in MSSqlServer:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/reorientobject-geography-data-type

ST_Reverse is probably preferable solution, because if some polygon happens to have a hole in it - import would fail if you don't fix orientation before polygon reached MSSqlServer.