Jump to content
Quito

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

Screen Shot 2017-11-03 at 19.36.37.png

Share this post


Link to post
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 this post


Link to post
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

Screen Shot 2017-11-04 at 10.24.34.png

Screen Shot 2017-11-04 at 10.24.04.png

Screen Shot 2017-11-04 at 10.31.39.png

Screen Shot 2017-11-04 at 10.31.45.png

Screen Shot 2017-11-03 at 19.36.37.png

Screen Shot 2017-11-04 at 10.23.50.png

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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
#------------------end add lines--------------------------
Set Variable [$output; Value:$output & "[" & $lat & "," & $long & "],¶"]

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

Edited by doughemi
  • Thanks 1

Share this post


Link to post
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
#------------------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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Who Viewed the Topic

    12 members have viewed this topic:
    JMart  Peter Jambors  Lola  Steve Martino  LaRetta  BruceR  Lee Smith  Aussie John  siroos12  dansmith65  Agnes Riley  doughemi 
  • Similar Content

    • By Oliver Reid
      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
    • By typewriter
      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?
    • By hutcheedingo
      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.
    • By owangolama
      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?
      Thanks in advance!
      (I'm not a pro; I'm just somebody that has programmed a bunch of stuff into my own FM databases.)
    • By Hoytopher
      The script which I am using (kindly provided by another user) is: "http://www.publishersmarketplace.com/pm/search?ss_q=" & Contacts::First Name & "+" & Contacts::Last Name & "&ss_c=dealmaker"  
      Using this script, I am able to generate the URL that I wish. The URL is different depending on who I am searching for, and as such each URL is unique to a specific record. I currently have a field set to perform this script - the problem is creating a button capable of going to this calculated URL. Trying to enter it as the URL to go to leads to me searching for Contacts::First Name rather than the desired first name. I am unsure of how to go forward and am not finding useful information relevant to this problem in my searches.
×

Important Information

By using this site, you agree to our Terms of Use.