Converting Mappoint routes to SqlGeography
The mapping services from microsoft aren't limited
displaying maps in Virtual Earth, and not only limited to websites.
There are numerous webservices for, resolving locations to longtiudes
and latitudes as well as a routing api.
I am working on something for SQLBits to enable people to lift share. For
this I am using the routing API. This is very very easy to use, its just a
webservice which means we can use it from a website or from a windows
application.
I'm then putting the routes in the database, so I need to convert
the set of points that make the route into a SqlGeography type.
This I thought would be easy using the builder (SqlGeographyBuilder) (As
shown with the post SQL
2008 and Virtual Earth : Converting a VERoute to a SqlGeography) but I
found that I was getting an error saying the geography was invalid.
I thought of a few things but couldn't find the answer. I pinged Johannes
Kebeck (http://johanneskebeck.spaces.live.com/blog/)
and he reminded me that even with
lines, the set of points can be invalid if the intersect i.e. like an ampersand
&. Now I had thought of this but the Geography type doesn't have a
MakeValid function which will make a geometry type that is invalid into a valid
shape that doesn't overlap. So I thought self intersecting geographies where
fine. After speaking to Johannes I changed my code following is suggestion,
whilst there is no MakeValid for SqlGeography you can just use the SqlGeometry
type, use the MakeValid function which gets your points in the corect order, and
then convert to binary format (WKB) and create a SqlGeography instance from that
WKB. This works because the WKB doesn't have any knowledge of the base type, it
just describes the points of a shape, this applies to WKT as well i.e.
LINESTRING(0 0,0 10,10 82, 10 11) is a valid format for a Geography type as it
is a Geometry type. The only difference is that for Geograpahy types you are
limited to the values of the latitude values (-90 to
90).
After all that this is the code to convert a route from the
virtualearth route service to a SqlGeography.
RouteResponse routeResponse =
routeService.CalculateRoute(routeRequest);
SqlGeometryBuilder
gb = new SqlGeometryBuilder();
gb.SetSrid(0);
gb.BeginGeometry(OpenGisGeometryType.LineString);
bool
start = true;
foreach
(var rt in
routeResponse.Result.RoutePath.Points)
{
if (start) {
gb.BeginFigure(rt.Longitude,rt.Latitude, rt.Altitude, null); start = false; }
else
{
gb.AddLine(rt.Longitude,rt.Latitude, rt.Altitude, null);
}
}
gb.EndFigure();
gb.EndGeometry();
SqlGeography
g = SqlGeography.STGeomFromWKB(gb.ConstructedGeometry.MakeValid().STAsBinary(),
4326);
[Update 2009-02-22 SS: Due to the nuance of the the way
MS defined Lat and Long in the early stages of SQL2008 the positions for Lat and
Long are sometimes reversed. In this case I had to reverse the Lat and Long
values being passed to the builder compared with passing them to the
GeographyBuilder, if you find your routes seem to be rotated by 90 degrees this
is the problem]
-