Subscribe to this thread
Home - Cutting Edge / All posts - Reading DMS representation
artlembo


2,875 post(s)
#22-Oct-17 21:20

I have data in DMS:

W75°36'35.1" , N38°22'26.6"

any thoughts on a best-practice to turn this into a geometry? I know I can play the old parsing game, splitting by characters (o,',"), or grabbing characters 2,3, 5,6, 8,9,10,11 , etc. I wasn't sure if there might be a function I've missed, or a better way of doing it.

tjhb

7,503 post(s)
#22-Oct-17 22:10

In M8 this can be handled automatically, just with a CAST, e.g.

VALUES (

    CAST("W75°36'35.1" AS LONGITUDE),

    CAST("N38°22'26.6" AS LATITUDE)

    )

    NAMES ([X][Y])

    ;

or

VALUES (

    NewPointLatLon(

        CAST("W75°36'35.1" AS LONGITUDE),

        CAST("N38°22'26.6" AS LATITUDE)

        )

    )

    NAMES ([Point])

    ;

(You don't even have to deal with that pesky trailing " for seconds: just leave it off/convert it into a closing quote mark! Very nice.)

Roughly the same thing is possible in tables.

Haven't tried in SQL9.

artlembo


2,875 post(s)
#23-Oct-17 01:47

Thanks. Yeah, 8 understands those values. Not sure about 9. I ran it with this:

SELECT   meter, GeomMakePoint(VectorMakeX2(-1*lng,lat))

INTO newtable

FROM

(

SELECT ubmi_meter AS meter,(Cast(StringSubstringLen([ubmi_data_1],1,2) AS float64)) +

  Cast(StringSubstringLen([ubmi_data_1],4,2) AS float64)/60 +

    Cast(StringSubstringLen([ubmi_data_1],7,4) AS float64)/3600 AS lng,

 

  Cast(StringSubstringLen([ubmi_data_2],1,2) AS float64) +

  Cast(StringSubstringLen([ubmi_data_2],4,2) AS float64)/60 +

    Cast(StringSubstringLen([ubmi_data_2],7,4) AS float64)/3600 AS lat

FROM [dbo.ubmetinv]

AS A

I guess I can't complain, it isn't too hard to do, and now I have it forever :-) So, while CASTING to LONGITUDE is cool and easy, this works too.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.