Report for finding non-indexed places

[brumer]

I have several people adding people & families into my Tree but to ensure places get added uniformly, I regularly go to places in Admin & scroll thru all the places, one by one & "index" the ones that as yet don't have lat/long entered.

I'm wondering if anyone has already written a report (or would like to), that will bring up a list pf "non-indexed" places that could be printed out. I do this job almost daily & sometimes there are no new entries but I still need to go thru the list page by page just in case.

[Scotty]

I have several people adding people & families into my Tree but to ensure places get added uniformly, I regularly go to places in Admin & scroll thru all the places, one by one & "index" the ones that as yet don't have lat/long entered.

I'm wondering if anyone has already written a report (or would like to), that will bring up a list pf "non-indexed" places that could be printed out. I do this job almost daily & sometimes there are no new entries but I still need to go thru the list page by page just in case.

Don't remember who wrote this.... but it works

SELECT place,latitude,longitude FROM tng_places WHERE (latitude is null or latitude = "") and (longitude is null or longitude = "") ORDER BY tng_places.place

And this one is kinda cool for finding places with no users attached to them. If you are interested in that sort of thing. You have to put in your gedcom name in several places. E.G., WHERE gedcom = ''

SELECT id, gedcom, place, longitude, latitude, notes
FROM tng_places
WHERE gedcom = ''
AND place
IN (

SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (

SELECT gedcom, birthplace AS place
FROM `tng_people`
WHERE gedcom = ''
UNION SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = ''
UNION SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = ''
UNION SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = ''
UNION SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = ''
UNION SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = ''
) AS p
USING ( gedcom, place )
WHERE pl.gedcom = ''
AND isnull( p.place )
)

I know Luke put this last one together.

Scotty

[brumer]

Thankyou Scotty......... I did search but maybe not in the right place?

Much appreciated!

[Luke]

I have several people adding people & families into my Tree but to ensure places get added uniformly, I regularly go to places in Admin & scroll thru all the places, one by one & "index" the ones that as yet don't have lat/long entered.

I'm wondering if anyone has already written a report (or would like to), that will bring up a list pf "non-indexed" places that could be printed out. icon_question.gif I do this job almost daily & sometimes there are no new entries but I still need to go thru the list page by page just in case.

Hi all,

Here is a report that I call "Places - No Map". Not only does it find the non-indexed places but sorts the list by number of events attached to the placename starting with the largest number of events. That way you can fix the most used places first.

This SQL should be used in "Report" not "phpmyadmin" because it uses html links. Wherever you see 'yourtree' change it to the name you've given to your tree

Choosing the "Places" name link takes you straight to 'place editing' so that you can modify latitude and longitude using the googlemap.

Choosing the eventcount number takes you to the list of events for that name.

After changing a place just use "refresh" on the report page and it will give you a new list without the ones you just fixed.

Luke

Scotty thanks for crediting my code, I appreciate that. Thank you for the code you contributed. I didn't realize I needed to check both null and empty latitude and longitude. I added that to my SQL below.

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 = 'yourtree'
UNION all SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'yourtree'

) 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

[Ken Roy]

Hi all,

Here is a report that I call "Places - No Map". Not only does it find the non-indexed places but sorts the list by number of events attached to the placename starting with the largest number of events. That way you can fix the most used places first.

This SQL should be used in "Report" not "phpmyadmin" because it uses html links. Wherever you see 'yourtree' change it to the name you've given to your tree

Choosing the "Places" name link takes you straight to 'place editing' so that you can modify latitude and longitude using the googlemap.

Choosing the eventcount number takes you to the list of events for that name.

After changing a place just use "refresh" on the report page and it will give you a new list without the ones you just fixed.

Luke

Scotty thanks for crediting my code, I appreciate that. Thank you for the code you contributed. I didn't realize I needed to check both null and empty latitude and longitude. I added that to my SQL below.

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 = 'yourtree'
UNION all SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'yourtree'

) 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

Luke,

This looked very interesting and promising, so I tried it on my site after replacing 'yourtree' with initially my 'Tree ID' (T0001) and then later using my 'Tree Name' (Antoine_Roy). In both case, the test report function in TNG only returns the SQL syntax with no results.

I put the query in phpMyAdmin and it returns 0 rows selected, so I must be missing another key element or this requires something else in order for it to work.

[satyricon]

It worked OK for me.

Might I suggest 2 things:

a) You have changed EVERY occurence of 'yourtree' (I missed two!)

b) You are using the correct name - go to Admin -> Trees and it is the description under the ID column you should be using. (I had used the description under Tree Name at first).

Regards

Adrian

(PS: You obviously have to have locations that do not have lat/long in the tree!)

[Ken Roy]

It worked OK for me.

Might I suggest 2 things:

a) You have changed EVERY occurence of 'yourtree' (I missed two!)

b) You are using the correct name - go to Admin -> Trees and it is the description under the ID column you should be using. (I had used the description under Tree Name at first).

Regards

Adrian

(PS: You obviously have to have locations that do not have lat/long in the tree!)

Adrian,

Thanks for your reply. I did not catch it earlier, but a Search / Replace in PSPad added an extra blank for the Tree ID in between the single quotes.

Report works great and I have a lot of places left to geocode. The Event Count shows the ones that should be done first.

Luke, thanks for another great report. The Place links also make it very helpful to geocode those locations.

[Luke]

Adrian,

Thanks for your reply. I did not catch it earlier, but a Search / Replace in PSPad added an extra blank for the Tree ID in between the single quotes.

Report works great and I have a lot of places left to geocode. The Event Count shows the ones that should be done first.

Luke, thanks for another great report. The Place links also make it very helpful to geocode those locations.

Hi all,

Glad you got it to work.

Ken, thanks for the encouragement. I try to make the job of geocoding by hand a little easier.

Google Maps does great on cities and towns, pretty good on counties though that is a little harder to verify since the maps don't actually have county names. Also I've noticed that combining a city, county, and state in a search will sometimes geocode the county rather than the city. Have any of you run into locations that Google Maps can't find. What sources do you use?

Specifically I have trouble with U.S. townships. I've tried Wikipedia to get latlong. That works pretty well but not every Wikipedia story has latlong. I've also used placenames.com which works pretty well on US places as long as you realize the name has the form "Lancaster, Township of" All these methods require a lot of copying and pasting to fill in. Anybody have any solutions that work better for the USA and also work for the rest of the world?

Luke

[Scotty]

Hi all,

Glad you got it to work.

Ken, thanks for the encouragement. I try to make the job of geocoding by hand a little easier.

Google Maps does great on cities and towns, pretty good on counties though that is a little harder to verify since the maps don't actually have county names. Also I've noticed that combining a city, county, and state in a search will sometimes geocode the county rather than the city. Have any of you run into locations that Google Maps can't find. What sources do you use?

Specifically I have trouble with U.S. townships. I've tried Wikipedia to get latlong. That works pretty well but not every Wikipedia story has latlong. I've also used placenames.com which works pretty well on US places as long as you realize the name has the form "Lancaster, Township of" All these methods require a lot of copying and pasting to fill in. Anybody have any solutions that work better for the USA and also work for the rest of the world?

Luke

Luke, I too like the improvement over the original code....

As far as finding geocodes I use the following:

www.clocations.com U.S. and Possessions only.

geonames.usgs.gov/ click on the search domestic names or foreign.

Nice thing about this one is you can narrow it down by state, then county, and then feature class, say cemetery. You find the name and click on that and then it displays the Decimal Long and Lat. AND it usually has the older names in there from wayback. Take for instance, Albion Cemetery, Albion, Whitman county, WA. It isn't there, but Guy Cemetery is.... they changed the name of the town and cemetery in 1912! NOW, of course if you didn't know this it could be a problem. But, the 1910 Census says Guy and the 1920 Census says Albion.

Such fun, when looking for names.

Scotty

[Ken Roy]

Luke, I too like the improvement over the original code....

As far as finding geocodes I use the following:

www.clocations.com U.S. and Possessions only.

geonames.usgs.gov/ click on the search domestic names or foreign.

Nice thing about this one is you can narrow it down by state, then county, and then feature class, say cemetery. You find the name and click on that and then it displays the Decimal Long and Lat. AND it usually has the older names in there from wayback. Take for instance, Albion Cemetery, Albion, Whitman county, WA. It isn't there, but Guy Cemetery is.... they changed the name of the town and cemetery in 1912! NOW, of course if you didn't know this it could be a problem. But, the 1910 Census says Guy and the 1920 Census says Albion.

Such fun, when looking for names.

Scotty

Scotty,

Thanks for the link to geonames.usgs.gov/ I searched for Dickeyville, which existed only for 1 year 1869-1870 which included the 1870 census and it returned Frenchville, where I grew up. When I search for Frenchville on Google Maps, it returns the wrong town, yes there are two different Frenchville in Aroostook County, Maine, so I typically have to use Upper Frenchville for Google to find the correct town. The post office at Upper Frenchville no longer exists.

Location names are not the only challenge. A lot of my ancestors were baptized and married at St Basile, Madawaska County, New Brunswick. Of course some of these events actually took place at Ste Luce in Upper Frenchville but were recorded in the parish at St Basile in New Brunswick, Canada.

So one also needs to know some history of the region in doing genealogy research.

Luke,

Thanks for making it very easy to geocode. The event counts really add the level of importance on knowing where the biggest bang for the buck is.

As soon as we open the TNG Wiki backup I will add a reference to this forum entry to the Google Maps topic.

[Ken Roy]

Hi all,

Here is a report that I call "Places - No Map". Not only does it find the non-indexed places but sorts the list by number of events attached to the placename starting with the largest number of events. That way you can fix the most used places first.

This SQL should be used in "Report" not "phpmyadmin" because it uses html links. Wherever you see 'yourtree' change it to the name you've given to your tree

Choosing the "Places" name link takes you straight to 'place editing' so that you can modify latitude and longitude using the googlemap.

Choosing the eventcount number takes you to the list of events for that name.

After changing a place just use "refresh" on the report page and it will give you a new list without the ones you just fixed.

Luke

Scotty thanks for crediting my code, I appreciate that. Thank you for the code you contributed. I didn't realize I needed to check both null and empty latitude and longitude. I added that to my SQL below.

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 = 'yourtree'
UNION all SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'yourtree'

) 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 a variation on Luke's query for a report on Places - No Zoom or Placelevel to check for places where zoom and placelevel were not set, by replacing the where clause above to

where (isnull(zoom) or isnull(placelevel) or zoom=0 or placelevel = 0)

[theKiwi]

Actually you'd need this to find those places that do have a latitude (which I've assumed means you've also got the longitude) but don't have the place level or zoom set

where (latitude!=NULL or latitude!=0) AND (isnull(zoom) or isnull(placelevel) or zoom=0 or placelevel=0)

Roger

[Ken Roy]

Actually you'd need this to find those places that do have a latitude (which I've assumed means you've also got the longitude) but don't have the place level or zoom set

where (latitude!=NULL or latitude!=0) AND (isnull(zoom) or isnull(placelevel) or zoom=0 or placelevel=0)

Roger

Thanks Roger,

Your query only returns Places that have been geocoded but where the zoom or placelevel were not set. 134 locations whereas my query also returned places that had not yet been geocoded (3220).

[theKiwi]

I was assuming there was little point in finding the big set (3,220 places that had no zoom or place level), when those places can be found by the query for an empty or 0 latitude or longitude.

The ones that it's helpful to find are those that were geocoded, but didn't have the zoom or placelevel set. I expect this to be particularly true for those people who import latitude and longitude from Legacy to be able to isolate those places they haven't yet set a zoom or placelevel for, even though they've got the latitude/longitude set.

Roger

[Luke]

Hi all,

Here is a report that I call "Places - No Map". Not only does it find the non-indexed places but sorts the list by number of events attached to the placename starting with the largest number of events. That way you can fix the most used places first.

This SQL should be used in "Report" not "phpmyadmin" because it uses html links. Wherever you see 'yourtree' change it to the name you've given to your tree

Choosing the "Places" name link takes you straight to 'place editing' so that you can modify latitude and longitude using the googlemap.

Choosing the eventcount number takes you to the list of events for that name.

After changing a place just use "refresh" on the report page and it will give you a new list without the ones you just fixed.

Luke

Scotty thanks for crediting my code, I appreciate that. Thank you for the code you contributed. I didn't realize I needed to check both null and empty latitude and longitude. I added that to my SQL below.

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 = 'yourtree'
UNION all SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'yourtree'

) 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

Hi all, Scotty, thanks for the very useful geocoding resources. Ken and Roger thanks for your alternative versions of the code. I try to use placenames that are historically accurate for the event dates. Not an easy task for so many ancestors from Germany. So I changed the query to give me the year of the earliest (First) and latest (Last) events, to aid me in finding the correct name(s) for those years. Instructions for using it are the same. Hope you find it useful. Luke

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, 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 = 'yourtree'
UNION all SELECT gedcom, altbirthplace, altbirthdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, marrplace, marrdatetr
FROM `tng_families`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, deathplace, deathdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, burialplace, burialdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, eventplace, eventdatetr
FROM tng_events
WHERE gedcom = 'yourtree'

) 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

[theKiwi]

Cool - Thanks Luke!!!!

[Paul Howes]

Hi everyone. I was attracted to these posts from the mailing list topic. This looks like a really useful utility, but can someone tell me please, as a complete neophyte when it comes to SQL, where do I run this code, or what can I paste it into to make this report work? Many thanks

Paul

www.howesfamilies.com

[Ken Roy]

Hi everyone. I was attracted to these posts from the mailing list topic. This looks like a really useful utility, but can someone tell me please, as a complete neophyte when it comes to SQL, where do I run this code, or what can I paste it into to make this report work? Many thanks

Paul

www.howesfamilies.com

Paul,

Go to Admin >> Reports and select the Add New tab and create a new report. Give it a title and description. Leave the report as Active No and copy the SQL code from above and paste it in the SQL input box at the bottom of the Admin Report page, and click Save.

Then run the report from the Test icon in the Admin >> Reports list when ever you want to do some more geocoding. The SQL will be repeated at the top of the produced page, so just ignore it.

Hope that helps.

[Paul Howes]

Thank you,Ken. I will try that tomorrow

Paul

[Paul Howes]

Yay! Very happy customer! Many thanks to Luke and Ken. Other new users: remember to change EVERY occurrence of 'yourtree' to the name you chose under ID in Admin/Trees.

This is SUCH a time-saver. Brilliant

[aewold]

There is a typo in the Places -- No Map code:

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

There should be a space between the comma and the target=, as this:

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

Note that I also added the target= to the eventcount link.

(The target= was being nullified by attaching to the href= code.)

Andy

p.s. -- Luke, if you have the time to look into it, I have many different places with a greater-than and less-than signs surrounding the place name (thanks to Ancestral File) that group into a single "blank" place name on this report with 160,000+ entries. Any way to get these split out and clickable?

[Luke]

There is a typo in the Places -- No Map code:

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

There should be a space between the comma and the target=, as this:

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

Note that I also added the target= to the eventcount link. (The target= was being nullified by attaching to the href= code.) Andy p.s. -- Luke, if you have the time to look into it, I have many different places with a greater-than and less-than signs surrounding the place name (thanks to Ancestral File) that group into a single "blank" place name on this report with 160,000+ entries. Any way to get these split out and clickable?

Hello Andy, Thank you for finding my html syntax errors!!! I was never able to figure out why the "target=" was not working. What a difference a space makes. As to your places being surrounded by '<.....>', there are two solutions: 1. Fix my SQL query to properly display those entries and then in the geocoding, they can optionally be removed 2. Use SQL to permanenty trim the offending '<,,,>' off the entries, so that my present SQL query works, Do you want the '<...>' to stay because your next import will put them back in or was it an accident that happened one time? It seems to mess up the regular placename editor on TNG 6.1.4 does it also on 7.0? Below is solution 1:

SELECT id, pl.gedcom, 
concat('<a href="admin/editplace.php?ID=',pl.id,'&tree=', pl.gedcom,'", target=_blank>', replace(replace(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 = 'weerts'
UNION all SELECT gedcom, altbirthplace, altbirthdatetr
FROM `tng_people`
WHERE gedcom = 'weerts'
UNION all SELECT gedcom, marrplace, marrdatetr
FROM `tng_families`
WHERE gedcom = 'weerts'
UNION all SELECT gedcom, deathplace, deathdatetr
FROM `tng_people`
WHERE gedcom = 'weerts'
UNION all SELECT gedcom, burialplace, burialdatetr
FROM `tng_people`
WHERE gedcom = 'weerts'
UNION all SELECT gedcom, eventplace, eventdatetr
FROM tng_events
WHERE gedcom = 'weerts'

) 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

I modified:

pl.place

to:

replace(replace(pl.place,'>',''),'<','')

Solution 2 is more difficult because data in 'places' is repeated at every place as well so you need to update the placenames at each place. You can use either 'phpmyadmin' or 'reports'. In this case phpmyadmin is preferable because it tells you how many records are changed. 'Report' does an update but gives an error because 'update' has no output like 'select'. Here are the seven updates you need:

update tng_events set eventplace = replace(replace( eventplace, '>', '' ) , '<', '' )  where eventplace like '<%' or eventplace like '%>'

update tng_families set marrplace = replace(replace( marrplace, '>', '' ) , '<', '' )  where marrplace like '<%' or marrplace like '%>'

update tng_people set burialplace = replace(replace( burialtplace, '>', '' ) , '<', '' )  where burialplace like '<%' or burialplace like '%>'

update tng_people set deathplace = replace(replace( deathplace, '>', '' ) , '<', '' )  where deathplace like '<%' or deathplace like '%>'

update tng_people set altbirthplace = replace(replace( altbirthplace, '>', '' ) , '<', '') where altbirthplace like '<%' or altbirthplace like '%>'

update tng_people set birthplace = replace(replace( birthplace, '>', '' ) , '<', '' ) where birthplace like '<%' or birthplace like '%>'

update tng_places set place = replace(replace(place,'>',''),'<','') where place like '<%' or place like '%>'

Be careful that you do this to every place name in your database ( I may have missed some LDS places)

otherwise some will get 'lost'. The info will still be on the record but will not be listed in "Places"

Let me know how this works for you,

Luke

[Luke]

Here is a variation on Luke's query for a report on Places - No Zoom or Placelevel to check for places where zoom and placelevel were not set, by replacing the where clause above to

where (isnull(zoom) or isnull(placelevel) or zoom=0 or placelevel = 0)

Hi all, Here's another variation of the query I call "Places -- Invalid Map". This finds improperly geocoded places. In my case it found locations geocoded in degrees/minutes/seconds format, geocodes with '0'(zero) at end of decimal, non printable and space characters from copying and pasting from other sources, and decimals with more than 12 places after the decimal point. Basically, I checked for lat/lon that was an invalid decimal number but not empty. Directions for installing remain the same as before. Hope this helps with cleaning up your 'places' table Luke

SELECT id, pl.gedcom, 
concat('<a href="admin/editplace.php?ID=',pl.id,'&tree=', pl.gedcom,'", target=_blank>', replace(replace(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 = 'yourtree'
UNION all SELECT gedcom, altbirthplace, altbirthdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, marrplace, marrdatetr
FROM `tng_families`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, deathplace, deathdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, burialplace, burialdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, eventplace, eventdatetr
FROM tng_events
WHERE gedcom = 'yourtree'

) as p group by gedcom, place 

) as plc using ( gedcom, place )
where (longitude <> '' and length(longitude + 0) <> length(longitude)) or (latitude <> '' and length(latitude + 0) <> length(latitude))
order by evcount desc, gedcom, pl.place

[Scotty]

Hi all,

Here's another variation of the query I call "Places -- Invalid Map". This finds improperly geocoded places. In my case it found locations geocoded in degrees/minutes/seconds format, geocodes with '0'(zero) at end of decimal, non printable and space characters from copying and pasting from other sources, and decimals with more than 12 places after the decimal point.

Basically, I checked for lat/lon that was an invalid decimal number but not empty. Directions for installing remain the same as before.

Hope this helps with cleaning up your 'places' table

Luke

SELECT id, pl.gedcom, 
concat('<a href="admin/editplace.php?ID=',pl.id,'&tree=', pl.gedcom,'", target=_blank>', replace(replace(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 = 'yourtree'
UNION all SELECT gedcom, altbirthplace, altbirthdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, marrplace, marrdatetr
FROM `tng_families`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, deathplace, deathdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, burialplace, burialdatetr
FROM `tng_people`
WHERE gedcom = 'yourtree'
UNION all SELECT gedcom, eventplace, eventdatetr
FROM tng_events
WHERE gedcom = 'yourtree'

) as p group by gedcom, place 

) as plc using ( gedcom, place )
where (longitude <> '' and length(longitude + 0) <> length(longitude)) or (latitude <> '' and length(latitude + 0) <> length(latitude))
order by evcount desc, gedcom, pl.place

Luke,

Interesting code but it has one flaw.

Google maps returns decimal locations with 15 places when you change the exact location. So this code returned 354 places for me!

I tried to figure out where that limiter was in the code... but to no avail....

Scotty

[Luke]

Luke,

Interesting code but it has one flaw.

Google maps returns decimal locations with 15 places when you change the exact location. So this code returned 354 places for me!

I tried to figure out where that limiter was in the code... but to no avail....

Scotty

Hi Scotty,

It has to be an automatic conversion at "latitude + 0" and "longitude + 0" . This changes the text field to a decimal field, which determines the invalid characters. Now I just have to figure out how to override the 12 digit default to 15. I only had a few of these so I just changed the decimals but I can see this wouldn't work for you.

Luke