Determining if two locations are duplicate

Question

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?

Answer 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.

Answer 2

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.