locked
Can't make this geometry valid RRS feed

  • Question

  • Hi there!

    here is a geometry definition:

    SET @myGEOM = 'LINESTRING(46.334692 6.06015,46.334061 6.059791,46.333180999999996 6.0598019999999995,46.33268699999999 6.059699999999999,46.33233899999999 6.059500999999999,46.33191999999999 6.0589699999999995,46.33188799999999 6.058858
    	 ,46.33184999999999 6.058788,46.33180699999999 6.05875,46.33173799999999 6.05874,46.33167899999999 6.058648,46.33134099999999 6.057977999999999,46.33127099999999 6.057892,46.33115799999999 6.057897,46.32727999999999 6.061159,46.32704899999999 6.061078
    	 ,46.32694699999999 6.06081,46.32678099999999 6.060692,46.32658799999999 6.060789000000001,46.326501999999984 6.061009,46.32621799999998 6.061282,46.32520899999998 6.061529,46.323760999999976 6.062049,46.32318099999998 6.06213,46.322311999999975 6.062409
    	 ,46.321560999999974 6.0624519999999995,46.321067999999975 6.062569999999999,46.31934099999997 6.063647999999999,46.31849799999997 6.064071999999999,46.31833199999997 6.063921999999999,46.31820899999997 6.064167999999999,46.31648099999997 6.064961999999999
    	 ,46.314871999999966 6.066190999999999,46.31465199999997 6.066431999999999,46.314469999999965 6.066367999999999,46.31438899999996 6.0665879999999985,46.31386899999996 6.066667999999998,46.311427999999964 6.066608999999998,46.308809999999966 6.066919999999998
    	 ,46.308337999999964 6.066828999999998,46.307109999999966 6.066308999999998,46.30678199999996 6.066238999999998,46.30488899999996 6.066377999999998,46.30448099999996 6.066469999999998,46.30407899999996 6.066721999999998,46.30081699999996 6.069049999999998
    	 ,46.30018999999996 6.069628999999998,46.298890999999955 6.071248999999998,46.298279999999956 6.072187999999998,46.297238999999955 6.074199999999998,46.296101999999955 6.0757499999999975,46.29476099999996 6.077010999999998,46.29459999999996 6.077069999999998
    	 ,46.29447099999996 6.076860999999998,46.29434199999996 6.076897999999998,46.29425099999996 6.0770909999999985,46.29427799999996 6.077251999999999,46.29220199999996 6.079118999999999,46.29030799999996 6.081001999999999,46.28896199999996 6.082127999999999
    	 ,46.28844199999996 6.082760999999999,46.28676199999996 6.085469999999999,46.28630099999996 6.085657999999999,46.28475099999996 6.0857709999999985,46.28451999999996 6.085829999999999,46.28396799999996 6.086167999999999,46.28173099999996 6.088237999999999
    	 ,46.281188999999955 6.088940999999998,46.28012099999996 6.090710999999998,46.27956899999996 6.091386999999998,46.27872099999996 6.091858999999999,46.27638799999996 6.092738999999999,46.27557799999996 6.092899999999999,46.27498799999996 6.093178999999999
    	 ,46.27441899999996 6.093666999999999,46.273527999999956 6.094626999999999,46.272278999999955 6.0964409999999996,46.271881999999955 6.096901999999999,46.27104999999995 6.097357999999999,46.27032099999995 6.098049999999999,46.26972999999995 6.098457999999999
    	 ,46.269638999999955 6.098349999999999,46.269531999999955 6.098349999999999,46.269450999999954 6.098639999999999,46.261479999999956 6.1040899999999985,46.260540999999954 6.104856999999998,46.259827999999956 6.105870999999998,46.259698999999955 6.105838999999998
    	 ,46.259650999999955 6.105961999999998,46.25967799999996 6.106058999999998,46.25978999999996 6.106090999999998,46.26032099999996 6.107968999999998,46.26036999999996 6.108601999999998,46.26020899999996 6.109588999999998,46.25962899999996 6.110907999999998
    	 ,46.25952199999996 6.110907999999998,46.25934999999996 6.111390999999998,46.25836899999996 6.112817999999997,46.25821799999996 6.112570999999997,46.25814899999995 6.112410999999997,46.25808999999995 6.112431999999997,46.257917999999954 6.1126679999999975
    	 ,46.257858999999954 6.112806999999997,46.25777899999996 6.112957999999997,46.258148999999996 6.112411,46.258218 6.112571,46.258369 6.112818,46.258251 6.11299,46.25765 6.114288,46.257269 6.114921,46.255949 6.116638,46.255761 6.116729,46.255719 6.116681000000001
    	 ,46.255579 6.116729,46.255579 6.116890000000001,46.255348 6.1170290000000005,46.254839 6.117179,46.254130999999994 6.1171310000000005,46.254082 6.116949000000001,46.253991 6.116901000000001,46.253841 6.117008000000001,46.252661 6.116520000000001,46.252661 6.116412000000001
    	 ,46.25258 6.116209000000001,46.252452000000005 6.116160000000002,46.252312 6.116251000000002,46.251872000000006 6.115790000000002,46.251389 6.115280000000002,46.250993 6.114562000000002,46.250322000000004 6.111402000000002,46.25000000000001 6.110431000000002
    	 ,46.24909900000001 6.1083820000000015,46.24883100000001 6.107411000000002,46.24877200000001 6.1066600000000015,46.24882000000001 6.105952000000001,46.24945800000001 6.103661000000002,46.24959800000001 6.102899000000002,46.24956000000001 6.101768000000002
    	 ,46.24939900000001 6.101070000000002,46.248680000000014 6.0996700000000015,46.245998000000014 6.095802000000002,46.24546200000002 6.094901000000002,46.24551000000002 6.0948470000000015,46.24556900000002 6.094649000000001,46.24547800000002 6.094418000000001
    	 ,46.24551000000002 6.094139000000001,46.24563900000002 6.093560000000001,46.247441000000016 6.088002000000001,46.247608000000014 6.0868600000000015,46.247720000000015 6.0868220000000015,46.247768000000015 6.086640000000002,46.247650000000014 6.086468000000002
    	 ,46.247532000000014 6.0852400000000015,46.24634200000001 6.081651000000002,46.24646000000001 6.081388000000001,46.24642700000001 6.081190000000001,46.24630900000001 6.081050000000001,46.24614800000001 6.081050000000001,46.24436200000001 6.075637000000001
    	 ,46.24420100000001 6.074420000000002,46.24429800000001 6.073207000000002,46.244679000000005 6.072038000000002,46.245312000000006 6.071078000000002,46.248568000000006 6.0678480000000015,46.253417000000006 6.0637500000000015,46.25371200000001 6.063439000000002
    	 ,46.25413100000001 6.062698000000002,46.25435100000001 6.061722000000002,46.25436100000001 6.061121000000003,46.25404000000001 6.057801000000002,46.25377100000001 6.052538000000002,46.252849000000005 6.047227000000002,46.252452000000005 6.045430000000002,46.25246800000001 6.044808000000002
    	 ,46.25237100000001 6.044620000000003,46.252049000000014 6.044620000000003,46.251668000000016 6.044390000000003,46.244180000000014 6.0381620000000025,46.242882000000016 6.036890000000002,46.24083800000002 6.034632000000002,46.24019900000002 6.033452000000002,46.24017800000002 6.033242000000002
    	 ,46.24006000000002 6.032931000000002,46.23982900000002 6.032711000000003,46.23941100000002 6.032781000000003,46.23923900000002 6.032701000000003,46.23835900000002 6.031692000000003,46.23678200000002 6.030120000000003,46.23583800000002 6.028779000000003,46.235221000000024 6.027690000000002
    	 ,46.23447000000002 6.0261020000000025,46.23347200000002 6.023350000000002,46.23297900000002 6.021269000000002,46.23273200000002 6.019638000000002,46.23250100000002 6.017031000000002,46.23244200000002 6.015438000000002,46.23258200000002 6.014242000000002,46.23277000000002 6.013517000000002
    	 ,46.23386900000003 6.010878000000002,46.23428200000003 6.0092690000000015,46.23436800000003 6.007692000000001,46.23420700000003 6.006469000000001,46.233832000000035 6.005170000000001,46.23288200000003 6.0034220000000005,46.23119200000003 6.000541,46.228500000000025 5.996298
    	 ,46.22846700000002 5.996131,46.22736200000002 5.9942590000000004,46.22711000000002 5.993793,46.22694900000002 5.993369,46.22693800000002 5.993229,46.22691200000002 5.993101,46.22684700000002 5.992999,46.22722800000002 5.992038,46.227319000000016 5.992028,46.22740000000002 5.991931
    	 ,46.22738900000002 5.9917110000000005,46.22751800000002 5.991459000000001,46.228698000000016 5.989388000000001,46.228800000000014 5.988857000000001,46.22882100000001 5.987859000000001,46.22898800000001 5.987221000000001,46.22952900000001 5.985832000000001,46.229572000000005 5.985209000000001
    	 ,46.229272 5.983230000000001,46.229288000000004 5.982651000000001,46.229449 5.982291000000001,46.22939 5.982179000000001,46.229277 5.981948000000001,46.229192000000005 5.980730000000001,46.22858000000001 5.978810000000001,46.22845100000001 5.978928000000001,46.227931000000005 5.980027000000001
    	 ,46.227411000000004 5.980650000000001,46.226552000000005 5.9810680000000005,46.22537200000001 5.98183,46.22480900000001 5.981808,46.22412800000001 5.982221,46.222888000000005 5.98271,46.222577 5.983042,46.221558 5.984501,46.221279 5.984029,46.220619 5.982919,46.220065 5.982172,46.220619 5.982919');
    

    it seems that the last point generate  a 24200 error (the actual geometry is in fact longer).

    I believe I tried all the tricks described here: http://www.beginningspatial.com/fixing_invalid_geography_data

    The actual geometry has been generated by Bing maps as a travel route, Bing Maps manage to display it, but SQL server Denali generates a 24200 error when I try to buffer it.

    Any advice?

     

    Regards.

    Thursday, January 13, 2011 3:39 PM

Answers

  • As a refresher for other folks moving databases from SQL Server 2008 to Denali, you can set the compatibility level of the database with the following Transact-SQL statements:

    --Set database compatibility level to SQL Server Code-Named "Denali"
    ALTER DATABASE <database name> 
    SET COMPATIBILITY_LEVEL = 110;

    --Set database compatibility level to SQL Server 2008
    ALTER DATABASE <database name>
    SET COMPATIBILITY_LEVEL = 100;

    You can get information on the compatibility level of all of the databases in a given SQL Server instance with the following:

    EXEC sp_helpdb

    - Ed


    Ed Katibah Spatial Ed Blog
    • Proposed as answer by Spatial Ed Monday, January 17, 2011 4:32 PM
    • Marked as answer by Alex Feng (SQL) Monday, January 24, 2011 10:24 AM
    Monday, January 17, 2011 4:29 PM

All replies

  • Hi Arnaud -

    The linestring returned from Bing Maps has an overlapping segment in it (the geometry departs from the main path and forms a loop and then overlaps itself on the way back to the main path).  This is not an uncommon result from the routes generated by Bing Maps.  SQL Server uses the OGC Simple Features specification for geometry definitions and overlapping segments in linestring are invalid.  To correct this, you will need to run MakeValid().  I successfully buffered your linestring using the following T-SQL in Denali:

    SELECT @myGEOM.MakeValid().STBuffer(.01)

    Thanks,

    - Ed


    Ed Katibah Spatial Ed Blog
    Thursday, January 13, 2011 7:04 PM
  • Thanks a lot Ed, still not understand what went wrong, I will keep you informed of my mistake if I figure out.

     

    Cheers!

    Thursday, January 13, 2011 7:38 PM
  • Any particular reason that you are not using the GEOGRAPHY type?

     

    - Ed


    Ed Katibah Spatial Ed Blog
    Thursday, January 13, 2011 9:22 PM
  • Any particular reason that you are not using the GEOGRAPHY type?

    My guess would be that the OP is using the geometry datatype precisely because this linestring is invalid.... roll on Denali and geography.MakeValid() !

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, January 13, 2011 10:09 PM
    Answerer
  • OK now I found out what went wrong... And I know why when I tested your solution it worked, and why when I integrated it into my code it didn't work...
    The database executing the code has been imported from a SQL Server 2008 server and I forgot to set the compatibility level to 110 (Denali)....
    Now it works fine, I believe I needed a good night of sleep to figure that out.
    Thanks for your help.
    Cheers
    Friday, January 14, 2011 8:52 AM
  • As a refresher for other folks moving databases from SQL Server 2008 to Denali, you can set the compatibility level of the database with the following Transact-SQL statements:

    --Set database compatibility level to SQL Server Code-Named "Denali"
    ALTER DATABASE <database name> 
    SET COMPATIBILITY_LEVEL = 110;

    --Set database compatibility level to SQL Server 2008
    ALTER DATABASE <database name>
    SET COMPATIBILITY_LEVEL = 100;

    You can get information on the compatibility level of all of the databases in a given SQL Server instance with the following:

    EXEC sp_helpdb

    - Ed


    Ed Katibah Spatial Ed Blog
    • Proposed as answer by Spatial Ed Monday, January 17, 2011 4:32 PM
    • Marked as answer by Alex Feng (SQL) Monday, January 24, 2011 10:24 AM
    Monday, January 17, 2011 4:29 PM