Geography Markup Language for Geospatial Data in SQL Server

Problem

The company I work with has migrated most of its geospatial data to SQL Server 2008’s new spatial data types. During the course of a recent conversation the initialism GML was used a number of times. I’m not sure what it is, but it sounds like some type of markup language, particularly since they were mentioning data transfer and consumption in the same context. How close was my guess?

Solution

The answer is that you were right. GML stands for Geography Markup Language and is a standard adopted by both the Open Geospatial Consortium and International Organization for Standardization (ISO). It is an XML format and, as with any XML document, serves as a standard method of data transfer for the particular institution or discipline for which it was developed. As such it has a schema which describes the document and the instance document that houses the actual data. A number of GML Application Schemas have been developed for industries, but that doesn’t preclude an organization from creating their own schema. You and your clients should decide which GML Application Schema best suits your needs so that there is no confusion. GML is commonly used in applications that utilize spatial data.

All SQL Server 2008 instances support the use of GML. GML functions, like many of the other functions in the spatial data family, are derived as .NET CLR functions.

There are only a few functions that are used to work with GML in SQL Server 2008:

DECLARE @GEO GEOMETRY  SELECT @GEO = GEOMETRY::STGeomFromText       (‘POLYGON ((0 0, 0 1560, 1100 1560, 1100 0, 0 0))’, 0)  SELECT @GEO.AsGml() 

<Polygon >       <exterior>            <LinearRing>                 <posList>0 0 0 1560 1100 1560 1100 0 0 0</posList>            </LinearRing>       </exterior>  </Polygon> 

DECLARE @GEOG GEOGRAPHY  SELECT @GEOG = geography::STGeomFromText(‘POINT(-84.3 33.88)’, 4326)  SELECT @GEOG.AsGml() 

<Point >       <pos>33.88 -84.3</pos>  </Point> 

DECLARE @GeoString XML  SELECT @GeoString =  ‘<Polygon >       <exterior>            <LinearRing>                 <posList>0 0 0 1560 1100 1560 1100 0 0 0</posList>            </LinearRing>      </exterior>  </Polygon>’  SELECT Geometry::GeomFromGml(@GeoString, 0)

DECLARE @GeoString XML  SELECT @GeoString =  ‘<Point >       <pos>17.117 -61.783</pos>  </Point>’  SELECT Geometry::GeomFromGml(@GeoString, 4326) 

One thing to note is that there is some performance penalty when executing a query using the GML functions. In the example below I queried a table that houses geographic information on zip codes in the United States (about 43200 records). The estimated execution plan is essentially the same when querying with and without the AsGml(); however, there is a substantial performance hit when querying the table including the AsGml() function:

Next Steps

• Remember that the spatial functions are .NET CLR functions and are case-sensitive
• Review information and download documentation on the OpenGIS Geography Markup Language Encoding Standard
• Read about the GeomFromGml() function for geometry and geography instances
• Read about the AsGml() function for geometry and geography instances

Tim Cullen

Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.

• MSSQLTips Awards: Trendsetter (25+ tips)