sql-server-2008 geospatial spatial geography ogc

Sql Server 2008 geography LineString size limitations

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 ;)

Solution

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.