Mass Updates of Place and Zoom Levels Using Reports
Starting with version 9, TNG will geocode each place. However, the Place Level remains at zero (0) and the Zoom Level is set to ten (10).
Place and Zoom levels can be assigned based on the number of commas found in the Place field.
No need for phpMyAdmin. The user will be able to copy and paste code to the Reports area of their TNG site.
The statements in the report will update the tng_places table. If you are unsure about anything, then don't use it. Better yet, stop reading and don’t do anything.
Place and Zoom Level Update Report
The code will:
- Calculate the Place Level for each location based on the number of commas in the Place Field.
- Allow the user to set the Zoom Level for each Place Level.
- Select places where the “placelevel” value = 0 (new places) and updates Place and Zoom levels. It is optional to adjust all existing places.
- The user can elect to update all of their trees or a selected one.
Several examples are presented here. More information is found at the following link: http://connergenealogy.com/files/Download/Adjust_Place_And_Zoom_Levels_On_TNG_Report.pdf
Back Up Your TNG Tables.
- Sign in to your TNG site as administrator.
- Select UTILITIES.
- Select the “Select All” button.
- Next to “With selected:” is a pull down menu. Select “Back Up”.
- Select the GO button.
Step 1 – Create a report in the administration area.
Log onto TNG and enter the administration area. Open Reports and select Add New. At the very bottom of the screen is a box with the title "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:". You will insert a code snippet in this box.
Step 2 – Copy the Code.
There are two types of code. Type 1 is for users that include the name of the country in the place field. Type 2 is for users that left out the name or the country.
GET READY TO COPY AND PASTE....
TYPE 1 - The following is for places that include the name of the Country.
For example: “901 Starbit road, Towson, Baltimore county, Maryland, USA”
[BEGIN COPY AFTER THIS LINE]
/* Remember to SET Max Search Results to a high number */ UPDATE tng_places SET zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 5 WHEN 1 Then 7 WHEN 2 Then 11 WHEN 3 Then 14 WHEN 4 Then 16 else 18 END, placelevel= CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 6 WHEN 1 Then 5 WHEN 2 Then 4 WHEN 3 Then 3 WHEN 4 Then 2 else 1 END WHERE placelevel = 0; /* Remember to RESET Max Search Results back to 50 */
[END COPY BEFORE THIS LINE]
[PASTE SELECTED CODE IN THE LAST BOX ON THE REPORTS PAGE]
TYPE 2 - The following is for places that DO NOT have the name of the Country.
For example: “901 Starbit road, Towson, Baltimore county, Maryland”
[BEGIN COPY AFTER THIS LINE]
/* Remember to SET Max Search Results to a high number */ UPDATE tng_places SET zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 7 WHEN 1 Then 11 WHEN 2 Then 14 WHEN 3 Then 16 else 18 END, placelevel= CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 5 WHEN 1 Then 4 WHEN 2 Then 3 WHEN 3 Then 2 else 1 END WHERE placelevel = 0; /* Remember to RESET Max Search Results back to 50 */
[END COPY BEFORE THIS LINE]
[PASTE SELECTED CODE IN THE LAST BOX ON THE REPORTS PAGE]
Step 3 – Save and Run the Report.
Be sure to give it a meaningful name and description. Do NOT check the box for “Active”. This keeps the report private to the Administration area. Visitors will not be able to see it, when they view reports.
Select the “Save and Exit” button in your TNG Report.
This is the kind of report that you want to run solely as an administrator. Go to the Administration -> Reports part of your site. Each report has three buttons on the left side. They are (left to right) EDIT, DELETE and TEST. The rightmost green shaded TEST button is how you may run this report.
More than 50 Places to Update
TNG reports are set up to display values to the screen. It has a limit of 50 items to display at a time. If your report displayed more than 50 entries, the first 50 are displayed with a prompt to retrieve the next 50. TNG is not expecting the UPDATE command. All reports including your custom SQL report will be processed 50 at a time even though this report is not creating a display list. If you are updating more than 50 new places (placelevel=0) or if you want to update all of your places, you will need to update the “Max Search Results” parameter.
From the TNG Administration area select: SETUP -> GENERAL SETTINGS -> MISCELLANEOUS. Change the value of “Max Search Results” from 50 to some large number like 10000. Be sure to select the SAVE button at the bottom of the page. NOTE: This is temporary and will be reset. Run your report as outlined above and it will update up to the “Max Search Results” value. Go back to MISCELLANEOUS and change “Max Search Results” back to 50. If you don’t change it, your other display reports will be very long.
The code snippets have /* comment fields */ that remind you to set and reset "Max Search Results".
Updating ALL Place and Zoom Levels.
These are the same pieces of code but without the WHERE clause at the end.
TYPE 1 - The following is for places that include the name of the Country.
For example: “901 Starbit road, Towson, Baltimore county, Maryland, USA”
[BEGIN COPY AFTER THIS LINE]
/* Remember to SET Max Search Results to a high number */ UPDATE tng_places SET zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 5 WHEN 1 Then 7 WHEN 2 Then 11 WHEN 3 Then 14 WHEN 4 Then 16 else 18 END, placelevel= CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 6 WHEN 1 Then 5 WHEN 2 Then 4 WHEN 3 Then 3 WHEN 4 Then 2 else 1 END; /* Remember to RESET Max Search Results back to 50 */
[END COPY BEFORE THIS LINE]
[PASTE SELECTED CODE IN THE LAST BOX ON THE REPORTS PAGE]
TYPE 2 - The following is for places that DO NOT have the name of the Country
For example: “901 Starbit road, Towson, Baltimore county, Maryland”.
[BEGIN COPY AFTER THIS LINE]
/* Remember to SET Max Search Results to a high number */ UPDATE tng_places SET zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 7 WHEN 1 Then 11 WHEN 2 Then 14 WHEN 3 Then 16 else 18 END, placelevel= CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 5 WHEN 1 Then 4 WHEN 2 Then 3 WHEN 3 Then 2 else 1 END; /* Remember to RESET Max Search Results back to 50 */
[END COPY BEFORE THIS LINE]
[PASTE SELECTED CODE IN THE LAST BOX ON THE REPORTS PAGE]
Updating ALL Place and Zoom Levels For Only One Tree.
The WHERE clause contains the name of a single tree ID. Place tables call this field "gedcom". To find your tree ID go to the administration area and select Trees. The second column labeled "ID" has the Tree ID.
TYPE 1 - The following is for places that include the name of the Country.
For example: “901 Starbit road, Towson, Baltimore county, Maryland, USA”
[BEGIN COPY AFTER THIS LINE]
/* Remember to SET Max Search Results to a high number */ UPDATE tng_places SET zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 5 WHEN 1 Then 7 WHEN 2 Then 11 WHEN 3 Then 14 WHEN 4 Then 16 else 18 WHERE gedcom = 'YourTreeID' END, placelevel= CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 6 WHEN 1 Then 5 WHEN 2 Then 4 WHEN 3 Then 3 WHEN 4 Then 2 else 1 WHERE gedcom = 'YourTreeID' END; /* Remember to RESET Max Search Results back to 50 */
[END COPY BEFORE THIS LINE]
[PASTE SELECTED CODE IN THE LAST BOX ON THE REPORTS PAGE]
Find the two (2) lines with gedcom = 'YourTreeID' and substitute 'YourTreeID' with the name of your actual tree ID. The tree ID is to be surrounded by single quotes. Be mindful of case sensitivity.
TYPE 2 - The following is for places that DO NOT have the name of the Country
For example: “901 Starbit road, Towson, Baltimore county, Maryland”.
[BEGIN COPY AFTER THIS LINE]
/* Remember to SET Max Search Results to a high number */ UPDATE tng_places SET zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 7 WHEN 1 Then 11 WHEN 2 Then 14 WHEN 3 Then 16 else 18 WHERE gedcom = 'YourTreeID' END, placelevel= CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 5 WHEN 1 Then 4 WHEN 2 Then 3 WHEN 3 Then 2 else 1 WHERE gedcom = 'YourTreeID' END; /* Remember to RESET Max Search Results back to 50 */
[END COPY BEFORE THIS LINE]
[PASTE SELECTED CODE IN THE LAST BOX ON THE REPORTS PAGE]
Find the two (2) lines with gedcom = 'YourTreeID' and substitute 'YourTreeID' with the name of your actual tree ID. The tree ID is to be surrounded by single quotes. Be mindful of case sensitivity.
Adding Custom Values to the Place / Zoom Level
When TNG does batch geocoding, it sets placelevel = 0 and zoom = 10. A Place Level of zero (0) means that it is not set. The report examples replace the Place and Zoom fields in the tng_places table. The replacement values are based on the number of commas present in the place field.
The code snippets are preloaded with suggested Zoom levels for each Place. You may choose your own Zoom levels. Change the values in the reports to reflect your needs.
Preloaded values for places that include the name of the country:
Commas | Place (1-6) | Zoom (1-20) | |
---|---|---|---|
5 or more | 1 | 18 | Institution |
4 | 2 | 16 | Street Address |
3 | 3 | 14 | City / Town |
2 | 4 | 11 | County |
1 | 5 | 7 | State |
0 | 6 | 5 | Country |
Preloaded values for places that DO NOT include the name of the country:
Commas | Place (1-6) | Zoom (1-20) | |
---|---|---|---|
4 or more | 1 | 18 | Institution |
3 | 2 | 16 | Street Address |
2 | 3 | 14 | City / Town |
1 | 4 | 11 | County |
0 | 5 | 7 | State |
Applying user defined Zoom levels
The Code has a statement:
SET
zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', )))'
Following that statement are a number of conditions of the form "WHEN 0 THEN 5". The value after WHEN is the comma count and the value after THEN is the Zoom level.
If you wanted to change the Zoom levels for place fields that are Type 1 (with the name of a Country) and you only wanted to change the County (Place level = 4) from a zoom level of 11 to a Zoom level of 9, Change the line that has "WHEN 2 Then 11" to "WHEN 2 Then 9"
UPDATE tng_places SET zoom = CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 5 WHEN 1 Then 7 WHEN 2 Then 11 C H A N G E T H I S L I N E T O --> WHEN 2 Then 9 WHEN 3 Then 14 WHEN 4 Then 16 else 18 END, placelevel= CASE (LENGTH(place) - LENGTH(REPLACE(place, ',', ''))) WHEN 0 Then 6 WHEN 1 Then 5 WHEN 2 Then 4 WHEN 3 Then 3 WHEN 4 Then 2 else 1 END;
Use the Preloaded Values Tables as a guide. Experiment with Zoom levels and find the ones that suit your needs.
Place levels can be left alone.
Acknowledgments
- Original SQL - Stephen Conner
Related Links
See also Maintenance - Places - Zoom & Place Levels on how to create a report of Places with no Zoom and Place Levels that also can be used