Admin report: Find places without coordinates

From TNG_Wiki
Jump to navigation Jump to search

Description: Admin report: Find places without coordinates when you're using a TNG version lower than 8 you will have to replace admin_editplace.php with admin/editplace.php


Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate


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`

UNION all SELECT gedcom, altbirthplace
FROM `tng_people`

UNION all SELECT gedcom, marrplace
FROM `tng_families`

UNION all SELECT gedcom, deathplace
FROM `tng_people`

UNION all SELECT gedcom, burialplace
FROM `tng_people`

UNION all SELECT gedcom, eventplace
FROM tng_events


) 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

Here is an example of what this report looks like; File:Tng reports 064 no coordinates.jpg