Not used placenames report

[dicksonvm]

Hi,

Is it possible to generate a report of the not used placenames? While there is not a administrative tool to delete the no longer used placenames that have some coordinates data, I would like to have this report and delete them manually. Any ideas?

Thank you!

Dickson von Mühlen

[Luke]

Hi,

Is it possible to generate a report of the not used placenames? While there is not a administrative tool to delete the no longer used placenames that have some coordinates data, I would like to have this report and delete them manually. Any ideas?

Thank you!

Dickson von Mühlen

Hi Dickson,

This is fairly easy with SQL. Here is an example of the report:

http://www.cousinfolk.net/showreport.php?reportID=70

Here is the SQL statement. It requires at least mysql 4.1 because it uses subqueries.

Here's how it works: A union of six queries finds all the fields where places are used. 'tng_places left join the used places' will give all places and if there isn't an equivalent used place that field will have a null. So all nulls will list unused places. Then list the fields where place is in the unused list

How does yours work? Is this what you want?

Luke

Here's the code:

Note: 'yourtree' must be replaced with the name of you default tree in eight spots below

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

SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (

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

Now that you see the places that are not used and are satisfied this list is correct and has what you want to delete, you may replace the first line (everything up to FROM) with the single word 'DELETE' and it will delete all the records in that the previous 'select' listed. For reference only,This is the equivalent DELETE: DO NOT COPY THIS, change your select that you know is working. One small typo in 'yourtree' could be disastrous AND delete far more than you intended. You have been warned.

DELETE
FROM tng_places
WHERE gedcom = 'yourtree'
AND place
IN (

SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (

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

[dicksonvm]

Hi Luke,

That was exactly what I needed and worked perfectly! I preferred to carefully delete manually each not used placename, but in only a few minutes I solved my "great" problem.

Thank you!

Dickskon von Mühlen

[Luke]

Hi Luke,

That was exactly what I needed and worked perfectly! I preferred to carefully delete manually each not used placename, but in only a few minutes I solved my "great" problem.

Thank you!

Dickskon von Mühlen

Hi Dickson,

You're welcome.

Glad it worked for you. I understand completely wanting to carefully delete manually especially if its a small number. SQL deletes make me extremely nervous and SQL updates make me a little nervous. I always do an equivalent select before doing either but ....

Really like your website. Wonder if my family and your family came across each other in Brasil. My grandfather, Edward Dicke, was a missionary to the German population in Ijuhy, Rio Grande do Sul, Brazil (anglicized spelling?) for 10 years in the 1920's. My mother was born there. Is that the same as Ijui on your website? See:

http://www.cousinfolk.net/getperson.php?pe...amp;tree=weerts

Sincerely,

Luke

[dicksonvm]

Luke,

The world is very small. Its probable that our ancestors really met in Brasil. I found out your grandfather was missionary in the same church we are related today (Lutheran Church - Missouri Synod). I found your grandfather's name in this document of our church: http://www.ielb.org.br/old/recursos/pdfs/p...falecidos_0.pdf

In the famly of my wife there are several pastors of this Church, including her great grandfather: http://genealogia.tati.dickson.nom.br/getp...personID=I18664. As he graduated as Pastor in 1918 and our Brazilian church was not so big that time, is really possible they met. And his father, Guilherme Augusto, lived near Ijuí in the 1920's. Ijuhy is the old spelling of Ijuí, that appears in my site. RS = Rio Grande do Sul (state).

Very interesting!

Best regards,

Dickson

[Tom Strong]

Luke, et al

I have tried this report query in version 7.1 and get no results. I know I have unassociated/unlinked places so I thought I'd give this a try. When I run the report, all seems to operate correctly but I get NO RESULTS. Here is one example of an unlinked place that should be reported. http://www.strongfamilytree.org/placesearc...kogee,+Oklahoma

Has something changed in the database structure of version 7 that will allow this report to run but have no results? Or could it possibly be that these have been orphaned from TNG i.e. they have NO PLACE NUMBER and only exist in the SQL database and are manifested in TNG for any places query?

Here is the report result of running the query

SQL: SELECT id, gedcom, place, longitude, latitude, notes FROM tng_places WHERE gedcom = 'Strong Family Tree' AND place IN ( SELECT pl.place FROM tng_places AS pl LEFT JOIN ( SELECT gedcom, birthplace AS place FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, altbirthplace FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, marrplace FROM `tng_families` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, deathplace FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, burialplace FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, eventplace FROM tng_events WHERE gedcom = 'Strong Family Tree' ) AS p USING ( gedcom, place ) WHERE pl.gedcom = 'Strong Family Tree' AND isnull( p.place ) )

Home Search Print Logout

# ID Tree Place Longitude Latitude Notes

Thanks in advance,

Tom Strong

http://strongfamilytree.org

[theKiwi]

... WHERE gedcom = 'Strong Family Tree'

Your gedcom is STR06 not "Strong Family Tree".

Change that in all the places and it will work I'm sure.

Roger

[arnold]

This is fairly easy with SQL. Here is an example of the report:

Not easy for me!!!

What do I do to make the code below work? I know enough to get to phpMyAdmin.

My thanks,

Arnold

[Tom Strong]

Thanks Roger,

For the database name assumed that it was the "Tree Name" and not the "Tree ID". When I used the tree ID it worked perfectly.

Arnold, when they say insert the code below, they are referring to the Admin Reports Create a New Report. On the TNG Wiki there is a graphic explanation of how this works. http://tng.lythgoes.net/wiki/index.php?tit..._with_no_Events

Cheers,

Tom Strong

Not easy for me!!!

What do I do to make the code below work? I know enough to get to phpMyAdmin.

My thanks,

Arnold

[arnold]

Thanks, Tom.

Worked like a charm.

The TNG Wiki <http://tng.lythgoes.net/wiki/index.php?title=Main_Page> is fantastic.

Arnold