Error when using MakeValidGeographyFromText

Jun 12, 2009 at 7:15 PM

Hi,

 I am trying to use the MakeValidGeographyFromText function on a set of points but it is throwing an invalid geometry error.

 

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

DECLARE

</font></font><font size="2" color="#0000ff">

 

</font>

 

@myPoly GEOGRAPHY;

--SET @myPoly = geography::STPolyFromText(@polytext,4326)

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

SET

</font></font>
<font size="2" color="#0000ff">

 

</font>

@myPoly = dbo.MakeValidGeographyFromText(@polytext,4326)

MY string looks like

POLYGON ((-76.90017700195312 40.276251580471076,-76.86756134033203 40.27638254704875,-76.86807632446289 40.266428364211436,-76.90086364746094 40.26734525809924,-76.90017700195312 40.276251580471076))

I also tried this from the "makevalid_example.sql" included in the download but get the same error. This "polygon" text Iincluded would be a 4 point polygon in clockwise rotation. I assumed this function should correct that, maybe I am wrong.

Here is the error I get.

"Msg 6522, Level 16, State 1, Line 7

A .NET Framework error occurred during execution of user-defined routine or aggregate "MakeValidGeographyFromText":

System.ArgumentException: 24200: The specified input does not represent a valid geography instance.

System.ArgumentException:

at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)

at Microsoft.SqlServer.Types.SqlGeographyBuilder.get_ConstructedGeography()

at SQLSpatialTools.SqlProjection.Unproject(SqlGeometry geometry)

at SQLSpatialTools.Functions.MakeValidGeographyFromGeometry(SqlGeometry geometry)

at SQLSpatialTools.Functions.MakeValidGeographyFromText(String inputWKT, Int32 srid)

"

<font size="2">

 

</font>

Any guidance would be appreciated.

Jun 12, 2009 at 7:40 PM

I think the problem might be because either you are somehow breaking the hemisphere limit or the edges overlap when plotted on a sphere. Plotting in a planar geometry works fine: -

 

declare @p1 geometry = 'POLYGON ((-76.90017700195312 40.276251580471076,-76.86756134033203 40.27638254704875,-76.86807632446289 40.266428364211436,-76.90086364746094 40.26734525809924,-76.90017700195312 40.276251580471076))'

select @p1

 

Shifting one of your points slightly will fix this: -

 

declare @p2 geography = 'POLYGON ((-76.90017700195312 40.276251580471076,-76.86756134033203 40.27638254704875,-76.86807632446289 40.7,-76.90086364746094 40.26734525809924,-76.90017700195312 40.276251580471076))'

select @p2

Or reversing the order of your points will do so too: -

declare @p geography = 'POLYGON ((-76.90017700195312 40.276251580471076,-76.90086364746094 40.26734525809924,-76.86807632446289 40.266428364211436,-76.86756134033203 40.27638254704875,-76.90017700195312 40.276251580471076))'

select @p

 

Jun 13, 2009 at 5:35 PM

I am sorry I misread your post. Yes I would expect MakeValidGeographyFromText to correct the rotation.

Looking at the source code I cannot see where get_ConstructedGeography is called from gnomonicProjection.Unproject. I am not sure if the release available to download is in sync with SVN.

I am not a developer on this project but have you tried checking out the version in trunk and trying the same thing with the latest version?

Developer
Jun 15, 2009 at 5:03 PM

It appears that both MakeValid... functions do not correctly orient polygon rings.  We will look into these issues in addition to the source code synchronization issue with current release.

Jun 15, 2009 at 5:21 PM

Thanks for your feedback folks,

I found a workaround from another post elsewhere on the web.

Basically it creates a geometry object from my string first, runs STUnion and Make Valid on the geometry, then converts it to a geography.

 

 

 

 

 

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

ALTER

</font></font><font size="2" color="#0000ff">

 

</font>

 

PROCEDURE [dbo].

[GetTilesByPolygon]

(

<font size="2">

@polytext

</font>

 

varchar(max

)

)

AS

-- convert texdt to geom, then run a union on it to account for polygons submitted in clockwise orientation (which is an invlid geogrpahy)

-- convert back to geography for use in the query

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

DECLARE

</font></font>
<font size="2" color="#0000ff">

 

</font>

 

@myPoly GEOGRAPHY

;

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

DECLARE

</font></font>
<font size="2" color="#0000ff">

 

</font>

 

@Geom AS GEOMETRY

;

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

SET

</font></font>
<font size="2" color="#0000ff">

 

</font>

 

@Geom = geometry::STGeomFromText(@polytext, 4326

)

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

SET

</font></font>
<font size="2" color="#0000ff">

 

</font>

 

@myPoly = geography::STGeomFromWKB(@Geom.STUnion(@Geom.STStartPoint()).MakeValid().STAsBinary(), @Geom.STSrid

)

Developer
Jun 15, 2009 at 6:46 PM

@bsdz

gnomonicProjection object inside MakeValid method is instance of SqlProjection class.
If you look at definition of Unproject method in SqlProjection.cs you will find get_ConstructedGeography call.

public SqlGeography Unproject(SqlGeometry geometry)
{
   ThrowIfArgumentNull(geometry, "geometry");
   SqlGeographyBuilder builder = new SqlGeographyBuilder();
   geometry.Populate(new Unprojector(this, builder, geometry.STSrid.Value)); // NOTE srid will be reused
   return builder.ConstructedGeography;
}

 

 

 

Jun 15, 2009 at 7:02 PM

Thanks. I must admit I overlooked the obvious property at the end.