Maintenance - Places with no maps

Purpose

This article shows you how you can create an Admin Report that lists all places having no coordinates (latitude and longitude). The resulting report lists all relevant places as a link that takes you directly to the 'Places / Edit Existing Place' page where you can insert the coordinates or search for them using the 'show/hide clickable map' button (provided you have a Google Map key).

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

• BE SURE you replace ALL instances of 'mytree' with your tree ID (inside single quotes).
• Save the Report.

SQL Code

For TNG V8, you need to change the admin/editplace.php in the following SQL to admin_editplace.php

SELECT id, pl.gedcom,
concat('<a href="admin/editplace.php?ID=',pl.id,'&tree=', pl.gedcom,'", target=_blank>', pl.place, '</a>') as Places,
 notes, concat('<a href="placesearch.php?psearch=',replace(pl.place,' ','+'), '">', evcount, '</a>') as eventcount
FROM tng_places as pl
join
( select gedcom, place, count(*) as evcount from
( SELECT gedcom, birthplace AS place
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'mytree'

) as p group by gedcom, place

) as plc using ( gedcom, place )
where (isnull(longitude) or isnull(latitude) or longitude='' or latitude = '')
order by evcount desc, gedcom, pl.place

Viewing and Using the Report

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

If you click on a place name in the list it will take you to the 'Places / Edit Existing Place' page.

Related pages

Maintenance - Places - Zoom & Place Levels - Places with no Zoom or Place Level set

Acknowledgments

• Original SQL - Luke Weerts
• See TNG Forum: https://tng.community/index.php?/forums/index.php?showtopic=3273 for a variety of 'tweaks' to the original SQL and that provided here.