Invalid geographies imported to SQL Server using ogr2ogr

[leighghunt]

When attempting to import polygons as geographies into SQL Server, I'm getting invalid instances. Importing them as geometries appears to work fine.

I suspect this is because the ring polygons are getting imported with the incorrect orientation.

More info:

Importing as geometry:

C:\Users\Administrator\Documents>ogr2ogr -f "MSSQLSpatial" "MSSQL:server=.;database=geo;trusted_connection=yes" "buildings-subset-small.shp"

select ogr_geometry.STArea(), ogr_geometry.STAsText(),  * from buildings_subset_small
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ogr_fid     ogr_geometry                                                                                                                                                                                                                                                     objectid                                elevation                               feat_code       source
---------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------- ------------------------------
2.06886988962651E-06   POLYGON ((174.7848762372297 -41.273814856209142, 174.78482328530623 -41.273750644256559, 174.78477741057449 -41.273683260970913, 174.78473898702057 -41.273613331067629, 174.78470838729984 -41.273541299137264, 174.78468562126773 -41.273467615395006, 174.784 1           0xE6100000010594000000E449C5B41DD96540E2497E5D0CA344C0F2F0B8451DD96540A12BD8420AA344C0D32184E51CD9654096AA970D08A344C0C9A4EF941CD96540B157FAC205A344C0318BC3541CD9654030F3BA6603A344C0901F05251CD965407E51A0FC00A344C045183B061CD96540253F6988FEA244C0E852D2CB1B 75026                                   26.800000000000001                      Building        photo 2006, 0.30m
1.28582541947253E-07   POLYGON ((174.78528995348302 -41.276238379939144, 174.78515741662858 -41.2757304670763, 174.78533649622491 -41.275554148738159, 174.78550070980495 -41.276209011138675, 174.78528995348302 -41.276238379939144))                                                 2           0xE6100000010505000000C882651821D965408CDE7BC75BA344C03654720220D96540404DCD224BA344C085F4007A21D96540C38DBC5B45A344C01B7362D222D96540BED91ED15AA344C0C882651821D965408CDE7BC75BA344C000000000000000000000000000000000000000000000000000000000000000000000000000 64505                                   12.800000000000001                      Building        photo 2002
2.83356257568812E-06   POLYGON ((174.78466636839821 -41.273972500116869, 174.78485299505013 -41.273827688069012, 174.78489752472771 -41.273899595798909, 174.78498698788982 -41.273984698492157, 174.78517162099189 -41.274093975787039, 174.78528729998146 -41.274143295698927, 174.78 3           0xE610000001051F00000053D1A4FC1BD96540AE00E88711A344C00E3D07841DD96540737F22C90CA344C057ED69E11DD96540062C57240FA344C0B718089D1ED965406AD03BEE11A344C00A3D3C2020D965409A04EB8215A344C04EECD41221D96540E7B8A42017A344C04D040EA722D96540E5BD55A718A344C0BAE9BCE724 73569                                   11.330000000000000                      Building        photo 2006, 0.30m

(3 row(s) affected)

All appears fine, and display looks OK, however areas aren't in square metres as I want.

When attempting to force the geometry type to be geography rather than geometry, I'm getting invalid geographies:

C:\Users\Administrator\Documents>ogr2ogr -f "MSSQLSpatial" "MSSQL:server=.;database=geo;trusted_connection=yes" "buildings-subset-small.shp" -lco "GEOM_TYPE=GEOGRAPHY"

select ogr_geometry.STAsText(), ogr_geometry.STArea() , * from buildings_subset_small

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------- ------------------------------
Msg 6522, Level 16, State 1, Line 5
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException: 
   at Microsoft.SqlServer.Types.SqlGeography.STArea()
.

If I fix this using MakeValid(), I get the following:

select ogr_geometry.MakeValid().STArea(), ogr_geometry.STAsText() ,ogr_geometry.MakeValid().STAsText(),  * from buildings_subset_small

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         ogr_fid     ogr_geometry                                                                                                                                                                                                                                                     objectid                                elevation                               feat_code       source
---------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------- ------------------------------
19251.4759233594       POLYGON ((174.7848762372297 -41.273814856209142, 174.78482328530623 -41.273750644256559, 174.78477741057449 -41.273683260970913, 174.78473898702057 -41.273613331067629, 174.78470838729984 -41.273541299137264, 174.78468562126773 -41.273467615395006, 174.784 POLYGON ((174.78464308813946 -41.273060449840287, 174.78467093984545 -41.273392726316317, 174.78468562126767 -41.273467615394964, 174.7847083873 -41.273541299137165, 174.7847389870204 -41.273613331067587, 174.78477741057432 -41.273683260971048, 174.7848232 1           0xE6100000020194000000E2497E5D0CA344C0E449C5B41DD96540A12BD8420AA344C0F2F0B8451DD9654096AA970D08A344C0D32184E51CD96540B157FAC205A344C0C9A4EF941CD9654030F3BA6603A344C0318BC3541CD965407E51A0FC00A344C0901F05251CD96540253F6988FEA244C045183B061CD9654098F212A5F3 75026                                   26.800000000000001                      Building        photo 2006, 0.30m
510065621709800        POLYGON ((174.78528995348302 -41.276238379939144, 174.78515741662858 -41.2757304670763, 174.78533649622491 -41.275554148738159, 174.78550070980495 -41.276209011138675, 174.78528995348302 -41.276238379939144))                                                 POLYGON ((174.78528995348302 -41.276238379939144, 174.78515741662858 -41.2757304670763, 174.78533649622491 -41.275554148738159, 174.78550070980495 -41.276209011138675, 174.78528995348302 -41.276238379939144))                                                 2           0xE61000000225050000008CDE7BC75BA344C0C882651821D96540404DCD224BA344C03654720220D96540C38DBC5B45A344C085F4007A21D96540BED91ED15AA344C01B7362D222D965408CDE7BC75BA344C0C882651821D9654000000000000000000000000000000000000000000000000000000000000000000000000000 64505                                   12.800000000000001                      Building        photo 2002
510065621684630        POLYGON ((174.78466636839821 -41.273972500116869, 174.78485299505013 -41.273827688069012, 174.78489752472771 -41.273899595798909, 174.78498698788982 -41.273984698492157, 174.78517162099189 -41.274093975787039, 174.78528729998146 -41.274143295698927, 174.78 POLYGON ((174.78466636839821 -41.273972500116869, 174.78485299505013 -41.273827688069012, 174.78489752472771 -41.273899595798909, 174.78498698788982 -41.273984698492157, 174.78517162099189 -41.274093975787039, 174.78528729998146 -41.274143295698927, 174.78 3           0xE610000002251F000000AE00E88711A344C053D1A4FC1BD96540737F22C90CA344C00E3D07841DD96540062C57240FA344C057ED69E11DD965406AD03BEE11A344C0B718089D1ED965409A04EB8215A344C00A3D3C2020D96540E7B8A42017A344C04EECD41221D96540E5BD55A718A344C04D040EA722D965409A00639719 73569                                   11.330000000000000                      Building        photo 2006, 0.30m

(3 row(s) affected)

I noticed the STArea(), whilst now in the correct units (m² rather than degrees²) was very different for two of the polygons, and wondered if the polygons are getting imported with the incorrect ring orientation (e.g. turned inside out).

[leighghunt]

Whilst troubleshooting, I found the advice at ​http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx, and the following workaround appears to work:

If necessary, before call to ogr2ogr, drop tables:

DROP TABLE buildings_subset_small
DROP TABLE buildings_subset_small_geog

Import using ogr2ogr to import as geometry, NOT GEOGRAPHY.

C:\Users\Administrator\Documents>ogr2ogr -f "MSSQLSpatial" "MSSQL:server=.;database=geo;trusted_connection=yes" "buildings-subset-small.shp"

Run fix on geometries, and then copy to geography field containing table. Note that without the UPDATE part below, we're still left with invalid geographies.

-- Modified version of http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx

UPDATE buildings_subset_small 
  SET ogr_geometry = ogr_geometry.STUnion(ogr_geometry.STStartPoint()); 

-- 
-- Create table with Geography column 
-- 

CREATE TABLE buildings_subset_small_geog ( 
  id   INTEGER, 
  geog GEOGRAPHY); 
-- 
-- Convert from Geometry to Geography using Well Known Text 
-- 
INSERT INTO buildings_subset_small_geog 
  SELECT buildings_subset_small.ogr_fid, GEOGRAPHY::STGeomFromText(buildings_subset_small.ogr_geometry.STAsText(),4326) 
  FROM buildings_subset_small; 

SELECT geog.STArea(), * from buildings_subset_small_geog

                       id          geog
---------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
19251.4759225091       1           0xE6100000010494000000C0092F2818A344C0CDAF029725D96540B63C863616A344C02AFC5F9925D96540D0B1855116A344C0C38E4C3E25D965402D03A15615A344C01AF19D4625D96540033D0D2C15A344C080C3C72926D965403FCBF6D714A344C03FC56E0727D96540F08BD3DF13A344C067D0713728D96540ECAD007D12
1196.44645786285       2           0xE61000000104050000008CDE7BC75BA344C0C882651821D96540BED91ED15AA344C01B7362D222D96540C38DBC5B45A344C085F4007A21D96540404DCD224BA344C03654720220D965408CDE7BC75BA344C0C882651821D9654001000000020000000001000000FFFFFFFF0000000003
26366.1477432698       3           0xE610000001041F000000039A3D92A4A344C00BAF6B9902D965400E3185FF77A344C08F767BC10DD9654024CF5C5176A344C011D562B910D9654050318DD05BA344C0ABE8CC5C17D96540D6FAC87958A344C003C185E017D9654055CB997A4EA344C0AE80DCFE18D96540463E3A4249A344C001E3A4F219D96540D501C55A36

(3 row(s) affected)

The areas above are now accurate.

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.