Wanted: Parse text from response URL

[pnelson]

I'm trying to parse data using the Geocoding Web Service (from Google Maps or Yahoo Maps for example) to find the specific latitude and longitude for an address. The Request URL is formulated as follows:

[color:purple]http://local.yahooapis.com/MapsService/V1/geocode

It's easy enough to form a sample Request Url as follows:

[color:purple]http://local.yahooapis.com/MapsService/V1/geocode?appid=[color:red]YD-9G7bey8_JXxQP6rxl.fBFGgCdNjoDMACQA--&street=701+First+Ave&city=Sunnyvale&state=CA

using data from fields in FileMaker

(note: [color:red]you need to apply for and supply your own APPID for your application as shown in red)

The Response field data (xsd format) are displayed in a FileMaker web viewer window. The schema document for this service response is located at: [color:purple]http://local.yahooapis.com/MapsService/V1/GeocodeResponse.xsd

The data being displayed in the web viewer appear like this (depending of course on the address you are geocoding)

42.742843-71.459653389 Main StNashuaNH03060-5035US

So is there a way to parse the lat/long figures from that response string?

I'm trying to figure out if creating/using a Custom Function is way to go to parse out the latitude and longitude figures from the response URL so they can simply be placed in fields for later use during other mapping activities. I'm stumped as to how to go about this... any help would be appreciated. I did not see any such Custom Functions in the library specific to map API geocoding.

Edited October 29, 2008 by Guest

[comment]

It's hard to tell what the format is from a single example. Is there no separator between latitude and longitude?

Moreover, are you sure that's the exact response you get (as opposed to what you see)? You should check what the page source looks like. I would expect the response to be an XML document which could be imported.

[Raybaudi]

Hi

you can try this way:

1) while in Layout Mode give a name to the object webViewer ( for example "wv" )

2) create a calculation field with this calc ( for Latitude ):

Let([

text = GetLayoutObjectAttribute ( "wv" ; "content" );

start = Position ( text ; "" ; 1 ; 1 ) + 10 ;

end = Position ( text ; "" ; 1 ; 1 )

];

Middle ( text ; start ; end - start )

)

3) similar calculation for Longitude:

Let([

text = GetLayoutObjectAttribute ( "wv" ; "content" );

start = Position ( text ; "" ; 1 ; 1 ) + 11 ;

end = Position ( text ; "" ; 1 ; 1 )

];

Middle ( text ; start ; end - start )

)

May be that you'll have to launch a script that performs those calculations and set those fields.

[Fenton]

Probably the fastest way to do this is to use Import XML, with a XSL stylesheet to transform the result to FileMaker XML structure. It's not that hard to do, 'cause there isn't much returned. The attached file will do this, and return an error result, if there is one (hopefully). You need to put in your own Yahoo ID.

Address_wError.zip

[pnelson]

I've started looking at both responses and I can see ways to use each approach so once I've had time to digest this a bit more I'll try and report back and to contribute my results. In the meantime thanks so much for pointing out some techniques I had not thought of and was not familiar with.

Per one reply, this would be the example of the source from the response URL:

<?xml version="1.0"?>

42.742843-71.459653

389 Main St

NashuaNH03060-5035US

Edited October 29, 2008 by Guest

[comment]

Then I think you should follow Fenton's advice.

[pnelson]

Fenton, your solution works great for a single record at a time, however, I'm not figuring out how to batch geocode a Found Set of records using the same technique. Can you shed some light on this? I'd sure appreciate it.

[Fenton]

Just run a loop in FileMaker. Yahoo is not crazy enough to allow free batch geocoding; nor is anyone else I don't think.

[jwjay]

I tried raybaudi's solution above with Google Maps and it worked for one record, but not the others. I think it had something to do with whether the webpage was loaded in the web viewer when the calculation was performed.

I did find success when applying it with a Scraping Script I found here:

scraping.pdf

lat: and lng: were the tags I used to locate the values

[jwjay]

Thanks for posting that info fenton. I'm definitely going to try to learn more about xml and xsl after seeing how quickly that worked compared to the Web Viewer scraping.

One issue comes up with fenton's example when doing a batch of records. The xml only has one record and FM gives an error message stating:

There are less records in the data source than in the target table. Not all records were updated

After selecting 'OK' the first record in the found set is updated with the record's information where the script was initiated.

To bypass this issue I used a temporary table that would only hold one record and I passed the individual records as variables to that table and ran the geocode script and then passed the results back to the list of addresses.

[HazMatt]

I don't know if this is helpful to anyone (or if my solution is the most elegant way to do this), but I came up with this script that I developed to pull values from URL strings like this:

 transaction_id=409v0984jv098098d098v09834234v&error_code=999&auth_response_text=This is a dummy message

Parse Value from Text String

#The next line contains the suggested script parameters for this script: 

#//  Text string to parse field value from 

"" 

& "¶" & 

//  Name of the field 

"" 

& "¶" & 

//  Separator character 

"" 

#Note:  This script assumes the provided $text_string is a single line (no returns) 

If [ Get(PrivilegeSetName) ≠ "[Full Access]" ] 

#Prevent script cancelation 

Allow User Abort [ Off ] 

End If 

If [ not $$debug_mode_is_on ] 

#Suppress FileMaker errors 

Set Error Capture [ On ] 

End If 

If [ IsEmpty(Get(ScriptParameter)) ] 

#No script parameter supplied 

Beep 

Show Custom Dialog [ Title: "Program Error"; Message: "Sorry, no script parameter was specified in the script:¶" & Get 

(ScriptName); Buttons: “OK” ] 

Exit Script [  ] 

Else 

#Convert script parameter to variable(s) 

Set Variable [ $text_string; Value:GetValue(Get(ScriptParameter); 1) ] 

Set Variable [ $field_name; Value:GetValue(Get(ScriptParameter); 2) ] 

Set Variable [ $separator_character; Value:GetValue(Get(ScriptParameter); 3) ] 

End If 

#Append "=" to field name 

Set Variable [ $field_name; Value:$field_name & "=" ] 

If [ PatternCount($text_string; $field_name) = 0 ] 

#Field does not exist in supplied text 

Exit Script [ "" ] 

End If 

If [ Right($text_string; 1) ≠ $separator_character ] 

#Text string does not end in separator character - Append it now 

Set Variable [ $text_string; Value:$text_string & $separator_character ] 

End If 

#Calculate length of field name 

Set Variable [ $field_name_length; Value:Length($field_name) ] 

#Find position of field_name element 

Set Variable [ $field_value_start_position; Value:Position($text_string; $field_name; 1; 1) + $field_name_length ] 

Set Variable [ $field_value_next_separator_position; Value:Position($text_string; $separator_character; $field_value_start_position; 

1) ] 

#Set field value 

Set Variable [ $field_value; Value:Middle($text_string; $field_value_start_position; $field_value_next_separator_position - 

$field_value_start_position) ] 

Exit Script [ Result: $field_value ]

Edited July 29, 2009 by Guest
Added "" to Exit Script [ ] step if field not found

[PatrickMc]

The XML file looks like this

<?xml version="1.0"?>

...42.742843-71.459653...

# Script latlong.txt

# Input argument - URL

var str URL

var str data ; cat $URL > $data

stex -c "^^]" $data > null ; stex -c "]^^" $data

stex -c "^^]" $data > null ; stex -c "]^^" $data

The script will pull data directly from the URL, then extract latitude and longitude. Script is in biterscripting ( http://www.biterscripting.com ) . To try, save the script as C:Scriptslatlong.txt, start biterscripting, enter the following command.

script latlong.txt URL("http://local.yahooapis.com/MapsService/...")

Make sure you are passing the correct value for the URL. Can also call this script directly from a web server without manually starting biterscripting.

Patrick