Maintenance - Places with identical geocodes

Purpose

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.

SQL Code

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

After saving the report changes, click the 'Test' button. The resulting report should look like this:

You can then use the list to merge the places with similar names and identical geocodes, using

• the TNG Place Merge capability
• your desktop genealogy software if you Import Data.

Related pages

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

Acknowledgments

• 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