Hi,

I have a database with +1000 records, each with a long list of long/lat coordinates that need to be reversed to lat/long.  The amount of decimal numbers is not fixed, yet every longitude value starts with an opening bracket "[" and is separated from the Lat value by a comma ",". Each coordinate ends with a closing bracket "]," and a carriage return.

I guess if it were just one coordinate I could do it with left, middle and right. Hope someone can ease me towards a solution.

Best,

Daniel

Try this script:

Go to Record/Request/Page [First]
Loop
	Set Variable [$output; Value:""]
	Set Variable [$input; Value:YourTable::longlat]
	Set Variable [$totalCount; Value:ValueCount ( $input )]
	Set Variable [$currValCount; Value:1]
	Loop
		Set Variable [$currVal; Value:GetValue ( $input ; $currValCount )]
		Set Variable [$firstComma; Value:Position ( $currVal ; "," ; 1 ; 1 )]
		Set Variable [$valLength; Value:Length (  $currVal  )]
		Set Variable [$long; Value:GetAsNumber(Left ($currVal ; $firstComma ))]
		Set Variable [$lat; Value:GetAsNumber(Right ($currVal ; $valLength -$firstComma ))]
		Set Variable [$output; Value:$output & "[" & $lat & "," & $long & "],¶"]
		Set Variable [$currValCount; Value:$currValCount + 1]
		Exit Loop If [$currValCount > $totalCount]
	End Loop
	Set Field [YourTable::latlong; $output]
	Go to Record/Request/Page [Next; Exit after last]
End Loop

 

Thank you Doughemi,

Your script worked beautifully with most coordinates.  Please give these screenshots a look. What's weird is:

a. latitude coordinates are being stripped of the 0 unit. 

b. the negative value is conserved.

c. If the latitude starts with 1 or -1, then the unit is conserved.

All the very best,
Daniel

When asking us to parse text data, please post the actual text data so that we can put it in a field, and not a screen shot that we can not access.

TIA,

Lee

Hi Lee,

Sure thing:

[-77.84121237982664,-1.06364908387218], 
[-77.84166562911301,-1.063525160518601], 
[-77.84237632439182,-1.063411868215063], 
[-77.84261193006375,-1.063284600036807], 
[-77.84265653299182,-1.063260506766708], 
[-77.84271928886058,-1.063226607398715], 
[-77.84282835004831,-1.063032767601322], 
[-77.84286600406656,-1.062984747435158], 

[-77.5319437625604,0.6486004404427348], 
[-77.53183056992712,0.6486539147774151], 
[-77.531751134082,0.6487010449218822], 
[-77.53169773427386,0.6487465350210897], 
[-77.53165753615083,0.6487968087259288], 
[-77.53162461615723,0.6488600039549386], 
[-77.53158956333689,0.6489673077817226], 
[-77.53155820366391,0.6490971148515756], 
[-77.53152359126715,0.649367471361435], 
[-77.5314980705212,0.6495039651069012], 
[-77.53139221405952,0.649865012864832], 
[-77.53130483819433,0.650194416498282], 

[-78.59388666327277,-0.8382328250941384], 
[-78.59346449863854,-0.8381310547019249], 
[-78.59285416063659,-0.8381231110950926], 
[-78.59205038517958,-0.8381126495159497], 
[-78.5915825353803,-0.8379765929485172], 
[-78.59110306730949,-0.837748780848333], 
[-78.58955391777447,-0.8365195786769242], 
[-78.58955387377618,-0.8365195750339982], 
[-78.58933291646856,-0.8362419382737962], 
[-78.58909508106591,-0.8360332578099272], 

11 hours ago, Quito said:

 latitude coordinates are being stripped of the 0 unit. 

I used the GetAsNumber function to save a pile of parsing out brackets. This resulted in expected behavior for numbers: FileMaker strips leading and trailing zeros from numbers.

Add the following lines to the script 

Set Variable [$lat; Value:GetAsNumber(Right ($currVal ; $valLength -$firstComma ))]
#--------Add these lines after above current line in script---------
If [Abs ( $lat ) <1 and $lat < 0]
	Set Variable [$lat; Value:Substitute ( $lat ; "-" ; "-0" )]
End If
If [Abs ( $lat ) <1 and $lat > 0]
	Set Variable [$lat; Value:"0" & $lat]
End If
#------------------end add lines--------------------------
Set Variable [$output; Value:$output & "[" & $lat & "," & $long & "],¶"]

This works only because FileMaker is quite sloppy about typecasting numbers and text.

Edited November 5, 20178 yr by doughemi

2 hours ago, doughemi said:

I used the GetAsNumber function to save a pile of parsing out brackets. This resulted in expected behavior for numbers: FileMaker strips leading and trailing zeros from numbers.

Add the following lines to the script 

Set Variable [$lat; Value:GetAsNumber(Right ($currVal ; $valLength -$firstComma ))]
#--------Add these lines after above current line in script---------
If [Abs ( $lat ) <1 and $lat < 0]
	Set Variable [$lat; Value:Substitute ( $lat ; "-" ; "-0" )]
End If
If [Abs ( $lat ) <1 and $lat > 0]
	Set Variable [$lat; Value:"0" & $lat]
End If
#------------------end add lines--------------------------
Set Variable [$output; Value:$output & "[" & $lat & "," & $long & "],¶"]

This works only because FileMaker is quite sloppy about typecasting numbers and text.

That worked perfectly Doughemi!

Thank you very much!