Clever Geek Handbook

Can I export spatial data from Sql Server 2008 in gml2 format?

Sql Server 2008 supports spatial data with new UDT geometry and geography. Both of them support the AsGml () method for serializing data in gml format. However, they do serialize data in GML3 format. Is there a way to tell about the need to serialize data in GML2 format?

+1


source to share


4 answers


As Marco said, there is no gml2 support in Sql Server 2008, so I just wrote a function to convert the gml3 returned by the server to the gml2 I need.



0


source


AFAIK, there is no built-in function to serialize geospatial data in GML 2.x. You need to use some third party tools, implement a youserlf script or, this suggestion may seem a little odd, use PostGIS for this transition.

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

I would suggest using PostGIS as staging and translated repository.



  • Store data in GML 3 with SQL Server functions

  • Loading data serialized in GML 3 using PostGIS function ST_GeomFromGML

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

It may seem strange to suggest a different geospatial database, but I'm sure it will work pretty smoothly and well.

+1


source


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

Here is an example of custom serialization using the 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(')');
    }
}

Use the SqlGeometryBuilder class to deserialize:

// 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;
0


source


Ok, since you're done, it doesn't make much sense, but I would recommend putting geoserver in front of SQL Server. Geoserver has all the serialization code built for just about any format you need, easy to install and works as advertised.

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

0


source






More articles:


All Articles