Hi Manifold,

Please excuse my thick head. I have searched the manual but don't seem to be able to land on the relevant topic/demo/example.

I have table columns with latitudes formatted as text ( example "52 25 23.892" and longitudes formatted as text (example "-112 5 48.823"). These were imported from a .csv file. There are no degree, minute or second signs - just spaces. I would simply like to convert the latitudes and longitudes to respective float64 columns which would then be further used to create a Geom column and finally a drawing.

I cannot determine how to do this.

Many thanks in advance for any help.

Cheers, Jim

Not sure in M9, seems the assumption is your lat and long data will be numeric type, not text.

If you have access to M8, you can import your original data as Text (ansi variable). In the table window, right click the column and "change type"; in the drop-down you have a choice of "latitude" and "longitude" as types. Choose appropriately and you're done. You can then do another column right click and select "format" to get the look you want. To get it into a drawing, select table all / the records you want, copy, and in the project pane, "paste as" drawing, choose the appropriate lat and long columns in the dialogue, done. Save M8 project, then open it in M9 - the M8 to M9 conversion will be handled automatically. Might not be the most elegant of solutions, but seems to work.

Attachments:
LL as text.JPG

The manual has anexample here

And tucked away in the workings of the example is thing I innately tried. Which was to right button click into the column header field to change the format. Time to suggest the feature noted in the manual:

It is an extra step to use the Expression template to convert a field that is text into a field that is a number. Upcoming Manifold builds will add a Convert typecommand to the context menu for column heads in tables that will do the conversion "in place".

You're in luck: There's a new example, Example: Get Tokens From a String, that does exactly what you want.

The example uses a simple regular expression to extract "tokens" (sequences of text separated by spaces or by other special characters) from a string to get the degrees, minutes, and seconds parts of the latitude and longitude text that you have. It's fast and easy and very powerful.

The example then goes on to show how to combine the separate degrees, minutes, and seconds parts into float64, decimal latitude or longitude fields.

Many thanks for the replies from Grame, dale, and Dimitri. Before seeing the example from Dimitri (which I assume is new since the example latitudes and longitudes were surprisingly familiar; may thanks for this new example), I calculated decimal degrees via spreadsheet. I then worked my way through the new example and confirmed that both methods gave the same results.

Again, many thanks for this very helpful example from Dimitri.

Cheers, Jim

As a fan of functions within SQL I came up with this approach:

--SQL9

FUNCTION GetToken(@str NVARCHAR, @idx INT64) FLOAT64 AS (

 CAST(StringRegexpSubstringNth(@str, '[\-0-9.]+', @idx, 'gi') AS FLOAT64)

) END;

FUNCTION ToDecimal(@s NVARCHAR) FLOAT64 AS (

 CASE WHEN GetToken(@s, 0) < 0 THEN

 GetToken(@s, 0) - GetToken(@s, 1) / 60 - GetToken(@s, 2) / 3600

 ELSE

 GetToken(@s, 0) + GetToken(@s, 1) / 60 + GetToken(@s, 2) / 3600

 END

) END;

SELECT

 lat, ToDecimal(lat) as lat_dec

 , lon, ToDecimal(lon) as lon_dec

FROM

 [Tabel]

http://www.mppng.nl/manifold/pointlabeler

Very cool! There's no need to consider the various possible token separator characters when instead, as you do, all you need is to use [\-0-9.]+ to pick out groups of characters that are 0 through 9 or minus signs or decimal points.

A minor typo: no need for the 'gi' flags in the StringRegexpSubstringNth function, as just 'i' is all you need. (it still works as is as unnecessary flags are ignored).

There is one minor problem with the query, though. Values such as -0°25'23.892" are treated as positive latitudes or longitudes, when they should be negative. That is because the CAST in GetToken casts a "-0" text string into numeric 0, which is not less than zero and so the CASE test in ToDecimal fails to pick up such values as negative.

That can be fixed by using a test of

CASE WHEN StringContains(@s, '-')

I didn't think of the -0 case. Good addition. It's obvious that I don't live near the equator or the Greenwich meridian...

http://www.mppng.nl/manifold/pointlabeler

It's still a wonderful use of SQL, and now part of the example topic as well. Thank you for sharing!

I'm honoured...

http://www.mppng.nl/manifold/pointlabeler

This is an excellent addition to the Examples section. I will get a lot of use from this. Thanks!

Landsystems Ltd ... Know your land | www.landsystems.co.nz

if you don't like writing functions, you could do it all in an SQL statement:

SELECT (lat_d + lat_m/60 + lat_s/3600) AS lat_dd,

 (lon_d + lon_m/60 + lon_s/3600) AS lon_dd

FROM ( 

SELECT 

 Cast(StringSubStringLen(lat_dms,0,StringFindNth(lat_dms, ' ',0)) AS float32) AS lat_d,

 Cast(StringSubStringLen(lat_dms,StringFindNth(lat_dms, ' ',0)+1,StringFindNth(lat_dms, ' ',0)) AS float32) AS lat_m,

 Cast(StringSubStringLen(lat_dms,StringFindNth(lat_dms, ' ',1)+1,StringLength(lat_dms)) AS float32) AS lat_s,

 Cast(StringSubStringLen(lon_dms,0,StringFindNth(lon_dms, ' ',0)) AS float32) AS lon_d,

 Cast(StringSubStringLen(lon_dms,StringFindNth(lon_dms, ' ',0)+1,StringFindNth(lon_dms, ' ',0)) AS float32) AS lon_m,

 Cast(StringSubStringLen(lon_dms,StringFindNth(lon_dms, ' ',1)+1,StringLength(lon_dms)) AS float32) AS lon_s

FROM [t] )

and, you can wrap that into a geometry like:

SELECT GeomMakePoint(VectorMakeX2((lon_d + lon_m/60 + lon_s/3600), 

 (lat_d + lat_m/60 + lat_s/3600)) )

FROM ( 

SELECT 

 Cast(StringSubStringLen(lat_dms,0,StringFindNth(lat_dms, ' ',0)) AS float32) AS lat_d,

 Cast(StringSubStringLen(lat_dms,StringFindNth(lat_dms, ' ',0)+1,StringFindNth(lat_dms, ' ',0)) AS float32) AS lat_m,

 Cast(StringSubStringLen(lat_dms,StringFindNth(lat_dms, ' ',1)+1,StringLength(lat_dms)) AS float32) AS lat_s,

 Cast(StringSubStringLen(lon_dms,0,StringFindNth(lon_dms, ' ',0)) AS float32) AS lon_d,

 Cast(StringSubStringLen(lon_dms,StringFindNth(lon_dms, ' ',0)+1,StringFindNth(lon_dms, ' ',0)) AS float32) AS lon_m,

 Cast(StringSubStringLen(lon_dms,StringFindNth(lon_dms, ' ',1)+1,StringLength(lon_dms)) AS float32) AS lon_s

FROM [t] )