Is it possible to export spatial data from Sql Server 2008 in gml2 format?

Question

Sql Server 2008 supports spatial data with new geometry and geography UDT's. They both support AsGml() method to serialize data in gml format. However they serialize data into GML3 format. Is there any way to tell it to serialize data into GML2 format?

Answer 1

There is no support for GML2, but there is extensibility API that can be used to implement custom serialization.

Here is an example of custom serialization using SqlGeometry.Populate(IGeometrySink) method (C# code):

CustomWriter w = new CustomWriter();
SqlGeometry.Parse("POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))").Populate(w);
System.Console.WriteLine(w);

public class CustomWriter : IGeometrySink {
    private StringBuilder _builder = new StringBuilder();

    public string ToString() {
     return _builder.ToString();
    }

    public void SetSrid(int srid) {
     _builder.Append('@');
     _builder.Append(srid);
    }

    public void BeginGeometry(OpenGisGeometryType type) {
     _builder.Append(" (");
     _builder.Append(type);
    }

    public void BeginFigure(double x, double y, double? z, double? m) {
     _builder.Append(" [");
     _builder.Append(x);
     _builder.Append(' ');
     _builder.Append(y);
    }

    public void AddLine(double x, double y, double? z, double? m) {
     _builder.Append(',');
     _builder.Append(x);
     _builder.Append(' ');
     _builder.Append(y);
    }

    public void EndFigure() {
     _builder.Append(']');
    }

    public void EndGeometry() {
     _builder.Append(')');
    }
}

To do deserialization use SqlGeometryBuilder class:

// Create "POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))" using Builder API
SqlGeometryBuilder b = new SqlGeometryBuilder();
b.SetSrid(0);
b.BeginGeometry(OpenGisGeometryType.Polygon);
    b.BeginFigure(0, 0);
    b.AddLine(10, 0);
    b.AddLine(10, 10);
    b.AddLine(0, 10);
    b.AddLine(0, 0);
    b.EndFigure();
b.EndGeometry();
SqlGeometry g = b.ConstructedGeometry;

Answer 2

As Marko said, there is no support for gml2 in Sql Server 2008, so I just ended up writing a function for transforming gml3 returned by the server to gml2 that I needed.

Answer 3

AFAIK, there is no built-in feature to serialize geospatial data to GML 2.x. You need to use some third-party tools, implement a writer youserlf or, this suggestion may sound a bit strange, use PostGIS for this transition.

PostGIS is alternative geospatial database, similar solution to SQL Server, but implementing de-/serialization for both formats: GML 2 and GML 3.

What I'd suggest is to use PostGIS as an intermediate and translating storage.

1. Store data to GML 3 using SQL Server functions

2. Load data serialized to GML 3 using PostGIS function ST_GeomFromGML

3. Store data from PostGIS to GML 2 format using ST_AsGML which allows you to specify target version of GML: text ST_AsGML(integer version, geometry g1);

It may sound strange to propose another geospatial database, but I'm sure it would work fairly smoothly and well.

Answer 4

Well since you finished there is not much point but I would recommend putting geoserver in front of SQL Server. Geoserver has all the serialization code built in for almost any format you want, is easy cheezy to install, and works as advertised.

http://docs.geoserver.org/2.0.x/en/user/services/wfs/outputformats.html