Geography Markup Language for Geospatial Data in SQL Server


By:   |   Updated: 2010-12-20   |   Comments (2)   |   Related: More > Spatial Data Storage

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:

Function Description
[FIELD].AsGml() Returns the GML equivalent of a geometric or geographic instance

GeomFromGml Returns the Well-Known Binary Representation of a geometric or geographic instance



AsGml() Examples

Geometry Instance

DECLARE @GEO GEOMETRY 
SELECT @GEO = GEOMETRY::STGeomFromText 
     ('POLYGON ((0 0, 0 1560, 1100 1560, 1100 0, 0 0))', 0) 
SELECT @GEO.AsGml() 
Result
<Polygon xmlns="http://www.opengis.net/gml"> 
     <exterior> 
          <LinearRing> 
               <posList>0 0 0 1560 1100 1560 1100 0 0 0</posList> 
          </LinearRing> 
     </exterior> 
</Polygon> 

Geography Instance
DECLARE @GEOG GEOGRAPHY 
SELECT @GEOG = geography::STGeomFromText('POINT(-84.3 33.88)', 4326) 
SELECT @GEOG.AsGml() 
Result
<Point xmlns="http://www.opengis.net/gml"> 
     <pos>33.88 -84.3</pos> 
</Point> 

GeomFromGml() Examples

Geometry Instance
DECLARE @GeoString XML 
SELECT @GeoString = 
'<Polygon xmlns="http://www.opengis.net/gml"> 
     <exterior> 
          <LinearRing> 
               <posList>0 0 0 1560 1100 1560 1100 0 0 0</posList> 
          </LinearRing> 
    </exterior> 
</Polygon>' 
SELECT Geometry::GeomFromGml(@GeoString, 0)
Result

Returns Well-Known Binary Representation

Geography Instance
DECLARE @GeoString XML 
SELECT @GeoString = 
'<Point xmlns="http://www.opengis.net/gml"> 
     <pos>17.117 -61.783</pos> 
</Point>' 
SELECT Geometry::GeomFromGml(@GeoString, 4326) 
Result

Returns Well-Known Binary Representation

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:

The estimated execution plan for an AsGml() function query

executing a query using the GML functions

I queried a table that houses geographic information on zip codes in the United States

Next Steps


Last Updated: 2010-12-20


get scripts

next tip button



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips





Comments For This Article




Monday, March 12, 2012 - 7:47:49 AM - Anjithalk Back To Top (16345)

How do we process more than one polygon ? this only work with an instance ? 


Monday, December 20, 2010 - 9:29:36 AM - Ralph D. Wilson II Back To Top (10468)

Well, GML may mean Geographic Markup Language to you but I believe you will find that the original meaning of GML was General Markup Language . . . and that it was an IBM production that dates back to at least the early 1980's.  I know because I used it to develop help and instruction manuals for some applications on an IBM 370 back in the day.

 



download


Recommended Reading

SQL Server Geography Data Type

Calculate the Geographical Distance Between Two Cities in SQL Server

Calculate Geographical Coding and Time Zone for an Address in SQL Server with PowerShell, T-SQL and Google Maps

SQL Server 2008 Geography and Geometry Data Types

SQL Server 2008 Spatial Index Performance





get free sql tips
agree to terms


Learn more about SQL Server tools