0

I'm working on an app that will display drugstores in a map. I have a table that has all drugstores, each with its Lat/Lon.

We used several different sources to add the data to the table: the Walgreens location app, Waze, Google Maps, among others. Naturally, one location can be in all three sources, so there are many duplicates.

The problem is that one specific location may be in Waze and the Walgreens app, but they both have slightly different coordinates.

How do I determine if two sets of coordinates are for the same location? Is there a "difference" I can calculate and then determine if they're different locations?

1
  • 1
    You may find that there's more than one kind of distance involved. What if one facility is called "Bob's Hardware" in one database, and "Bob's True Value" in another, and "Anytown True Value" in a third? What if there really are two Starbucks on either side of a busy intersection? Working with crowd-sourced data means spelling errors, positioning errors, and phantom features.
    – Vince
    Dec 13 '19 at 11:43
1

You can compute the distance between pairs of points. Then it is up to you to determine under what threshold you consider that two points represent the same location.

You can get a rough distance by using the Haversine formula: https://en.m.wikipedia.org/wiki/Haversine_formula

If you want to be more precise, you can convert your longitude/latitude coordinates to UTM (https://en.m.wikipedia.org/wiki/Universal_Transverse_Mercator_coordinate_system) and then compute the difference to get the distance in meters.

0

You could apply a clustering algorithm to regroup points, such as ST_ClusterDBSCAN. You could either use only the geometries (if your dataset is sparse) or you could break down by name (or other attributes). You might want not to use exact name match but rather string similarity (ex: via pg_trm).

Let's note that this clustering algorithm will regroup geometries that are within a given distance of any feature already in the cluster. It is better than using only the distance. For example, if you acceptable distance is 5 meters and there are 3 points that are linearly located 4 meters away from the previous point (X----X----X), then the clustering algorithm will regroup the 3 points while considering only the distance would remove the central point and leave the 2 other ones.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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