Latitude/Longitude from an Address

[Ender]

Attached is sample technique of (1. Automating and verifying address data entry, and (2. getting the Latitude/Longitude from that address.

There are some requirements for making this work in your solution:

1. You should have your addresses broken down into separate fields for different parts (House #, Street, City.)

2. You need to access and convert parts of the Census databases for the areas you may have addresses in. In the US, you can find this data here: http://www.census.gov/geo/www/cob/index.html

When I built this, the main thing I was trying to do was find a way to show Waiting List children that are in the same geographical area as children that are already enrolled in a class. Knowing the latitude/longitude was much more accurate than simply using the zip codes. As a bonus, I could then feed the latitude/longitude numbers into a charting plugin to create demographics maps showing children enrolled in each room.

Hope this helps you as much as helped me and my school.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

addresses.zip

[GW]

I like your 'Address Lookup Program' and I visited the Census data website... However, I can not seem to find the 'address' info in order to use in my area.

Am I looking in the right place? All I seem to find is the Lat and Long numbers... no reference to addresses.

Any additional help would be greatly appreciated.

Thanks,

Gary Wallis

Specialized Data Services

[email protected]

FileMaker Version: 6

Platform: Mac OS X Panther

[Ender]

Sorry--I should have gone into more detail. The raw Tiger census files are pretty cryptic. I had to convert several files to FileMaker databases with separate fields and then combine them to get the data into one table. I'll see if I can back track to figure out which files they were.

[GW]

Thank you...

I am working on a project that would use GPS equiped radios. This type of info would be very helpful.

Gary Wallis

Specialized Data Services

[email protected]

[Ender]

Try this site: http://www.census.gov/geo/www/tiger/tiger2002/tgr2002.html

You have to download data by county, convert the .RT1 file and create fields to parse the data. This part is time consuming because you have to extract from one text string all the data based on position. You can find the position of each field in the Data Dictionary (Chapter 6) PDF file on the web site.

Do the same thing with the .RTC file and the .RT6 file (you don't need all the fields). The RT6 file gets you the Zip codes, the RTC file gets you the City. For the RT6 data, use a lookup based on Record ID, for the RTC file, a lookup based on FIPS Code.

[Ender]

I have updated the files to handle prefix directions, like "801 E 38th St" and created converters to make this process easier for those interested.

The converters allow you to take the cryptic .RT1 file and .RTC file from the Census Bureau web site, and with just a few imports, get the data into the Intersection file. You can use the Intersection file with an existing solution by adding keys and relationships in a file where you want to get latitude-longitude or address data entry automation. Just be aware that the relationships require a very specific address format to work correctly (standard abbreviations for streets type and direction, etc.)

The files are in FM5/6 format, but you should be able to convert them to FM7 if needed.

I'd love to hear about it if anyone ends up using this.

LongLat.zip

[senior]

Hey Mike,

This would be great for my application. I'm setting up a database that will be used for compiling lake data and tracking our process of making maps for those lakes. The database will also house a list of contacts, such as bait shops, fishing guides, government folks and others that are somehow related to fishing. In the past, we've manually matched people up with lakes based on zip codes and town names, but that's a painfully slow process. Your method would be sweet, and I'm excited to try it out.

I'm not a stage where I'm ready to start adding cool stuff yet, but I'll try this out when I am. Anyway, I had a question: What plugin(s) are you using to create maps from the latitude/longitude?

[Ender]

For this application, I used the xmChart plugin, charting longitude/latitude as x-y coordinates on a scattergram chart. I had to use a picture of the map area as a background picture for the chart. The points are then laid on top of the map. Attached is a screenshot of a map.

If you are interested in a more flexible solution, I believe the USGS has a database (available for purchase) that has elevation data for the entire US. I saw a demo of it from a couple guys at DevCon. It included some program that interfaced with FileMaker to plot points on the elevation map. I wish I could remember what it was called. I'll let you know if it comes to me.

Picture.pdf

[USGSguy]

I think I'm the guy you're looking for at the USGS. We've developed (along with NOAA, EPA, and the CENSUS Bureau) a pretty slick mapping module called MARPLOT, interfaced to FileMaker thru two plugins. All of this software is in the public domain under the system name of LandView. More info is available at http://landview.census.gov including a down-loadable fully functional demo (albeit only for one county in Virginia). There are about 36 map layers, including State and County lines, hospitals, schools, and many others. You can also relatively easily add your own layer...all you need is the latitude and longitude of the points you want to add.

Let me know if you need more info.

Cheers,

Jerry (the USGSguy)

[Ender]

Thanks for the info, Jerry.

[Denethor]

Is there an better way to do this for searching the whole US?

After doing the import for just one state my FM DB is >400MB. The entire US would be HUGE!!!

Like 20GB and the conversion would take weeks.

I imagine the lookup on a file that large would not be quick either.

Is there an extrenal service one could refernce somehow to get the Lat and Long for evey entry in your DB currently and then as new records are added in the future?

[Fenton]

I was just investigating the GoogleMapsAPI today for the 1st time, and I ran across this web site:

http://emad.fano.us/blog/?p=277

He provided a web services link that allows you to get the lat and long for a given address (well, it's in there anyway). Here's mine, fetched using AppleScript and curl:

do shell script "curl http://www.localsearchmaps.com/geo/?loc=1728+Park+Ave,+San+Diego,+CA++92116"

Edited February 23, 2006 by Guest

[Wim Decorte]

If you're on Windows, buy a copy of MS MapPoint. It can return the lat/long of any address, overlay data on maps (like color states based on sales), ... great application and lots of activex objects to use with VBscript.

[Denethor]

Can you call scripts like that from inside FM or are you refferign to makign a web based front end to the DB? I was trying to do this all in FM and this is my first FM app so bear with me if questions seem dumb.

[ino]

:violin:

[color:purple]Question? :qwery:

Way when I use do shell script for grab: applescript (set cell "result" of current record to do shell script "curl http://www.localsearchmaps.com/geo/?loc=1728+Park+Ave,+San+Diego,+CA++92116")

http://www.localsearchmaps.com/geo/?loc=1728+Park+Ave,+San+Diego,+CA++92116

I get result: [color:blue]map.centerAndZoom(new GPoint(-117.107353, 32.762806), 4);

- but when i try to grab: applescript (set cell "result" of current record to do shell script "curl http://www.localsearchmaps.com/geo/?format=json&country=ES&city=MADRID")

http://www.localsearchmaps.com/geo/?format=json&country=ES&city=MADRID

result is: [color:red]{"lat":0,"long":0}

for testing I use : Quick Tip - Download web content with Curl by Jasse Traynham 06-04

The same result is with: Shell Script Example from newcenturydata;

when i write the some in [color:red]browser result is correct: [color:blue]{"level":"city","city":"MADRID","lat":40.4203,"long":-3.7057}

also if I use getWebpage from Dave Dowling (use AppleScript which does the work of grabbing the text and populating the FMPro fields with the text of the web page:

http://www.localsearchmaps.com/geo/?format=json&country=ES&city=MADRID

result is correct: [color:blue]{"level":"city","city":"MADRID","lat":40.4203,"long":-3.7057}

or

http://www.localsearchmaps.com/geo/?format=json&country=HR&city=Murter

result is correct: [color:blue]{"level":"city","city":"Murter","lat":43.7999,"long":15.6}

[color:red]Where is mistake

[color:purple]Second question

[color:red]How to write - create :

set cell "result" of current record to do shell script "curl http://...”

for [color:red]Windows

Thanks in advance

Ino :thankyou:

[Fenton]

It's the ampersands (&). The shell script doesn't like them unless they're escaped. There's more than one way to do that. You can use single quotes around the URL, or escape them with with backslash ().

do shell script "curl http://www.localsearchmaps.com/geo/?format=json&country=ES&city=Madrid"

do shell script "curl 'http://www.localsearchmaps.com/geo/?format=json&country=ES&city=Madrid'"

If you use a backslash escape character in AppleScript, which you want to pass on to the shell script you have to use 2 (since the 1st one is considered an escape character for the next character).

You can also use the phrase "quoted form of" to set your data into an AppleScript variable first. Clear as mud?

Edited April 19, 2006 by Guest

[ino]

Thank you very much

ino

[ralph.nusser]

With the cf of Vaughan Bromfield

DistanceBetweenPoints ( Lat1; Lon1; Lat2; Lon2; Units )

http://www.briandunning.com/cf/143

I can calculate the distance between two points.

How do I calculate the center of the map (longitude/latitude) when I have a bunch of longitudes and latitudes?

Edited May 27, 2006 by Guest

[ralph.nusser]

With Virtual Earth I can put about 10 pushpins on a map:

http://local.live.com/default.aspx?v=2&cp=53.5~9.998249&style=r&lvl=9&rtp=null~null&sp=aN.53.596000_9.964249_Team%20Busch%20GmbH_~aN.53.549222_9.947253_FileMaker%20Magazin_~aN.43.488168_10.622438_Andreas%20Hebben_~aN.53.553239_10.003986_Landschulz%20Engler_~aN.53.558600_10.036812_Beasts%20Associated%20Teixeira_~aN.53.699607_10.146416_Lippert%20Kai_~aN.53.547467_9.556048_beo%20GmbH_~aN.53.642022_10.074467_waechter%20Partner_

How do I do it if I have more?

[comment]

How do I calculate the center of the map (longitude/latitude) when I have a bunch of longitudes and latitudes?

I think it depends on how you define 'center'. If I have 99 locations in Chicago, and 1 in Paris, France - should the 'center' be in New York, or in the middle of the Atlantic?

[ralph.nusser]

I would like to calculate the map center for the 99 locations in Chicago.

[Fenton]

Wouldn't the center be: ( Max of the Latitude + Min Latititude ) / 2

Max and Min being Summary fields. Same for Longitude.

[ralph.nusser]

When I imagine the rectangle the min/max points build then I have to say: You're right. Thank you.

[ralph.nusser]

Google has now geocoding as well:

I built a custom function

/*

Syntax:

cf_Google_Geocode_URL ( Street ; ZIP; town; country; API_KEY )



Author:

Ralph Nusser, 14.06.2006



Example Input: 

Address of FileMaker, Inc.

http://maps.google.com/maps/geo?q=5201+Patrick+Henry+Drive,Santa+Clara,CA&output=xml&key==YOUR_PERSONAL_API_KEY



Example Google Response:

  <?xml version="1.0" encoding="UTF-8" ?> 

- 

- 

  5201 Patrick Henry Drive,Santa Clara,CA

- 

  200 

  geocode

  

- 

  

5201 Patrick Henry Dr, Santa Clara, CA 95054, USA

- - US - CA - Santa Clara - Santa Clara - 5201 Patrick Henry Dr - 95054 - -121.984498,37.406485,0 Hint: Your personal key you get here: http://www.google.com/apis/maps/signup.html If you have a dynamic IP: z.B. "http://217.162.191.211" http://wwwfindnot.com tells you your dynamic IP. 50'000 retrievals per day is allowed. */ Let ( [ a = "http://maps.google.com/maps/geo?q=" ; b= "&output=xml&key="; street = Substitute ( cf_Text2HTML ( Street ); " "; "+"); zip = ZIP & "+" & Substitute ( town; " "; "+"); country = Country; // Jeder muss für seinen PC einen Google-Key haben key = API_KEY ] ; a & street & "," & zip & "+" & country & b & key )

Yahoo:

/*

Syntax:

cf_Yahoo_Geocode_URL ( street ; city ; state ; appid )





Musterbeispiel:

http://api.local.yahoo.com/MapsService/V1/geocode?appid=YahooDemo&street=5201+PatrickHenry+Drive+Mailstop+C-54&city=Santa+Clara&state=CA

*/



Let ( [ 

a = "http://api.local.yahoo.com/MapsService/V1/geocode?appid=";

b = "&street=";

c = "&city=";

d = "&state=";

Strasse = Substitute ( street; [" "; "+"] );

Ort = Substitute ( city; [" "; "+"] )



]  // End Let

; 

// calculation:



a  & appid & b & Strasse & c & Ort & d & state



)

Happy FileMaking Ralph Nusser

[Oldfogey]

If I have 99 locations in Chicago, and 1 in Paris, France - should the 'center' be in New York, or in the middle of the Atlantic?

Sydney.

Because, no matter how many lats and longs you have, Sydney is still the centre of the universe in spite of what my New York friends say.

Edited June 18, 2006 by Guest

[Genx]

Lol, you crack me up sometimes but we all know the centre of the universe is Melbourne anyway so it's all good.