2

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: enter image description here

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: enter image description here

anyone know why it isn't filling the up?

3
  • Hmm.. "The GEOM_TYPE layer creation option can be set to one of “geometry” or “geography”. If this option is not specified the default value is “geometry”. So as to create the geometry column with “geography” type, this parameter should be set “geography”. In this case the layer must have a valid spatial reference of one of the geography coordinate systems defined in the sys.spatial_reference_systems SQL Server metadata table. Projected coordinate systems are not supported in this case." - gdal.org/drivers/vector/mssqlspatial.html - did you try a different shape file by any chance? Jan 8, 2021 at 11:48
  • hi i don't have other shapefiles to try. As I said I have already tried changing the parameter to geography with bad results. My sys.spatial_reference_systems has the data I just added to my question.
    – James
    Jan 8, 2021 at 13:36
  • could you elaborate what full page of color in the "spatial results" pane looks like? is the whole globe filled with shapes? Jan 10, 2021 at 3:39

1 Answer 1

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.

1
  • adding this worked: -dialect SQLite -sql "select ST_Reverse(geometry), attribute1,attribute2 from CAPZONE" thank you
    – James
    Jan 18, 2021 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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