\nThis entry is a continuation of the previous posting on how to use SQL Server 2008 spatial data and Virtual Earth. This entry discusses your web service and SQL Server code choices.\n<\/p>\n
\nThe point of the web service is to translate one of the output formats of SQL Server 2008 spatial to a format this Virtual Earth can use. The spatial data types can be output in:
\n1. Well-known text format
\n2. "Native" format – that is, return a Geometry\/Geography type to a .NET client as the SqlGeometry\/SqlGeography native .NET types
\n3. GML – Geography markup language\n<\/p>\n
\nRemember that Virtual Earth needs:
\n1. GEORSS or
\n2. KML
\n3. Custom XML-based format (and custom Javascript code to process it)
\n4. Javascript strings (to pass to eval())\n<\/p>\n
\nThere is not a one-to-one correspondence between any of these methods, so there is going to be a need for a transformation somewhere. In addition, a SQL Server Geometry or Geography type can contain a collection of points, linestrings, or polygons. There may also need to be a one-to-many translation. This is facilitated by methods STNumGeometries\/STGeometryN and STNumPoints\/STPointN.\n<\/p>\n
\nFinally, there could be a pushpin (point) associated with the center of a polygon and\/or text associated with the pushpin. Where the text comes from will be application-dependent.\n<\/p>\n
\nBecause there are many ways to process XML in SQL Server and in web services, that's lots of choices. Some that come to mind are:
\n1. Transforming a SqlGeometry\/SqlGeography from a SqlDataReader to a custom XML format
\n Javascript code in the web page processes the custom format.
\n2. Using SQL Server's XQuery to transform GML into GEORSS or KML
\n This is returned to the web service as a SqlXml data type.
\n Consumed directly as a layer in Virtual Earth.
\n3. Using an XSLT transform (or LINQ to XML) to transform GML to GEORSS or KML. This can occur in the web service or even a SQLCLR procedure, although this type of processing should really be accomplished in the web service.
\n4. Using SELECT … FOR XML PATH to construct GEORSS or KML directly.
\n Similar processing to choice #2 above.
\n5. Transforming a SqlGeometry\/SqlGeography from a SqlDataReader to Javascript strings.
\n Web page simply calls eval()\n<\/p>\n
\nHopefully this posting will elucidate the choices you have for this rendering. Depending on which method you choose, there could be quite a few "moving parts" (a la Rube Goldberg) in the solution.
\n– Web pages with custom Javascript code
\n– Web service to supply the data
\n– HTTP Handler to allow Virtual Earth to use remote GEORSS\/KML
\n– Data access code in SQL Server
\n– Transformation code in web service or SQL Server\n<\/p>\n
\nSo…..what's your favorite method for using SQL Server 2008 spatial data with Virtual Earth?<\/p>\n","protected":false},"excerpt":{"rendered":"
This entry is a continuation of the previous posting on how to use SQL Server 2008 spatial data and Virtual Earth. This entry discusses your web service and SQL Server code choices. The point of the web service is to translate one of the output formats of SQL Server 2008 spatial to a format this Virtual […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,36],"tags":[],"class_list":["post-723","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-spatial"],"yoast_head":"\n