701021_en-US

liliconcom

Extracting Latitude Longitude from Text Column

Hello JMP Community,

 

I got this dataset of electrical vehicle data from data.gov. It has a column where it gives geo location but the column entries are in the given format:

 

POINT ( Longitude, Latitude) 

 

for example one row is 

 

POINT (-122.34301 47.659185) 

 

and I want to create two new continuous columns for Longitude and Latitude

 

My problem is that when I try to extract the string of numbers using the LEFT(), RIGHT(), or SUBSTR() function, the results are inconsistent because the string of numbers is not always the same.

 

Such as 

 

POINT (-122.6880708 48.2179983) & POINT (-121.9725 47.85674)

 

what would be the best way to make these latitude and longitude columns?

 

I've attached the JMP Data set. 

 

thank you,

 

-lovy 

 

Data Blending and CleanupRe: Extracting Latitude Longitude from Text Column

Very convenient way to split strings is via word():
https://www.jmp.com/support/help/en/18.0/#page/jmp/character-functions-2.shtml?os=win&source=application#ww8107571 

 

The 3rd argument allows to specify all characters which are used as separators, e.g.

 

string = "POINT (-122.34301 47.659185)";
Num(word(2,string, "( )"))
Num(word(3,string, "( )"))

 

Alternatively, you can use Text To Columns... from the Cols menu:

It will generate 3 columns. Afterwards, you just have to convert the 2nd and 3rd column to Numeric.

Re: Extracting Latitude Longitude from Text Column

You can also use recode, as a two-step process (once for lat, once for long).

Re: Extracting Latitude Longitude from Text Column

The Word() function is the easiest way.

If you learn only one Formula Editor function, Word() is the one