Compare Address to Longitude and Latitude for Accuracy

 

Is there a program that will do this comparison given that the data is in a csv file?

A comparison of what?

A comparison of what? You can't compare unless you have at least two things. The CSV file is one, what is the other? Another CSV file? A Map? Other?

PT

--
Garmin nüvi 200 (my first GPS), 780, & 3700 Series. And a Mac user.

A comparison of what

An adress is an adress.

An adress is attached to a building.
You would like a LAT & LONG of what ?. Lat & Long of the adress could be find with a little work.

Go to the position you want and save your lat & long, into your favorite.

Or you can also find a position of an adress with GoogleEarth. Just transfert the LAT & Long to your favorites and/or build your own csv file.

A csv file of the entire North America building adress convert in LAT & LONG are not available .
All the adress in your GPS are associate to a LAT&Long to be plot on your map.

Example:
If you would like to go at GARMIN Office.
You will search on your GPS for GARMIN.
Could you imagine a GPS telling you, do you want to go to GARMIN N 50 deg 54.102 min
W 001 deg 29.361 min or you prefer N 44 Deg 54.425 minW 122 59.673 min OR this one N 25 deg 03.707 min
E 121 deg 38.416 min .

PROBABLY not and me too.

But if your GPS is telling you the adress :
1200 E. 151st Street
Olathe, KS 66062-3426 , Kansas or

or 2345 Turner Road SE
Salem, OR 97302

Now you will know and choose the one you want.

Do you still prefer to compare to LAT & LONG.

Sometime maybe and sometime you don't want. Thats the reason why usually a car GPS provide what you are looking for as an adress and not LAT&LONG.

But if you prefer to go by a waypoint (LAT & LONG) , GPS give you the opportunity to do it but you have to do some work by yourself.

I have one of those list by LAT & LONG but i am telling you then i don't use it to often except maybe for hunting or fishing spot ....

csv Content

The csv file has long, lat, address, city, state, zip. I want to verify that the long and lat match the address to a very short distance. Think of the file as a generic csv file that is not necessarily in a particular format for say Garmin, etc.

There are many entries in the file and it's known that the address is correct. The long and lat have been collected from a variety of sources and may not be accurate enough to get directly to the property.

If there is a comparison program available I can easily use Excel to adapt the data to the format required including column swap or split into 2 files. It won't be practical to do some type of manual comparison on one entry at a time.

no way

CATraveler wrote:

The csv file has long, lat, address, city, state, zip. I want to verify that the long and lat match the address to a very short distance. Think of the file as a generic csv file that is not necessarily in a particular format for say Garmin, etc.

There are many entries in the file and it's known that the address is correct. The long and lat have been collected from a variety of sources and may not be accurate enough to get directly to the property.

If there is a comparison program available I can easily use Excel to adapt the data to the format required including column swap or split into 2 files. It won't be practical to do some type of manual comparison on one entry at a time.

There is no automated way. Some of the POI here were run to a geocode program which give you approximate log/lat. Sometime this program will give you way off log/lat. It will be your responsibility to verify them one by one and thru the painfull manual process.

Even if you use google or any other mapping tool, giving the address will alway give you approximate.

Lesson on using POI is either you create is by yourself and validate it one at a time or you just pray that the POI you got here is 100% accurate.

Most people say that this is just a tool. You have to verify it before you go. Which sometime unreasonable to me because sometime you go to places in a spur of a moment and you don't have time to validate it.

CSV Content

You can geocode the addresses using a batch decoder and then compare the coordinates using a distance formula.

The formulas are available here:

http://www.zipcodeworld.com/docs/distance.pdf

For most cases formula 1 or 2 is sufficient; however, the Great Circle Formula is the most accurate, but the most complex. If you want the results in feet, multiply by 5280, or whatever conversion factor is appropriate.

You can even write your own function in Excel using Visual Basic.

--
Tom

I think you did not answer the question

poitom wrote:

You can geocode the addresses using a batch decoder and then compare the coordinates using a distance formula.

The formulas are available here:

http://www.zipcodeworld.com/docs/distance.pdf

For most cases formula 1 or 2 is sufficient; however, the Great Circle Formula is the most accurate, but the most complex. If you want the results in feet, multiply by 5280, or whatever conversion factor is appropriate.

You can even write your own function in Excel using Visual Basic.

The problem was he wants to make sure that the POI long/lat is at least on the dot when you actually go to the place.

The one you attached is how to calculate distance between two points.

Either Way

Either way, you need to get at least two sets of coordinates to compare. Once you have them, the distance between the two will tell you how accurate they are.

--
Tom

Recommendation for a geocode batch decoder

It sounds like I should be able to decode the addresses into long and lat in a new csv file.

Then combine the two sets of coordinates into a single file and using my very rusty Excel skills run the distance formulas.

The formulas are simple enough. I don't need high accuracy for my purpose. Rather I'm just trying to establish that if someone GPS's to the coordinates the property location entrance should be obvious.

Any recommendations on which geocode batch decoder since I'm new at this?

Geocoding

See this thread for Geocoding sites:

http://www.poi-factory.com/node/8555

If you have questions on Excel, I can help.

--
Tom

but..

CATraveler wrote:

It sounds like I should be able to decode the addresses into long and lat in a new csv file....Then combine the two sets of coordinates into a single file and using my very rusty Excel skills run the distance formulas.

What if the original data had been obtained from a Batch Geocoder that has returned a "ZIP", or "City" level precision (without saying so). You need to find a Batch Geocoder that doesn't do this. Otherwise, you might get the same answers and conclude that both sets are right...when both sets are wrong!

For a small number of POIs, wouldn't you be better off viewing them on map and verifying them that way?

--
------------------------ Phil Hornby, Stockport, England ----------------------               http://GeePeeEx.com - Garmin POI Creation made easy           »      

I Agree

There are always risks, but as CATraveler said there are too many points for manual lookup. Anyway, even if address precision is indicated that still may be wrong because it depends on how the mapmaker interpolated addresses and how good the map is to begin with. To build on what you said: it may make the most sense to validate each point you plan to visit by viewing on a map to make certain that the address coincides.

At the very least, the distance formula can help focus attention on "some" of the problem coordinates.

Bottom line: There are no perfect answers - only better ones.

--
Tom

.

poitom wrote:

Anyway, even if address precision is indicated that still may be wrong because it depends on how the mapmaker interpolated addresses

We probably couldn't send a Cruise Missile through an open window, using Yahoo!'s geocoder then? laugh out loud

I have seen Yahoo!'s Geocoder return multiple 'address precision' results, when it couldn't find an exact match on the input. Quite difficult to handle that...

--
------------------------ Phil Hornby, Stockport, England ----------------------               http://GeePeeEx.com - Garmin POI Creation made easy           »      

My solution for small csv files

I think I know what you need and why. My address number is "1212", but in order for ANY map or GPS to come up with the correct location, I have to enter "1238". So, my address lat/lon is not correct for most mapping programs and GPSs.

When I try to verify a lat/lon for a given address, I send the addressing information to a web service like GPSVisualizer.com. You can "GEOCODE AN ADDRESS", either a single address or multiple addresses. The service will then convert your address to lat/lon, which you can then map. Mapping brings up Google maps, with the address transposed on the map. Click on an address, zoom in, confirm the location/address with the lat/lon in the lower left corner of the map.

Again, this is for small files - or as large as your patience allows. My thinking is the only way you can verify the lat/lon for a given address is "visually".

Good luck.

--
Garmin #1: eTrex Vista Garmin #2: Nuvi 350 Garmin #3: Forerunner 201 <br> "You are entitled to your own opinion, but you’re not entitled to your own facts." - Sen. D.P. Moynihan

You Got That Right

Hornbyp wrote:
poitom wrote:

Anyway, even if address precision is indicated that still may be wrong because it depends on how the mapmaker interpolated addresses

We probably couldn't send a Cruise Missile through an open window, using Yahoo!'s geocoder then? laugh out loud

I have seen Yahoo!'s Geocoder return multiple 'address precision' results, when it couldn't find an exact match on the input. Quite difficult to handle that...

Even our Military has trouble hitting the right building consistantly eventhough their systems cost a whole lot more than ours. I guess sometimes you don't get what you pay for.

But then again, since Yahoo is free, maybe we're getting what we pay for.

Regards,

--
Tom

address vs lat long not right

My experience is that the source of address data, which is propagated into maps like Mapsource or Google are inaccurate.

Example: I live on a short, dead end street. If I go to Google or Mapsource and look up my address - it shows me more than a block from where my house really is. If I look at where my house really is, the street address provided by Google or Mapsource is not a street number that exists anywhere - it is bogus.

So, if a POI is gathered from actually visiting the site and marking the location, it will be very accurate and the street address is not relevant. If I have a POI that I have used an address and looked up the lat/long - I ***always*** assume my GPS, guiding me to the lat/long I provided, will get me close - within a couple of blocks, and then I need to check for the real street numbers or look for the sign.

Until the source data gets better - the GPS can only take you to a lat/long - if the address doesn't match, well, what can you do?

--
___________________ Garmin 2455, 855, Oregon 550t

A Test

I used the 42 AZ addresses that I have and fed them into the batch mode of the Steve Morse program. I copied the output back to Excel and compared the 2 sets of coordinates using the great circle formula.

The results raise a lot of questions about my data as 13 of the entries were more than 500' apart. I used S&T to map my data (address and coordinates) for 3 of the locations and it confirmed the inconsistencies in my data. It also confirmed that the Morse proram coordinates are very close to the address. I've only manually compared 3 of the 13 since it is labor intensive.

I'm sure the addresses are correct so that leaves flaky coordinates. I've gone back to the owner of the data to see what he wants to do. Since this information is part of a guide it should probably be corrected and there are lots of approaches.

The important thing from this thread is that you guys/gals have given me lots of input (and lots of variations) on completing the task that I set out to accomplish. It is appreciated and keep the ideas coming.

Bob

The problem with geocoding addresses

Is the resulting coordinates are approximate. They use coordinates of known locations, and a formula to interpolate where a given address might be...and it's really only a best guess to within a block.

Example: My house # is 1004...the street on the east end of the block is S. 12th. The West end is S. 9th. The house is physically in the middle of the block; but, every GPSr I've used places my address about 1/4 a block away from where it is in reality.

The only way to get coordinates is to actually visit a place, and record them....and even those will be off by the margin of error for your particular GPSr at the moment you save coordinates. The second best way is an aerial photo overlay on a map with accurate coordinates - like google maps.

--
*Keith* MacBook Pro *wifi iPad(2012) w/BadElf GPS & iPhone6 + Navigon*

It helps to 1st "standardize" addresses per USPS specs

You will get more accurate geocoding if you "standardize" addresses before running them through a geocoder or looking them up on MapSource or Google Maps.

The US Postal Service has set standards for addressing mail. See http://pe.usps.gov/cpim/ftp/pubs/Pub28/pub28.pdf

For example, if you're given an address of 123 Roan Ridge, its address in standardized USPS format might be 123 ROANRIDGE DR. Because the address data in the better commercial databases follow USPS standards, you'll get more hits if your input data is in that same format.

You can standardize addresses one at a time at http://zip4.usps.com/zip4/welcome.jsp Unfortunately, USPS makes no provision to run multiple addresses through that Web app.

--
Garmin nüvi 250W | Garmin eTrex "yellow" | TomTom GO 910

USPS Batch Address Checker

Actually USPS does have a batch address checker that provides more information that the single address check. See http://www.usps.com/createmail/
It's part of their online mailing that delivers hardcopy.

But the mailing check portion can be used to verify standard addresses.

To access:
click Getting Started under Click2Mail, be sure to use the correct Getting Started
Sign Up for a free account and once you Sign In
Click Mailing List and upload your file

A header line is helpful to identify the fields. All of the non standard addresses will be listed as part of the verification of the file. Why do they care? Well the USPS provides a reduced rate to mail standard addresses.

Batch standardizing of US addresses

Thanks very much for that info, CATraveler. I was all over the USPS site last weekend, looking for a way to standardize multiple addresses. Finally I found an FAQ that said the ZIP Code lookup would not do batch checking, and that anyone who wanted to do that had to buy something.

You found a way around that!

Now I've just got to get through the most arcane and awful account sign-up I've ever experienced. Can't wait to try Click 2 Mail.

--
Garmin nüvi 250W | Garmin eTrex "yellow" | TomTom GO 910