Maintenance - Places with identical geocodes
This article shows how you can create an Admin Report that lists all places that have identical geocodes (latitude and longitude). The resulting report lists all relevant places with identical geocodes that should provide the information necessary to allow merging the places.
Creating the Report
- In Admin/Reports select the 'Add New' tab.
- Enter a Report Name and Description:
- Select the 'No' radio button against 'Active' (this ensures the report is viewable only by the system administrator).
- Copy the SQL code listed below and paste in the last text box (above the 'Save Report' button).
- Save the Report.
Enter the following code in the SQL SELECT statement box above the Save Report button.
SELECT t3.id, t3.place, t3.latitude, t3.longitude FROM tng_places AS t3 INNER JOIN ( SELECT t1.latitude, t1.longitude, count( t1.id ) AS Records FROM tng_places AS t1 WHERE t1.latitude != '' GROUP BY t1.latitude, t1.longitude HAVING count( t1.id ) >1 ) AS t2 ON t3.latitude = t2.latitude AND t3.longitude = t2.longitude ORDER BY t3.latitude, t3.longitude
Viewing and Using the Report
You can then use the list to merge the places with similar names and identical geocodes, using
Maintenance - Places - Zoom & Place Levels - Places with no Zoom or Place Level set
Maintenance - Places with no maps - Places that still need to be geocoded
- Original SQL was provided on the User2 list by Mike Helms on 6 Oct 2011
- Enhanced by Ulli Heist to exclude latitudes that are null