Thursday, August 28, 2008 7:32 PM
I'm projecting a data file from OSGB UK National Grid coordinates onto WGS84, and having some trouble. I'm getting the standard 'Does not represent a valid instance of geography' message, and I'm reasonably certain my problem is that the projection has done a direct, point-wise projection of the polygons onto WGS84, and that this is yielding some self intersecting segments.
Is there a way I can address these issues? Is there a tool that can reliably convert between coordinate systems and keep SQL Server 2008 happy? I really don't want to resort to throwing a buffer on the shape since that'll interfere with various calculations.
Friday, August 29, 2008 2:59 AMAnswererGive Safe's FME a whirl. I can't say that OSGB to WGS84 perfecty, but since there's a free trial, its worth a shot.
Friday, August 29, 2008 7:15 AM
Already using FME's workbench system. It seems to work for most polygons, but quite a few still fail. The import chain I'm using is
Shapefile -> Reprojection -> Close Holes -> Refiner -> SQL Server
Friday, August 29, 2008 9:13 AMAnswererHave you tried importing the results into a geometry column first, and then using the technique described by Ed in this blog post: http://blogs.msdn.com/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx ?
This resolves some issues with invalid geography data (notably incorrect ring orientation) by forcing a STUnion() method with a point already contained within the geometry definition (the start point) - the resulting geometry is unchanged, but correctly oriented. You can then move the valid data into a geography column.
It helped me with some UK postcode data that was in EPSG:27700 UK National Grid, so it might fix your problems too (although if FME can't automatically fix it, I'm not sure if this will work in your case)... worth a shot...?
Friday, August 29, 2008 1:20 PM
That does seem to make some of the polygons happier. It gets through approximately half of the dataset now before hitting the first error.). The error I'm referring to is:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
This trick seems to be reducing the error count (and I believe more through the self-union removing most self-intercecting points), but I belive that some aspects like very thin spikes or complex borders resulting in self-intersecting when represented as a geography but not when represented as a geometry (which means, as I understand things, that MakevValid won't touch them).
Friday, August 29, 2008 2:49 PMModeratorHi Steven,
I suspect your analysis is correct. Not that I know that this is the case you're seeing, but I think there's certainly the possibility of this. I see at least a few cases that won't be fixed by Ed's technique:
- Objects like Fiji, which are generally split into two polygons that are disjoint when lat/lon are treated like planar coordinates, but which overlap (on their edge) when viewed properly.
- Objects that have features that are valid when edges are viewed as straight on a lat/lon plane, but are not when the edges are treated as great circles.
- Objects that have features like very thin spikes that are non-degenerate for one system, but due to precision factors collapse on another.
One thing we want to do for the next version is improve this story, but I think there is a technique that could help with the first two of these problems and could be implemented today:
- Start with the object in lat/lon coordinates in the geometry type.
- Use the gnomonic projection to project the object to a plane, again in the geometry type. The plane should be chosen to be tangent near the center of the object, and the size of the object should be constrained to something like a quarter hemisphere. We use this projection because it takes great circle arcs to line segments.
- Force a MakeValid on the instance, which will fix and invalid portions, but also rectify the ring ordering. By "force", I mean that we currently do a NOP if the objects are valid on the plane, so you need to get a tiny bit little tricky to make it happen. (Ed discusses this.)
- Unproject the coordinates, but to a geography this time.
Sunday, August 31, 2008 9:57 AM
Looking at the spatial tools, I'm looking at the Vacuous geom to geography sink and thinking it would be more or less the scenario I've explored with direct point to point conversion. Presumably the code you're referring to is something that's pending. I'll post a message on the board there later to see what the score is.
For Future Releases of SQL Spatial
It'd be very useful if the WKT coordinate descriptors from sys.spatial_reference_systems could be augmented with extra data such as:
is_geography (1 = GEOGRAPHY, 0 = GEOMETRY)
Which would identify a planar vs spheroid projection, and then four functions to reproject from the instances SRID to the target SRID
Where @GeomValue / @GeogValue are geometry/geography instances accordingly, and would contain the relevant logic and rules for reprojection of shapes between any two WKT systems, including the work involved in making corresponding instances of geomcollection, multipolygon, polygon etc compliant.
This would vastly simplify adoption scenarios for SQL 2008 spatial, since working with alternative coordinate systems becomes a case of ensuring there's an entry in sys.spatial_reference_systems for it.
Sunday, August 31, 2008 7:33 PMAnswererTotally agree with the suggestion of a straightforward ReprojectAsxxx() method. Do you want to file a Connect issue to suggest this, and then you can encourage people to vote on it? (From my understanding, that is the way to "get things done") - I'll certainly support it.
Not sure I understand your first point - all of the records currently in sys.spatial_reference_systems relate to spatial reference systems based on geographic co-ordinates describing positions on an ellipsoidal model, so is_geography would always be 1. In order for this to be a useful column, are you also suggesting that this table should be populated with projected EPSG systems as well? (which aren't currently required, since the geometry datatype doesn't need to know any details of the projection used - just that the points lie on a flat plane). If you were to implement ReprojectAsxxxx() for projected spatial reference systems as well, this would presumeably be a prerequisite...
Sunday, August 31, 2008 9:39 PM
We'd need details of the geometric coordinate systems too, such as the UK ordinance survey national grid (EPSG 22700), otherwise I imagine that there'd be no other way to translate OSGB UK NG easting/northing values into WGS84 latitude/longitudes other than knowing the properties of the projection the source data represents.