ogr2ogr everts polygon geographies when writing shapefile to MSSQL Server Spatial

[seamus92]

Expected behavior and actual behavior.

When using ogr2ogr to convert a shapefile containing polygon features into an MSSQL Server spatial table with geography data, said polygons features are everted when written to SQL. By this I mean the output features cover every part of the globe except those described by their shapefile original equivalents. This seems to be because the ring order in the shapefile is preserved verbatim when written to SQL.

The shapefile specification (https://www.esri.com/library/whitepapers/pdfs/shapefile.pdf) specifies that the internal portion of a polygon is that to the right of an observer walking along the polygon bounds in vertex order. SQL server spatial appears to use the opposite convention (see https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/reorientobject-geography-data-type?view=sql-server-2017). Thus, preserving the point order of the bounds verbatim, instead of flipping it, causes the problem.

It is possible to fix the features after import into SQL server (using the the ReorientObject() function detailed in the link above), but it is needlessly inconvenient, especially when attempting to fix multi-polygons, for which simply calling ReorientObject() does not work. (In my experience each individual part of the multi must be re-oriented on its own and then UnionAggregated with the rest in order to reproduce the original shapefile feature).

Expected behaviour is that shapefile storage of polygon geographies is intelligently converted to SQL Sever geographies by ogr2ogr, such that converted features are identical to originals.

Steps to reproduce the problem.

ogr2ogr -f MSSQLSpatial "MSSQL:server=MyServer;database=MyExampleDatabase;trusted_connection=yes" \path\to\my\file\Land_Parcels.shp -lco GEOMETRY_NAME=GEOM -lco GEOM_TYPE=GEOGRAPHY -nln LandParcels_Everted

Note: shapefile must be a polygon file (for the purposes of demonstration, ideally containing relatively small features which cover much less than half the Earth's area; after conversion, select a feature and observe its outer bounds; they will extend from -180 to +180 longitude and ~ -90 to +90 latitude.)

Operating system

Windows 10 Pro 64 bit

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64) Sep 23 2016 16:56:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

GDAL version and provenance

GDAL 2.2.1, released 2017/06/23, from the OSGeo4W Network Installer (64 bit) found at https://qgis.org/en/site/forusers/download.html

[rouault]

CC @szekerest

[seamus92]

This also seems to affect conversion of FGDB polygons to MSSQL Server spatial

[lathamfell]

We are having this issue with FileGDB conversion to SQL Server spatial. Has anyone solved this, beyond exploding into polygons and individually re-orienting?

[jratike80]

Does it work right with -lco GEOM_TYPE=GEOMÈTRY? As a workaround I would try the following

ogr2ogr -f MSSQLSpatial "MSSQL:server=MyServer;database=MyExampleDatabase;trusted_connection=yes" \path\to\my\file\Land_Parcels.shp -lco GEOMETRY_NAME=GEOM -lco GEOM_TYPE=GEOGRAPHY -nln LandParcels_Everted -dialect SQLite -sql "select ST_Reverse(geometry), atrribute_1, attribute_2 from Land_Parcels"

[qjohnchen]

I closed the duplicated issue #3497.

this is the original command I use in OSGeo4Win Shell:

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=111.123.45.67;database=MyDB;trusted_connection=yes;Driver={SQL Server Native Client 11.0}" "E:\MyFolder\polygons.shp" -progress -nln "Polygons" -lco "GEOM_TYPE=geography" -lco "GEOM_NAME=Poly_Imported" -lco "SPATIAL_INDEX=No" -lco "PRECISION=No" -lco "LAUNDER=No" -gt 5000 It creates polygons with incorrect ring orientation.

I tried Jukka's suggestion. It works for SQL server too. However, when using -dialect SQLLite, I cannot see the progress. That's a big loss if I import millions of polygons. Also, I am not sure if it works for multi-polygon or donut shaped polygon. I have seen before that not every single polygon in the multi-polygon need to be reversed. My code (after import) is to check the area of the polygon and only Reorient those extremely large.

[jratike80]

I have verified that another Spatialite function ST_ForcePolygonCCW is included in Spatialite version 5.0 and I recommend to use that instead of ST_Reverse if possible.

As a background, in shapefiles the polygons are ordered clockwise and I guess that keeping that order is not a problem with MS SQL geometry type but just with geography.

[elpaso]

I'm trying to reproduce the issue but I've had no luck so far using latest gdal master.

I have created a test (multi)polygon shapefile (in EPSG 432):

Then I have imported it in MSSQL with ogr2ogr:

-f MSSQLSpatial 'MSSQL:server=localhost;port=1433;database=qgis_test;UID=sa;PWD=<*********>' /home/xxx/Maps/gd_gh_1128/gd_gh_1128.shp -lco GEOMETRY_NAME=GEOM -lco GEOM_TYPE=GEOGRAPHY

Running ogrinfo on the shapefile and the created layer shows no difference in the geometries of the two features, am I missing something?