# geocoding Calculation to switch a long list of long/lat to lat/long

## Recommended Posts

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

##### Share on other sites

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```

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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],

##### Share on other sites
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
Set Variable [\$output; Value:\$output & "[" & \$lat & "," & \$long & "],¶"]```

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

Edited by doughemi
• 1

##### Share on other sites
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
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!

## Create an account

Register a new account

• ### Who Viewed the Topic

2 members have viewed this topic:
Carson  bac mac
• ### Similar Content

• I have table with list of addresses. I need simple way to show, visually how they are clustered geographically - ideally one map location "Dot" for each address.

Any suggestions - would like to display result on demand in a web viewer

• Hi
I am experimenting with Treemaps using the D3.js and D3plus.js libraries in FMPro Advanced 15 and the Webviewer (OSX 10.11). D3plus.js is really nice as it makes it much much easier to implement a Treemap using a simple JSON object containing your data. But sometimes thing don't work as expected and I don't fully understand why:
- I invoke D3plus code in a FileMaker global text field, as a data:url, iincluding the D3 and D3plus libs in an external reference (I don't include the complete code in the text field). So these are loaded at runtime.
- this works fine, but sometimes the treemap hich is a complex SVG graphics generated by D3) defaults to a 200x200 px area.
Reloading the webviewer usually fixes the problem (or manually changing the HTML body, by adding a space, wihich forces the webviewer to refresh its contents). But my question remains: why and when does this happen? My guess is that at times the Javascript code is not loaded in the proper order, but I can't figure out how to influence this.
The D3.js is invoked prior to D3plus.js, and all D3plus code is executed after these libraries are invoked.
Anyone with similar issues?

• I'm trying in vein to incorporate google map directions into Tim Dietrich's EasyMaps, and have tried about a dozen or more methods, none of which seem to work.
Basically, I work with a not-for-profit that buses minors home after some after school activities, that typically finish around 11pm.  To make life easy for the bus driver, I'm hoping to be able to generate the map with directions, and then email it directly to the bus driver, who opens it up on his smart phone, and follows the directions.
As I said, I've tried a number of methods, and none of them are working for me.  Being a not for profit, spending on operations like Seedcode's ProMaps is out of the question due to cost.
Has anyone, or does anyone know how to modify EasyMaps so that it will be able to give me directions, and then send those directions to another person?
at the very least, we'd like to be able to send map with multiple points across, but everything would be ideal.

Appreciate the help.

• One of the sites that my database uses has recently started spamming ads. This eats up my personal hotspot bandwidth and messes up the user experience no end.
Is there a way to install an adblocker extension into the browser used by FM? I'm a Mac, still using FM v14, but will probably upgrade soon if that matters.
Does it use Safari or is it its own browser? Do folks have other solutions to this type of problem?