Sql Server 2008 geography LineString size limitations

Question

hi there,

today i ran into a possible size limitation of geography type Linestrings within Sql Server 2008. when i create a Linestring with STGeomFromText that contains 567 points containing Long,Lat and Z coordinates, everything works fine.

However, if i add one more point to the linestring i get an ArgumentException 24200: The specified input does not represent a valid geography instance.

I was not able to find any documentation regarding size limitations of sql server´s geography type linestrings or something similar. is this a limitation of geography or just of the StGeomFromtext() function? Does anyone has some links to some more detailed information or is the only way to get around this splitting the linestring up into several smaller linestrings grouped together in a multilinestring. any help is appreciated ;) regards j.

Answer 1

I haven't heard about any size limitations on LINESTRING (certainly not as short as 567 points).

I just tried an example

DECLARE @geom GEOGRAPHY
SET @geom = GEOGRAPHY::STGeomFromText(
  'LINESTRING (142.98873903132778 -11.006193013241768
   , 142.9891970000001 -11.005916999999954
   -- SNIP 1,119 points
   , 142.04362479801711 -11.629451936538608 )', 4326)
SELECT @geom, @geom.STNumPoints()

which worked fine (creates the LINESTRING and counts 1,122 points).

Does your example fail with ANY 567 points - or just a specific set of points (can you share them with us?). I guess I'm wondering whether your 568th point makes your GEOGRAPHY instance larger than a hemisphere? For example, if I change my example by adding another point (0,0) which forces the GEOGRAPHY to be too large:

DECLARE @geom GEOGRAPHY
SET @geom = GEOGRAPHY::STGeomFromText(
  'LINESTRING (142.98873903132778 -11.006193013241768
   , 142.9891970000001 -11.005916999999954
   -- SNIP 1,119 points
   , 142.04362479801711 -11.629451936538608
   , 0 0 )', 4326)         -- ADDED ANOTHER POINT !
SELECT @geom, @geom.STNumPoints()

I get ArgumentException 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. which obviously isn't the exact same error as you - but I thought I'd raise it anyway [Skip to the UPDATE at the end for a better idea]

My second question to you is: does it work with the GEOMETRY datatype? Eg. if I change my "breaking" example above to use GEOMETRY then it works fine:

DECLARE @geom GEOMETRY    -- using GEOMETRY type instead
SET @geom = GEOMETRY::STGeomFromText(
  'LINESTRING (142.98873903132778 -11.006193013241768
   , 142.9891970000001 -11.005916999999954
   -- SNIP 1,119 points
   , 142.04362479801711 -11.629451936538608
   , 0 0 )', 4326)         -- THIS POINT BREAKS GEOGRAPHY but works now!
SELECT @geom, @geom.STNumPoints()

If you can post some more details on your specific problem it might suggest the underlying problem. Also could you add whether you are entering the points in SQL Management Studio or via code (is it C# and SQL data types assembly)? What is the full text of the error message you receive (if there is more than what you quoted above - see my error).

But the short answer is "I don't think there is a 567-point limit".

UPDATE: Ed's post contains the exact error you get (System.ArgumentException: 24200) - so if you can get you data working in GEOMETRY instead, this might be worth a try:

...able to get the geometry to convert to a geography using the following query:

SELECT dbo.MakeValidGeographyFromGeometry(GEOM.Reduce(.0000001)) FROM ZillowNeighborhoods WHERE ID = 3499

So what is happening here? First of all, the tolerance used in the Reduce method is very small. So how many vertex points were removed from the original geometry by Reduce? It turns out that 6 vertices were removed. A quick discussion with the SQL Server spatial developers revealed that during current validation processing some very close vertices can shift slightly and cause edges to cross/overlap (remember that geographic edges are not straight lines...). In the next release of SQL Server (SQL11), we have changed the underlying methodology and eliminated this issue. For now, we will have to workaround this issue using Reduce with very small tolerances.

Answer 2

Hell Craig,

thanks for your quick answer. I´ve encountered a set of really strange behaviors. Eg. the mentioned linestring cannot be read into a geography type, but it works using geometry type. However, i can select the resulting geometry object and can view it in the spatial results tab within management studio. if i call the .STNumPoints() method on this geometry object i get the following exception :

24144: This operation cannot be completed because the instance is not valid

if i call MakeValid() on this geometry object before calling STNumPoints(), everything works fine. MakeValid is not needed to just select the geometry and display it.

the same data cannot be read into a geography type. if the last coordinate points is removed it works like charm.

the sample data i tried to execute is :

declare @g geography
set @g = geography::STGeomFromText('LINESTRING (9.64939 47.30562 480, 9.64889 47.30408 478.9, 9.64873 47.30409 477,9.64855 47.30412 477,9.64837 47.30415 476,9.64817 47.30417 476,9.64797 47.30418 476,9.64777 47.30418 475,9.64757 47.30417 475,9.64737 47.30415 475,9.64717 47.30413 475,9.64697 47.30413 475,9.64677 47.30414 475,9.64657 47.30416 475,9.64638 47.30419 475,9.6462 47.30424 474,9.64603 47.3043 474,9.64586 47.30437 474,9.64569 47.30445 474,9.64553 47.30453 473,9.64537 47.30461 473,9.64521 47.30469 472,9.64504 47.30476 472,9.64486 47.30483 472,9.64468 47.3049 471,9.6445 47.30495 471,9.64431 47.30501 470,9.64411 47.30505 470,9.64392 47.3051 470,9.64373 47.30514 469,9.64355 47.30517 469,9.64337 47.30521 469,9.64319 47.30524 469,9.64301 47.30527 468,9.64282 47.30531 468,9.64262 47.30534 468,9.64243 47.30538 468,9.64223 47.30541 467,9.64203 47.30544 467,9.64184 47.30547 467,9.64164 47.3055 467,9.64143 47.30553 467,9.64123 47.30556 466,9.64102 47.30558 466,9.6408 47.30561 466,9.64059 47.30564 466,9.64036 47.30566 465,9.64014 47.30569 465,9.63992 47.30571 465,9.63969 47.30574 465,9.63946 47.30576 465,9.63923 47.30578 465,9.639 47.30581 464,9.63877 47.30583 464,9.63853 47.30586 464,9.63829 47.30588 464,9.63805 47.3059 464,9.63782 47.30593 464,9.63758 47.30596 464,9.63736 47.30599 463,9.63714 47.30602 463,9.63692 47.30606 463,9.6367 47.30609 463,9.63648 47.30613 462,9.63626 47.30617 462,9.63604 47.30621 462,9.63583 47.30625 461,9.63562 47.30629 461,9.63541 47.30634 460,9.63521 47.30638 460,9.63502 47.30642 460,9.63484 47.30646 459,9.63469 47.3065 458,9.63455 47.30652 458,9.63444 47.30655 457,9.63434 47.30657 457,9.63426 47.30659 456,9.63417 47.30661 456,9.63407 47.30664 456,9.63394 47.30667 456,9.63381 47.30669 456,9.63368 47.30673 456,9.63356 47.30676 456,9.63344 47.30678 455,9.63335 47.3068 455,9.63329 47.30682 455,9.63324 47.30683 455,9.63322 47.30684 455,9.6332 47.30684 455,9.6332 47.30684 455,9.6332 47.30684 455,9.6332 47.30685 455,9.63318 47.30685 455,9.63316 47.30686 455,9.6331 47.30688 455,9.63295 47.30692 455,9.63278 47.30697 455,9.63259 47.30701 455,9.63239 47.30706 454,9.6322 47.30711 454,9.632 47.30716 454,9.6318 47.30721 454,9.63159 47.30726 453,9.63139 47.3073 452,9.63118 47.30735 452,9.63098 47.30741 452,9.63078 47.30746 451,9.63059 47.30751 451,9.63041 47.30756 451,9.63022 47.30761 451,9.63002 47.30766 451,9.62983 47.30771 451,9.62963 47.30776 451,9.62944 47.30781 451,9.62925 47.30786 451,9.62906 47.30791 451,9.62887 47.30796 450,9.62868 47.30801 450,9.62849 47.30806 449,9.6283 47.30811 449,9.6281 47.30816 449,9.62791 47.30821 449,9.62771 47.30827 449,9.62752 47.30832 448,9.62732 47.30837 448,9.62712 47.30843 448,9.62693 47.30848 448,9.62674 47.30853 447,9.62654 47.30858 447,9.62635 47.30863 447,9.62615 47.30868 446,9.62594 47.30873 445,9.62573 47.30877 444,9.62552 47.30881 443,9.62534 47.30888 442,9.62517 47.30896 442,9.62501 47.30905 442,9.62487 47.30915 442,9.62475 47.30925 441,9.62463 47.30936 441,9.62453 47.30946 440,9.62443 47.30957 440,9.62434 47.30969 439,9.62425 47.30981 438,9.62415 47.30994 437,9.62405 47.31007 437,9.62394 47.3102 436,9.6238 47.31033 435,9.62365 47.31044 434,9.62348 47.31054 433,9.62331 47.31061 433,9.62309 47.31068 433,9.62291 47.31074 433,9.62273 47.31079 433,9.62254 47.31084 433,9.62235 47.31088 434,9.62216 47.31092 434,9.62197 47.31096 434,9.62178 47.31099 434,9.62159 47.31103 434,9.6214 47.31106 434,9.62125 47.3111 434,9.6211 47.31114 434,9.62098 47.31118 434,9.62086 47.31121 434,9.62078 47.31123 434,9.62076 47.31126 434,9.62075 47.31128 434,9.62075 47.31128 435,9.62075 47.31128 435,9.62075 47.31128 435,9.62075 47.31128 435,9.62074 47.31129 435,9.62075 47.31131 435,9.62075 47.31134 435,9.62076 47.31139 435,9.62077 47.31145 436,9.62079 47.31154 436,9.62081 47.31166 436,9.62083 47.31179 437,9.62086 47.31193 437,9.62089 47.31208 437,9.62092 47.31224 437,9.62095 47.3124 437,9.62098 47.31256 438,9.62102 47.31273 438,9.62106 47.3129 437,9.62111 47.31307 437,9.62116 47.31325 437,9.62121 47.31343 437,9.62126 47.31362 437,9.62131 47.31381 436,9.62137 47.314 436,9.62142 47.31419 436,9.62148 47.31439 435,9.62154 47.3146 435,9.62161 47.3148 435,9.62168 47.315 435,9.62176 47.31521 434,9.62184 47.31542 434,9.62192 47.31563 434,9.62201 47.31584 434,9.62209 47.31605 433,9.62218 47.31627 433,9.62227 47.31648 432,9.62236 47.31668 432,9.62246 47.3169 431,9.62256 47.31711 431,9.62266 47.31734 430,9.62277 47.31756 430,9.62288 47.31779 429,9.62298 47.31802 429,9.62308 47.31825 428,9.62318 47.31847 428,9.62327 47.31869 427,9.62337 47.31892 427,9.62345 47.31915 427,9.62354 47.31938 426,9.62362 47.31962 426,9.62371 47.31986 426,9.62379 47.3201 426,9.62388 47.32035 426,9.62398 47.3206 426,9.62407 47.32085 426,9.62417 47.3211 426,9.62426 47.32135 427,9.62436 47.3216 426,9.62446 47.32184 426,9.62457 47.32208 426,9.62468 47.32232 426,9.6248 47.32255 426,9.62492 47.32278 425,9.62505 47.32302 425,9.62519 47.32325 425,9.62533 47.32348 425,9.62548 47.32372 424,9.62564 47.32396 424,9.6258 47.32419 424,9.62596 47.32442 424,9.62613 47.32465 424,9.62629 47.32488 424,9.62645 47.3251 424,9.6266 47.32533 424,9.62675 47.32555 424,9.62689 47.32578 424,9.62702 47.326 424,9.62714 47.32622 424,9.62724 47.32644 423,9.62734 47.32666 423,9.62743 47.32688 423,9.6275 47.3271 423,9.62757 47.32731 423,9.62763 47.32753 423,9.62768 47.32774 423,9.62772 47.32795 423,9.62776 47.32815 423,9.62778 47.32833 423,9.6278 47.32851 423,9.62782 47.32869 423,9.62783 47.32887 423,9.62783 47.32905 423,9.62783 47.32923 423,9.62781 47.32942 424,9.6278 47.3296 424,9.62779 47.32977 425,9.62778 47.32995 425,9.62777 47.33013 426,9.62777 47.33031 427,9.62776 47.3305 427,9.62775 47.33068 428,9.62776 47.33086 429,9.62776 47.33102 429,9.62776 47.33119 430,9.62776 47.33133 430,9.62773 47.33146 431,9.62767 47.33157 432,9.6276 47.33165 432,9.6275 47.33172 432,9.62738 47.33176 432,9.62724 47.33177 432,9.62707 47.33177 432,9.62693 47.33173 432,9.62681 47.33167 432,9.62671 47.3316 433,9.62663 47.33152 433,9.62657 47.33145 432,9.62653 47.33139 432,9.62651 47.33136 432,9.62649 47.33135 432,9.62648 47.33133 432,9.62646 47.3313 433,9.62644 47.33128 432,9.62642 47.33124 432,9.62639 47.3312 432,9.62635 47.33115 432,9.62628 47.33111 432,9.62619 47.33108 432,9.62606 47.33109 431,9.62591 47.3311 431,9.62573 47.33112 430,9.62552 47.33113 430,9.6253 47.33114 429,9.62506 47.33113 429,9.62482 47.33111 428,9.62458 47.33108 428,9.62434 47.33103 428,9.62411 47.33097 428,9.62388 47.3309 427,9.62366 47.33081 427,9.62345 47.33072 427,9.62326 47.33062 427,9.62307 47.33051 427,9.62289 47.33041 426,9.62273 47.33031 426,9.62256 47.33021 426,9.6224 47.3301 426,9.62224 47.33 426,9.62208 47.3299 426,9.62192 47.3298 426,9.62175 47.3297 426,9.62158 47.32961 426,9.6214 47.32952 425,9.62121 47.32943 425,9.62102 47.32935 425,9.62082 47.32928 425,9.62061 47.32921 425,9.6204 47.32914 424,9.62019 47.32908 424,9.61998 47.32903 424,9.61977 47.32898 424,9.61956 47.32894 424,9.61933 47.32891 424,9.61911 47.32888 423,9.61888 47.32886 423,9.61864 47.32883 423,9.6184 47.32881 423,9.61815 47.32879 423,9.6179 47.32878 423,9.61764 47.32877 422,9.61738 47.32877 422,9.61711 47.32878 422,9.61685 47.32879 422,9.61657 47.32881 422,9.6163 47.32884 422,9.61604 47.32887 422,9.61579 47.32891 422,9.61554 47.32895 422,9.61531 47.329 422,9.61507 47.32904 422,9.61484 47.3291 421,9.61462 47.32916 421,9.61439 47.32923 421,9.61416 47.3293 421,9.61392 47.32938 421,9.61369 47.32946 421,9.61345 47.32953 421,9.61321 47.32961 421,9.61298 47.32968 420,9.61276 47.32975 420,9.61253 47.32981 420,9.61232 47.32987 420,9.6121 47.32993 420,9.61188 47.32999 420,9.61167 47.33004 420,9.61145 47.33008 420,9.61125 47.33013 420,9.61104 47.33018 420,9.61084 47.33022 420,9.61064 47.33026 420,9.61044 47.3303 420,9.61024 47.33034 420,9.61006 47.33038 420,9.60988 47.33041 420,9.60971 47.33042 420,9.60953 47.33041 420,9.60934 47.33039 420,9.60916 47.33034 420,9.60899 47.33028 420,9.60882 47.33021 421,9.60865 47.33013 420,9.60849 47.33005 420,9.60832 47.32997 420,9.60815 47.32989 420,9.60798 47.32982 420,9.6078 47.32976 420,9.60762 47.32973 419,9.60743 47.32971 419,9.60723 47.3297 419,9.60703 47.32971 419,9.60684 47.32971 419,9.60664 47.32971 419,9.60645 47.32971 419,9.60626 47.32972 419,9.60607 47.32972 419,9.60587 47.32973 419,9.60567 47.32973 419,9.60547 47.32974 419,9.60527 47.32974 419,9.60507 47.32976 419,9.60487 47.32978 419,9.60469 47.3298 420,9.6045 47.32983 420,9.60431 47.32988 420,9.60413 47.32993 420,9.60396 47.33 419,9.60378 47.33006 419,9.60362 47.33012 419,9.60345 47.33018 419,9.60329 47.33024 419,9.60312 47.33031 419,9.60295 47.33037 419,9.60279 47.33043 419,9.60262 47.33049 419,9.60244 47.33055 419,9.60227 47.33061 419,9.60209 47.33066 419,9.6019 47.33071 420,9.60172 47.33076 420,9.60153 47.3308 420,9.60135 47.33084 419,9.60118 47.33088 419,9.601 47.33092 419,9.60082 47.33097 419,9.60065 47.33102 419,9.60048 47.33108 419,9.60032 47.33115 419,9.60016 47.33122 418,9.60001 47.33129 419,9.59985 47.33137 418,9.59969 47.33144 418,9.59953 47.33152 418,9.59937 47.33159 418,9.59921 47.33167 418,9.59905 47.33174 418,9.59889 47.33182 418,9.59873 47.3319 418,9.59857 47.33197 418,9.59841 47.33205 417,9.59826 47.33213 417,9.59812 47.33222 417,9.59798 47.33231 417,9.59787 47.33241 417,9.59778 47.3325 417,9.5977 47.33259 416,9.59764 47.33267 416,9.59759 47.33273 416,9.59755 47.33276 416,9.59749 47.33277 416,9.59743 47.33277 416,9.59735 47.33274 416,9.59723 47.33271 416,9.59709 47.33267 416,9.59695 47.33265 416,9.59682 47.33265 417,9.59669 47.33268 417,9.59661 47.33275 418,9.59656 47.33284 418,9.59655 47.33293 419,9.59656 47.33302 419,9.59657 47.33312 420,9.59658 47.3332 421,9.59657 47.33328 421,9.59654 47.33334 421,9.59649 47.33338 422,9.59642 47.33341 422,9.59634 47.33343 422,9.59625 47.33344 422,9.59614 47.33346 422,9.59602 47.33348 422,9.59589 47.33351 422,9.59575 47.33353 422,9.5956 47.33356 423,9.59545 47.33359 423,9.5953 47.33362 423,9.59514 47.33365 423,9.59499 47.33368 423,9.59483 47.33371 423,9.59467 47.33374 423,9.59451 47.33377 423,9.59434 47.3338 422,9.59417 47.33383 422,9.594 47.33386 422,9.59384 47.33389 422,9.59368 47.33392 422,9.59352 47.33395 422,9.59337 47.33398 422,9.59322 47.33401 422,9.59309 47.33404 421,9.59296 47.33406 421,9.59283 47.33408 421,9.59272 47.3341 421,9.5926 47.33411 421,9.59248 47.33412 421,9.59236 47.33411 421,9.59223 47.3341 421,9.59211 47.33409 420,9.59198 47.33408 420,9.59184 47.33406 419,9.59169 47.33405 419,9.59153 47.33403 418,9.59137 47.33401 417,9.5912 47.33399 416,9.59103 47.33398 415,9.59086 47.33397 415,9.5907 47.33398 415,9.59054 47.33401 415,9.59038 47.33405 415,9.59023 47.33411 414,9.59008 47.33418 415,9.58993 47.33424 415,9.58978 47.3343 415,9.58964 47.33437 415,9.58953 47.33444 415,9.58942 47.3345 415,9.58931 47.33454 415,9.58919 47.33456 415,9.58905 47.33455 415,9.58891 47.33453 415,9.58876 47.33451 416,9.5886 47.33449 416,9.58843 47.33447 416,9.58826 47.33444 416,9.58809 47.33441 416,9.58794 47.33439 416,9.58777 47.33439 417,9.58761 47.33439 417,9.58745 47.3344 417,9.58728 47.33441 417,9.58711 47.33443 417,9.58694 47.33444 417,9.58675 47.33445 417,9.58657 47.33447 417,9.58635 47.3345 417,9.58618 47.33452 417,9.58602 47.33454 417,9.58586 47.33455 417,9.58569 47.33455 417,9.58553 47.33453 416,9.58538 47.3345 417,9.58525 47.33445 417,9.58513 47.33439 417,9.58503 47.33434 416,9.58494 47.33429 416,9.58484 47.33427 416,9.58474 47.33426 416,9.58464 47.33426 416,9.58455 47.33424 415,9.58449 47.33422 416,9.58445 47.33421 416,9.58442 47.33421 417,9.58441 47.33421 417,9.5844 47.33421 417,9.5844 47.3342 417,9.58441 47.33419 417,9.58441 47.33419 418,9.58441 47.33418 418,9.58441 47.33418 418,9.58442 47.33418 418,9.58442 47.33417 419,9.58442 47.33417 419,9.58442 47.33416 419,9.58443 47.33416 420,9.58443 47.33416 420,9.58443 47.33415 420,9.58443 47.33415 420,9.58443 47.33414 419,9.58443 47.33415 419)',4326)

which does IMHO not exceed a single hemisphere. It´s not even a big area.

setting @g as geometry and calling the STGeomFromText static method with geometry instead of goegraphy works as long as you do not call methods like STNumPoints() or STDistance() on it.

this is getting really confusing...

edit: I´ve just encountered an even shorter Linestring that fails. removing the last point results in a valid geography object wherease the following statement fails:

set @g2 = geography::STGeomFromText('LINESTRING(9.56761 47.329475 421.5,9.56764 47.32942 422,9.56764 47.32942 422,9.56763 47.32942 423,9.56763 47.32942 423,9.56763 47.32942 423,9.56763 47.32942 424,9.56763 47.32942 423,9.56763 47.32943 422,9.56763 47.32943 422,9.56763 47.32944 422,9.56763 47.32945 421,9.56763 47.32946 420,9.56763 47.32947 419,9.56763 47.32948 418,9.56764 47.32948 417,9.56764 47.32948 417,9.56764 47.32949 417,9.56764 47.32949 416,9.56764 47.32949 415,9.56765 47.32949 415,9.56765 47.32949 415,9.56765 47.32949 416,9.56764 47.32949 417)',4326)
select @g2

i.e. that this Linestring fails with 24(!!!!) points and works with 23. If anyone has an idea what the problem might be...i´m getting somewhat frustrated. Too much nearby points would have been a reason for the first linestring, but i just cannot believe that 24 points that are too near to each other may lead to this error.

Answer 3

Make sure you don't cause a figure-8 with your data. A geography can't have its lines cross each other.

I haven't tried your data yet, but I imagine it could well be the problem. I once grabbed a ton of map data from someone, and found that several of my countries (like Canada, Russia, the UK) couldn't be converted into geographies, and when I investigated, it was down to the occasional figure-8 thing going on.

Ie... coming across a part of the border where it tries to double back on itself and then re-cross the border.

I'm just guessing here, mind you... if I have time today I'll grab your data and do some more investigation - time's the killer though.

Rob