Latitude and longitude values from sqlgeometry linestring using C#

Question

I recently discovered spatial columns in Microsoft SQL. I have now successfully stored LINESTRING sqlgeometry with correct EPSG (STRID). Checked by using AutoCAD Map.

The data is now sqlgeometry LINESTRINGs containing groups of X Y coordinates.

Using C# (or an sql statement), how do I get latitude and longitude values of the coordinate points?

I found a previous post of the same question ... looks like the answer is STTransform ... I will report back here after testing it... http://stackoverflow.com/questions/22611331/converting-geometryspatial-to-longitude-and-latitude — Kevin Barnett, Nov 13 '15 at 05:27
: ( looks like ST Transform is not SQL Server. Looks like a SQL statement does the trick. Have not figured it out yet. — Kevin Barnett, Nov 13 '15 at 08:58
this stackoverflow post appears to be the answer I need.. http://stackoverflow.com/questions/11245645/sql-server-from-longitude-and-latitude-to-geometry-data-type To convert your data from geometry to geography, try: Geography::STGeomFromText(cast(GeomCol as varchar(max)), 4326) — Kevin Barnett, Nov 13 '15 at 09:13
I forgot to mention... the geometry stored is a projected flat system . Maybe SQL does not provide transform from projected to lat long? — Kevin Barnett, Nov 13 '15 at 10:16

XavierFischer · Answer 1 · 2018-03-30T15:48:36.023

What you need is reprojection onto spherical coordinates. SQL Server does not provides such functionality. You need some plumbing in C# to get your geometries and project coordinates.

If you have your conversion function, using a GeometrySink is fast and would help.

internal class SqlGeometryProjectionSink : IGeometrySink110
{

    IGeometrySink110 _sink;
    int _outSrid;
    Func<double, double, double[]> _coordTransform;

    public SqlGeometryProjectionSink(IGeometrySink110 p_Sink, int outSrid, Func<double, double, double[]> coordTransform)
    {
        _sink = p_Sink;
        _outSrid = outSrid;
        _coordTransform = coordTransform;
        if (_coordTransform == null)
        {
            _coordTransform = new Func<double, double, double[]>((x, y) => new double[] { x, y });
        }
    }

    void IGeometrySink.AddLine(double x, double y, double? z, double? m)
    {
        double[] proj = _coordTransform(x, y);
        _sink.AddLine(proj[0], proj[1], z, m);
    }

    void IGeometrySink.BeginFigure(double x, double y, double? z, double? m)
    {
        double[] proj = _coordTransform(x, y);
        _sink.BeginFigure(proj[0], proj[1], z, m);
    }

    void IGeometrySink.BeginGeometry(OpenGisGeometryType type)
    {
        _sink.BeginGeometry(type);
    }

    void IGeometrySink.EndFigure()
    {
        _sink.EndFigure();
    }

    void IGeometrySink.EndGeometry()
    {
        _sink.EndGeometry();
    }

    void IGeometrySink.SetSrid(int srid)
    {
        _sink.SetSrid(_outSrid);
    }


    public static SqlGeometry ReprojectGeometry(SqlGeometry geom, int srid, Func<double, double, double[]> coordTransform)
    {
        if (geom != null)
        {
            SqlGeometryBuilder builder = new SqlGeometryBuilder();
            SqlGeometryProjectionSink sink = new SqlGeometryProjectionSink(builder, srid, coordTransform);
            geom.Populate(sink);

            return builder.ConstructedGeometry;
        }
        return null;
    }


    void IGeometrySink110.AddCircularArc(double x1, double y1, double? z1, double? m1, double x2, double y2, double? z2, double? m2)
    {
        throw new NotImplementedException();
    }
}

If you don't know the formula you can use for example DotSpatial which provides transformations functions. (see here for an implementation example : https://github.com/xfischer/SqlServerSpatial.Toolkit/blob/4a60154f206af430b27de730afd0340db19f9191/SqlServerSpatial.Toolkit/Viewers/GDI/SqlGeometryReprojection.cs)

Latitude and longitude values from sqlgeometry linestring using C#

1 Answers1