Maintenance - Places with no maps
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.
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.
Maintenance - Places - Zoom & Place Levels - Places with no Zoom or Place Level set
- 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.