locked
geometry.STAsText() to lat/long from XY RRS feed

  • Question

  • This returns a Polygon string with XY coordiates  where Geometry is the name of the column of Geometry data type:
    select Geometry.STAsText() from ParcelTest where APN='001-012-012'

     

     

    How do I get a Polygon() string containing lat/long points instead? The SQL needs to work with a query, not a stored procedure.

    Thursday, August 6, 2009 8:58 PM

Answers

  • STAsText() will return the WKT representation of each point in the geometry, independent of the coordinate system in which they were defined.

    So, if you use the geometry datatype to store X/Y coordinate values from a projected spatial reference system, STAsText() will return X/Y values.
    If you use the geometry datatype (or the geography datatype) to store latitude/longitude coordinate values from a geographic spatial reference system, STAsText() will return long/lat values.

    There is no way to convert between projected and geographic coordinates in SQL Server, so if you want to reproject your X/Y coordinates into a geographic system such as WGS84 you'll have to use an external tool such as Safe FME or FWTools.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, August 6, 2009 9:30 PM
    Answerer
  • You could try to use one of the projections available at http://www.codeplex.com/sqlspatialtools to convert to Geography type.
    Friday, August 14, 2009 2:21 PM

All replies

  • STAsText() will return the WKT representation of each point in the geometry, independent of the coordinate system in which they were defined.

    So, if you use the geometry datatype to store X/Y coordinate values from a projected spatial reference system, STAsText() will return X/Y values.
    If you use the geometry datatype (or the geography datatype) to store latitude/longitude coordinate values from a geographic spatial reference system, STAsText() will return long/lat values.

    There is no way to convert between projected and geographic coordinates in SQL Server, so if you want to reproject your X/Y coordinates into a geographic system such as WGS84 you'll have to use an external tool such as Safe FME or FWTools.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, August 6, 2009 9:30 PM
    Answerer
  • You could try to use one of the projections available at http://www.codeplex.com/sqlspatialtools to convert to Geography type.
    Friday, August 14, 2009 2:21 PM
  • I created this VBA function to convert from LatLong to UTM:

    Public Function Convert1LatLong2UTM(Latitude As Variant, Longitude As Variant, ByRef Northing As Variant, ByRef Easting As Variant)


    Dim a As Double
    Dim B As Double
    Dim f As Double
    Dim d1f As Double
    Dim rm As Double
    Dim k0 As Double
    Dim e As Double
    Dim e2 As Double
    Dim n As Double
    Dim A0 As Double
    Dim B0 As Double
    Dim C0 As Double
    Dim D0 As Double
    Dim E0 As Double
    Dim sin1 As Double

    Dim longzone As Double
    Dim longzonecm As Double
    Dim sec As Double
    Dim latrad As Double
    Dim longrad As Double
    Dim rho As Double
    Dim nu As Double
    Dim merarc As Double
    Dim ki As Double
    Dim kii As Double
    Dim kiii As Double
    Dim kiv As Double
    Dim kv As Double
    Dim A6 As Double
    Dim rawnorth As Double
    Dim pi As Double

    a = 6378137
    B = 6356752.3141
    f = 3.35281068750952E-03
    d1f = 298.257221538149
    rm = 6367435.67964348
       
    k0 = 0.9996
    e = 8.18191911198877E-02
    e2 = 6.73949678826144E-03
    n = 1.67922039780298E-03

    A0 = 6367449.14575089
    B0 = 16038.4296281578
    C0 = 16.832613491893
    D0 = 2.19844045825152E-02
    E0 = 3.1270522380694E-04
    sin1 = 4.84813681109536E-06
    pi = 3.1415926535897

    longzone = 16
    longzonecm = -87
    sec = (Longitude - longzonecm) * 3600 / 10000
    latrad = Latitude * pi / 180
    longrad = Longitude * pi / 180
    rho = a * (1 - e * e) / ((1 - (e * Sin(latrad)) ^ 2) ^ (3 / 2))
    nu = a / ((1 - (e * Sin(latrad)) ^ 2) ^ (1 / 2))
    merarc = A0 * latrad - B0 * Sin(2 * latrad) + C0 * Sin(4 * latrad) - D0 * Sin(6 * latrad) + E0 * Sin(8 * latrad)
    ki = merarc * k0
    kii = nu * Sin(latrad) * Cos(latrad) * sin1 ^ (2) * k0 * (100000000) / 2
    kiii = ((sin1 ^ (4) * nu * Sin(latrad) * Cos(latrad) ^ 3) / 24) * (5 - Tan(latrad) ^ 2 + 9 * e1sq * Cos(latrad) ^ 2 + 4 * e1sq ^ (2) * Cos(latrad) ^ 4) * k0 * (1E+16)
    kiv = nu * Cos(latrad) * sin1 * k0 * 10000
    kv = (sin1 * Cos(latrad)) ^ 3 * (nu / 6) * (1 - Tan(latrad) ^ 2 + e1sq * Cos(latrad) ^ 2) * k0 * (1000000000000#)
    A6 = ((sec * sin1) ^ 6 * nu * Sin(latrad) * Cos(latrad) ^ 5 / 720) * (61 - 58 * Tan(latrad) ^ 2 + Tan(latrad) ^ 4 + 270 * e1sq * Cos(latrad) ^ 2 - 330 * e1sq * Sin(latrad) ^ 2) * k0 * (1E+24)
    rawnorth = (ki + kii * sec * sec + kiii * sec ^ (4))
    Northing = rawnorth
    Easting = 500000 + (kiv * sec + kv * sec ^ (3))

    End Function

    and this one to convert from UTM to LAtLong:

    Public Function Convert1UTM2LatLong(Northing As Double, Easting As Double, ByRef Latitude As Double, ByRef Longitude As Double, Optional LZone As Integer = 16, Optional NorS As String = "N")
    Dim correctedNorthing As Double
    Dim eastprime As Double
    Dim arclength As Double
    Dim mu As Double
    Dim phi As Double
    Dim Cf1 As Double
    Dim T1 As Double
    Dim N1 As Double
    Dim R1 As Double
    Dim D1 As Double
    Dim fact1 As Double
    Dim fact2 As Double
    Dim fact3 As Double
    Dim fact4 As Double
    Dim lofact1 As Double
    Dim lofact2 As Double
    Dim lofact3 As Double
    Dim deltalong As Double
    Dim zonecm As Double
    Dim rawlat As Double
    Dim e As Double
    Dim e1sq As Double
    Dim a As Double
    Dim b As Double
    Dim k0 As Double
    Dim e1 As Double
    Dim C1 As Double
    Dim C2 As Double
    Dim C3 As Double
    Dim C4 As Double

    e = 8.18191911198877E-02
    e1sq0 = 6.73949678826144E-03
    b = 6356752.3141
    a = 6378137
    k0 = 0.9996

    e1 = 1.67922039780296E-03
    C1 = 2.51882660151949E-03
    C2 = 3.7009490859557E-06
    C3 = 7.44781391944673E-09
    C4 = 1.70359937019978E-11
    If NorS = "N" Then
        correctedNorthing = Northing
    Else
        correctedNorthing = 10000000 - Northing
    End If
                   
        eastprime = 500000 - Easting
        arclength = Northing / k0
        mu = arclength / (a * (1 - e ^ (2) / 4 - 3 * e ^ (4) / 64 - 5 * e ^ (6) / 256))
        phi = mu + C1 * Sin(2 * mu) + C2 * Sin(4 * mu) + C3 * Sin(6 * mu) + C4 * Sin(8 * mu)
        Cf1 = e1sq * (Cos(phi)) ^ (2)
        T1 = (Tan(phi)) ^ 2
        N1 = a / (1 - (e * Sin(phi)) ^ 2) ^ (1 / 2)
        R1 = a * (1 - e * e) / (1 - (e * Sin(phi)) ^ 2) ^ (3 / 2)
        D1 = eastprime / (N1 * k0)
        fact1 = N1 * Tan(phi) / R1
        fact2 = D1 * D1 / 2
        fact3 = (5 + 3 * T1 + 10 * Cf1 - 4 * Cf1 * Cf1 - 9 * e1sq) * D1 ^ (4) / 24
        fact4 = (61 + 90 * T1 + 298 * Cf1 + 45 * T1 * T1 - 252 * e1sq - 3 * Cf1 * Cf1) * D1 ^ (6) / 720
        lofact1 = D1
        lofact2 = (1 + 2 * T1 + Cf1) * D1 ^ (3) / 6
        lofact3 = (5 - 2 * Cf1 + 28 * T1 - 3 * Cf1 ^ (2) + 8 * e1sq + 24 * T1 ^ (2)) * D1 ^ (5) / 120
        deltalong = (lofact1 - lofact2 + lofact3) / Cos(phi)
        zonecm = 6 * LZone - 183
        rawlat = 180 * (phi - fact1 * (fact2 + fact3 + fact4)) / 3.1415926535
        If NorS = "N" Then
            Latitude = rawlat
        Else
            Latitude = -rawlat
        End If
       
        Longitude = zonecm - deltalong * 180 / 3.1415926535

    End Function

    Thursday, May 10, 2018 4:18 PM