Maintenance - Place Level = 2

Purpose

This article shows you how to create an Admin Report that lists all places that have been geocoded and assigned a place level of 2 (Location). Note that you have have overridden Location to some other name. After installing the Google Maps - More Place Levels Mod, you might now want to split the place level 2 into different place levels based on how you are overriding the additional place levels.

The resulting report lists all relevant places as a link that takes you directly to the Places / Edit Existing Place page where you can change the place level.

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

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,' ','+'), '", 
target=_blank>', evcount, '</a>') as eventcount, First, Last
FROM tng_places as pl
join
( select gedcom, place, count(*) as evcount, if(max(dt) > 0, min(if(dt>0,year(dt),9999)), 'No') 
as First, if(max(dt) > 0, year(max(dt)), 'Date') as Last from
( SELECT gedcom, birthplace AS place, birthdatetr as dt
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, altbirthplace, altbirthdatetr
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, marrplace, marrdatetr
FROM `tng_families`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, deathplace, deathdatetr
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, burialplace, burialdatetr
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, eventplace, eventdatetr
FROM tng_events
WHERE gedcom = 'mytree'
) as p group by gedcom, place
) as plc using ( gedcom, place )
where placelevel=2
order by pl.place, evcount desc, gedcom

Note that you can change this report to list any place level by changing the where placelevel= value in the SQL shown above.

Viewing and Using the Report

After saving the report changes, click the Test button. The resulting report should look similar to the following report, except for the report title:

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

Acknowledgments

This report is based on the Maintenance - Places - Zoom & Place Levels report originally provided by Luke Weerts

Related Links

Google Maps - More Place Levels Mod