User:Kmpoppe/Potential Note Duplicates
While the German Schwerpunkt der Woche No. 82 AND the UK Quarterly Project of Q1 2023 both aimed to close Map Notes, I came up with a way to find potential duplicates of Notes that have either been created by a glitch in a software or at will - either out of ignorance or just by accident.
StreetComplete bug was also identified thanks to this report (there is no obvious broken code but it is being investigated)
Process
The process was initially developed based on SQL Server and has since moved to a server system running Linux and is now PHP/MySQL based. I will link a GitHub repository with the code once it's cleaned up.
Prerequisits
- Importing all admin_level=2 countries and their relation ids from an overpass query, courtesy of Mateusz Konieczny,
[out:json][timeout:1000];relation["admin_level"="2"][boundary=administrative][type!=multilinestring];out tags;
- Importing a WKT-(multi)polygon for each of those relations from https://polygons.openstreetmap.fr/
Initial load and everyday update
- Downloading the `planet-notes.osn` that contains all Map Notes ever created, filtering them to only load the open ones and putting them into a table, without information about what country they are in.
- Running through every note and checking whether its coordinates are contained within the polygon of one of the above-mentioned 216 countries.
Finding potential duplicates
The Notes get ordered by their Distance from Lat/Lon 0/0. While that value might be the same for any pair of +/+, +/-, -/+ or -/- degrees, for both latitude and longitude, filtering the Notes by the country they are in first has a high likelihood of not delivering a false-positive.
The
RANK()
-function of SQL Server are then used to create sets of Notes (a pair at least, triplets and quadruplets are common as well, but there have been occurrences with 43(!) notes in the same place).
These sets of Notes will be made available here as Wiki-Tables, or other formats if requested.
The Candidates
The candidates are now listed outside the wiki: https://dupnotes.webmapping.cyou/
There are now 7 superscripted "hints" as to what application created the note, guessed from the special properties of the note text.
| Info | Application |
|---|---|
| ED | EveryDoor |
| MM | Maps.Me or its newer fork OrganicMaps |
| OM | |
| OMB | OSMyBiz |
| OO | onosm.org |
| SC xy.z | StreetComplete or its fork StreetComplete ExpertEdition, version xy.z |
| SC (EE) xy.z |