Methods for lat/lon quality control?

Question

I have a database with fields including latitude, longitude, city, state, and country.

When mapped, it is clear that a number of the sites are in the wrong location - most commonly because of a sign error:

I was thinking of writing a script to check for such errors, along the lines of

if country = USA and lon > 0 set lon = -1*lon
if country in (select names from europe) and lat < 0 set lat = -1*lat

etc.

This would be followed by additional checks by plotting the sites with their names over google maps, but this would get most of the errors.

1. What pitfalls to this approach should I anticipate?
2. Any suggestions for how to do comprehensively way?
3. Are there existing scripts that do this? It is hard to imagine that I am the first to face this issue.

Answer 1

In a global dataset you will have cases where a point is close to the equator, or Greenwich meridian. At these points a sign change could leave your location in the same country, but at the wrong location.

An alternative approach is to geocode the locations based on the city, county, and country fields. Create a field measuring the distance from the geocoded point to the location in your database. Where the distance is excessive attempt to invert the sign and test the new distance.

Geocoding is a well understood process, and gives a way to actively test all your points, not just the ones that look a bit odd.

Answer 2

One potential pitfall: if your table stores territories with the Country value as the "parent" Country (e.g. Northern Mariana Islands or Guam have Country=US), then your check for incorrect signs will be wrong. To continue with Guam and the Northern Marianas as an example, they are located in eastern (positive) longitudes, so the check for Lon>0 would be true, but it would be wrong to convert longitude to negative. Similar issues will exist for many British territories, as well.

Also, make sure your error checking looks for 0,0 in Lat and Long. These are often entered when coordinates are unknown, and may cause other problems in your error-checking if not accounted for.

Answer 3

A similar solution to @Matthew's would be to compare your stored lat/long values to that of a freely available City/State/Country dataset.

For example, the GeoNames dataset (see under "download server") already contains the Lat/Long values of many city/state/country locations. Doing some simple trigonometry (or if you have the data loaded into a spatial database like PostGIS) you could identify the distances of all features to their "known" location in the GeoNames database. Anything that is outside the a user-defined distance tolerance should be investigated further.