Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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:

enter image description here

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.
share|improve this question
    
Geocoding - sounds like you need a geocoder. en.wikipedia.org/wiki/Geocoding#List_of_some_geocoding_systems –  Mapperz Nov 2 '11 at 17:54
    
@Mapperz that is a good start, the lat/lon coordinates are not the city / state location, but the city is the nearest city. I could do the query with some tolerance and get better results than just >/<0. –  David Nov 2 '11 at 18:02
3  
I would ardently advise against making automatic fixes, especially on the basis of such crude checks. You risk making things irretrievably worse. Instead, when a potential error is detected, flag the record and move on. In a later step you can review the flags (or apply better checks), update the data, and leave a related record containing the original values (in case it turns out the fix was incorrect). –  whuber Nov 2 '11 at 18:54
    
As far as simple checks go, in addition to looking for sign errors, look for lat-lon reversals. Also search for points with coordinates extremely close to (0,0) (many databases store missing values as zeros, so a lot of junk gets dumped off the west African coast :-) and other points with out-of-bounds latitudes or longitudes. For more general advice I would (in all modesty) point you to essential data checking tests (on another SE site). –  whuber Nov 2 '11 at 18:56

3 Answers 3

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.

share|improve this answer
2  
parts of Alaska are also in the Eastern hemisphere. –  iant Nov 2 '11 at 18:55
4  
@iant Right--the westernmost parts of Alaska :-). –  whuber Nov 2 '11 at 19:00

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.

share|improve this answer

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.

share|improve this answer
2  
+1 especially for "investigated further" in place of "automatically changed." –  whuber Nov 2 '11 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.