Maintenance - Places with no maps

From TNG_Wiki
Jump to: navigation, search


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=',,'&tree=', pl.gedcom,'", target=_blank>',, '</a>') as Places,
 notes, concat('<a href="placesearch.php?psearch=',replace(,' ','+'), '">', evcount, '</a>') as eventcount
FROM tng_places as pl
( 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,

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