I have a csv file with latitude and longitude columns in the format 147°27'38.27" but import as table assigns Text ANSII variable length. Changing type to Longitude results in 147.0000 with the minutes and seconds lost. Must I convert to decimal degrees?

M8 latest

Thanks

Aussie Nature Shots

Yes, I think so Colin. There's a handy help topic which might help Edit a Table with the Transform Toolbar.

Colin,

the manual states that Latitude format can be formatted as follows:

12º20'44.2"- Results in displays using degrees, minutes and decimal seconds formats.

I have a distant memory of striking the same issue, bulk converting ddºmm'ss.s" to dd mm.mmmm

Try making two new columns lat and lon. Make sure the type is lat lon, and then change the format to ddºmm'ss.s

Use the transform toolbar to copy from the text column to the new formattedLat column.

Another thought, did you try changingthe format on the lon column to ddºmm'ss.s? You may find that works as well.

<edit> And lastly in my brain dump, the manual also advises:

Latitudes and longitudes expect that a single number with a decimal point, like 90.1234is intended as a decimal degrees value. Other numbers in the generic form of '90 2 3'-type strings will be parsed as degrees, minutes and sections using the two or three numbers with white space or almost any characters in between. This makes it easy to enter degrees, minutes and seconds values for latitude and longitude without hunting all over the keyboard for obscure ways of entering the "degree" character.

Try removing the º ' " symbols and reimport and change format.

Dale

Dale,

All three of your suggestions work well. (Didn't know this--saves writing RegExp.)

To clarify, I think the second suggestion should be

... did you try changingthe format data type on the lon column [from text to] to ddºmm'ss.s Longitude?

Tim

Thanks for the ideas. Here's my solution in excel (VBA)

Function DMS2Deg(sInp As String)

  DMS2Deg = Evaluate(Replace(Replace(Replace(sInp, """", ""), "°", "+"), "'", "/360+") & "/3600")

End Function

With credit going to a guru at http://www.excelforum.com/microsoft-office-application-help-excel-help-forum/

Format required "147° 27' 38.27"""

Aussie Nature Shots

Oops, should be

Function DMS2Deg(sInp As String)

  DMS2Deg = Evaluate(Replace(Replace(Replace(sInp, """", ""), "°", "+"), "'", "/60+") & "/3600")

End Function

Aussie Nature Shots

Colin, Tim,

importing a correctly formatted and supported coord format such as Colin's example above should NOT result in a rounding to degrees when changing type to Longitude.

I found my original workflow, it was to add the two new columns, with column type set as lat/lon respectively, and format as ddºmm'ss.ss". I stripped the º ' " symbols and then used the transform tool bar to copy from the original lat/lon columns.

It's curious that the manual describes parsing just about any string for lat/lon values that uses "the two or three numbers with white space or almost any characters in between" but not lat long formats otherwise supported.

I did note and chuckle at the manuals mention of "degrees, minutes and sections"

I'll send a note to sales, with the suggestion to support the format as noted above.

Lastly as I'm working on a Mac, power users all know to strike option shift 8 to enter a ° symbol. Never realised windows made hunting about on a keyboard for obscure ways to enter the degree character so hard that it is easier to support lat long import without using the symbol…

Alt 0176 in Windows for the degrees symbol is simple enough. Thanks for the comments. As things are it's much easier to create the function in excel and convert to decimal degrees there for import into Manifold.

Aussie Nature Shots

Dale,

importing a correctly formatted and supported coord format such as Colin's example above should NOT result in a rounding to degrees when changing type to Longitude.

The most curious thing is that this only applies to imported text, or an imported table. Text imported to a table from CSV is rounded (actually, fixed) to degrees on conversion. But if we create a table from scratch inside Manifold, enter identical text (ANSI or Unicode), then perform the same conversion to Latitude/Longitude, the fractional part is translated correctly.

See the attached project, containing a table with a single pair of latitude, longitude values (sorry, in that order) encoded as ANSI (variable length). The values were entered manually.*

First, export this table to CSV to make a copy (default options).

Now do the conversion, in the original table or a direct duplicate (not via export/import). Change the type of the [latitude] column from ANSI text to Latitude, [longitude] to Longitude. Correct, right?

Then reimport the CSV file to a new table (accepting defaults, forcing text, forcing ANSI--no differences from changing these as far as I can see). A distraction is that an extra double-quote mark (") is appended, but this seems to be a red herring. Remove it from each column using Delete Right 1 in each case.

So now ostensibly, the imported table has the same data types as the source table had (before conversion), and each column contains exactly the same text. (Right?) But here, the same conversions as before somehow fail.

What does this suggest? Is there an invisible difference in internal representation between the original, "manual" text versus the imported text? (We could test this more deeply.)

Perhaps this anomaly is not directly Manifold's fault, but due to whatever Microsoft code handles CSV export/import?

A very curious bug (apparently). BTW same result in 32-bit Manfold using the above workflow. While if the source table is copied and pasted to a new project the direct conversion still succeeds.

[*I have ° on right-Alt + 0, aka AltGr + 0, along with a lot of other customizations, for real punctuation and for other languages. The Microsoft Keyboard Layout Creator is worth getting to know.]

Attachments:
Test latitude, longitude conversion.map

Import the table as is., open the table,

Find and replace ° with "space" as in press the space bar once.

Repeat this process for ' and "

Change the field type to Longitude and Latitude columns.

It will now be converted to decimal degrees....

But why? When the same text, entered inside Manifold, can be converted as it is, without replacements.

Why should replacement (of ° ' " by whitespace) be necessary for imported text, but not for text entered manually, given that both use the same data type?

It is an interesting mystery, worth getting to the bottom of.

Or am I seeing a difference that others don't?

You are seeing exactly what I see. Importing longitude/longitude with symbology fails. Manually entering the same longitude/longitude with symbology within Manifold works.

Sure there are many ways to overcome this, but there is an underlying unknown.

Importing supported position type and format should not fail on change type.

The only suggestion I have is that I once dealt with importing tables with lat/lon columns that had been exported from MapInfo. In that case MapInfo for whatever reason inserted an invisible character into the string. That was over ten years ago. Is this the case now?

"Symbology" is the wrong word here.

In my opinion it is the wrong word everywhere, an ignorant ESRI invention, unless someone should actually mean the study of symbols--but then we have "semiology" instead (for the study of signs).

What Dale means is just "non-alphabetic characters". What ESRI means by "symbology" is, in English, formatting.

It is, I meant typesetting symbols.

I have not had a chance to attend to the problem, will have another look on Monday. I think I've reached the extents of my technical ability.

be necessary for imported text, but not for text entered manually

I too have not had a chance to examine this in detail but my first guess is that there is something different between what is imported and what is entered manually even if the text looks the same. Something having to do with character sets, collation, etc., that causes the parser to see one thing in one case and something else in the other. That's just a hunch and where I'd start with a closer look.

That sounds sensible Dimitri.

But (well, half a but): SQL seems to find exact identity.

SELECT *

FROM

    [Test] AS [T]

    FULL JOIN

    [Test via CSV (trimmed)] AS [U]

    ON [T].[latitude] = [U].[latitude]

    AND [T].[longitude] = [U].[longitude]

;

Here [Test] is the table from the project I posted earlier. [Test via CSV (trimmed)] is the result of exporting [Test] to CSV, reimporting it, then using transform [Delete right] 1 to remove the extra trailing ".

The result of the query is a single row, four columns.

I think what is going on here is that the CSV importer in 8 which utilizes Microsoft code results in a difference from manual entry. That's not going to change in 8 but is straightforward to workaround in Radian and 9.

For example, CSV could be imported using Microsoft code always as text and then further digestion done within Manifold code. Whether one imports text or enters that text manually the source text would be the same and thus the digested result would be the same as well. I've made sure people working on CSV import are aware of the issue so whatever is going on here it will remain a fondly remembered quirk of 8 but not show up in 9.

Thanks for reporting that Dimitri.

Aussie Nature Shots

The table in the attached map automatically converts to decimal degrees by changing the column type to Latitude and longitude.

Yes, that's stated repeatedly in several posts above.