Adjusting the Place and Zoom Levels for Google Maps Using Reports With
TNG (The Next Generation of Genealogy Sitebuilding)
Author: Stephen A Conner
Created: 15 June 2014
Last Edit: 13 October 2014
This document explains how to use the TNG Report feature to adjust the Zoom & Place levels on a TNG website. This is also outlined in the TNG wiki: http://tng.lythgoes.net/wiki/index.php?title=Mass_Updates_of_Place_and_Zoom_Levels_Using_Reports
1. INTRODUCTION
Many people publish their family histories online using Darrin Lythgoe’s The Next Generation of Genealogy Sitebuilding (TNG). Some have enabled the Google Maps feature for their sites. TNG will now add latitude and longitude (geocodes) for each place on the site. However, the Place Level remains at zero (0) and the Zoom Level is set to ten (10).
This documents presents code snippets that can be copied and pasted in the SQL area of a TNG report. When the report is selected, the Place and Zoom levels will be adjusted. It does the following:
1)Calculates the Place Level for each location based on the number of commas in the Place Field.
2)Allows the user to set the Zoom Level for each Place Level.
3)Selects places where the “placelevel” value = 0 (new places) and updates Place and Zoom levels. It is optional to adjust all existing places.
4)The user can elect to update all of their trees or a selected one
The user must be familiar with Darrin Lythgoe’s TNG. The user has set up a WEB site for the TNG software and is familiar with its administration.
Section 2 describes how to run the reports and Section 3 describes has background information.
2. PREPARATION AND EXECUTION
2.1 Disclaimer
This document illustrates how to update values on a TNG MySQL database using the Report feature. The statements in the report will update the tng_places table. We are not responsible, if these procedures are misused. If you are unsure about anything, then don't use it. Better yet, stop reading and don’t do anything.
2.2 Requirements
The user must have complete addresses stored in their tng_table “place field”. This means “Institution Name (optional), street address, City, County, State, Country”. For example the string “New Cathedral Cemetery, 4300 Old Frederick Road, Baltimore City, Maryland, USA” would be in the field “place” in tng_table. You can see how your places look by going to the TNG Administration area and select “Places”.
The user will need to know three things before proceeding.
1. The user will need to know the Tree ID values in their TNG site, if they want the Place and Zoom adjustments to affect only those trees. Use section 2.6.
However, if they want the adjustments to affect all the trees use section 2.5. The Tree ID is found on the TNG Administration area for TREES. Use the name in the column “ID”.
2. If the user has renamed the TNG database table “tng_places” as something else, then they need to have that name to proceed. Usually, no one changes these TNG settings. However, since TNG has that flexibility, there may be one person who may have done it. They would need to change each occurrence of tng_places with the name they created.
3. The user will have to decide on one of two code snippets to use. TYPE 1 is for those that used the name of the COUNTRY as part of the place field. TYPE 2 is for users that did not include the COUNTRY name in the place field.
TYPE 1 - 901 Starbit road, Towson, Baltimore county, Maryland, USA
TYPE 2 - 901 Starbit road, Towson, Baltimore county, Maryland
2.3 Back Up Your TNG Tables
1.Sign in to your TNG site as administrator.
2.Select UTILIES
3.Click the “Select All” button.
4.Next to “With selected:” is a pull down menu. Select “Back Up”.
5.Click the GO button.
There are THREE STEPS to update Place and Zoom Levels.
2.4 STEP 1 – Create a report in the administration area.
Log onto TNG and enter the administration are. 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.
The following code snippets maybe copied and then pasted in the special SQL box when creating a report. After you finish pasting the code, give it a meaningful title and save the report.
If you want to apply the updates to ALL of your trees, select Section 2.5
If you want to apply the updates to ONLY ONE of your trees, select Section 2.6
2.5 STEP 2 – Copy Code Snippet for All Trees.
Depending on whether or not you used the Country's name in your place field, select one of two TYPES of code.
2.5.1 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]
Proceed to section 2.7 - Step 3.
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]
Proceed to section 2.7 - Step 3.
2.6 Copy Code Snippet for ONLY One Tree (Step Two if you skipped section 2.5)
BE SURE TO CHANGE THE ‘YourTreeID’ to the Tree ID name in you TNG Trees Administration area. NOTE: The name may be case sensitive.
2.6.1 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) AND (gedcom = 'YourTreeID');
/* 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]
The tree ID field is called gedcom in the TNG table. Copy your tree ID precisely as it appears in Trees administration area.
These updates will affect ONLY one tree specified in the gedcom=’YourTreeID’ statement.
Proceed to section 2.7 - Step 3.
TYPE 2 - The following is for places that does NOT have the name of the Country.
For example: “901 Starbit road, Towson, Baltimore county, Maryland”
BE SURE TO CHANGE THE ‘YourTreeID’ to the Tree ID name in you TNG Trees Administration area. NOTE: The name may be case sensitive.
[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) AND (gedcom = 'YourTreeID');
/* 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]
The tree ID field is called gedcom in the TNG table. Copy your tree ID precisely as it appears in Trees administration area.
These updates will affect ONLY one tree specified in the gedcom=’YourTreeID’ statement.
Proceed to section 2.7 - Step 3.
2.7 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.
Click on 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.
2.7.1 More than 50 Places to Update.
The 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.
2.7.2 Change 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.
To get an idea of how many places you have, select PLACES from the administration area.
If you want to be more elegant, you could set your “Max Search Results” to the actual number of places before running the reports.
3. BACKGROUND
3.1 Place and Zoom Levels
TNG allows each place to have a Place Level (Street, City, County, State, etc.). This shows up as a push-pin with a color indicating the Level. Also, each place can have a starting Zoom level. When you click on the push-pin on the right side of the display, a map will appear at an appropriate Zoom level.
The SQL reports will calculate the appropriate Place and Zoom level for each location. The Place and Zoom levels are calculated based on the number of commas in the Place field. No commas indicate a State only. The user will be able to define the Zoom Level for no commas. Using the Country option, four commas may be a street address whereas for no Country present, three commas would be a street address.
This was inspired by Robert and Barry Reynolds at www.reynolds-lake.ca. A further discussion is at: http://tng.lythgoes.net/wiki/index.php?title=Managing_Latitude_and_Longitude
A general discussion of using the Google maps mod with TNG is at: http://tng.lythgoes.net/wiki/index.php?title=Google_maps_15.
3.2 Structured Query Language and how to use it.
SQL (pronounced Sequel) stands for Structured Query Language. It is a programming language for managing data in relational database management systems (RDBMS). Some tutorials are available at: http://www.w3schools.com/sql/default.asp and http://sqlzoo.net/.
SQL can be used on the databases for TNG sites. Use phpMyAdmin and access the TNG database. Select “QUERY” and the following dialog will appear.
There is a box where the user can enter an SQL statement. As an example we want to update "25 Bloomsbury Avenue, Catonsville, Baltimore County, Maryland, USA" and change the Place Level to 5 and the Zoom Level to 20.
This would update all the Trees that have that exact address. It would update the Place and Zoom Level even if it was already set.
If we were to use the same example and instead, we did not want to update Place and Zoom Levels for Places that already have a value in Place Level, the SQL would look as follows:
UPDATE tng_places SET zoom = 20, placelevel = 5 WHERE Place ="25 Bloomsbury Avenue, Catonsville, Baltimore County, Maryland, USA" AND placelevel = "0";
If we were to take the same example and only have it apply to a specific tree (OurWholeFamily), the SQL would look as follows:
UPDATE tng_places SET zoom = 20, placelevel = 5 WHERE Place ="25 Bloomsbury Avenue, Catonsville, Baltimore County, Maryland, USA" AND gedcom = "OurWholeFamily" AND placelevel = "0";
3.3 Adding Custom Values to the Place / Zoom Levels.
When TNG does a batch geocoding, it sets placelevel = 0 and zoom = 10. A Place Level of zero (0) means that it is not set.
The report example 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 snippets are preloaded with suggested Place / Zoom levels. You user may edit and 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 commas 1 18 -- Address
4 commas 2 16 -- Location
3 commas 3 14 -- City / Town
2 commas 4 11 -- County
1 commas 5 7 -- State
0 commas 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 commas 1 18 -- Address
3 commas 2 16 -- Location
2 commas 3 14 -- City / Town
1 commas 4 11 -- County
0 commas 5 7 -- State
3.4 Specifying a Single Tree
The report function acts on ALL trees on your site unless you used the snippet in section 2.6 and substituted the Tree ID (gedcom) of the tree you wish updated.
3.5 Replace ALL Place and Zoom Levels.
You may edit the snippets to replace existing Place and Zoom levels even if they already have values. Be sure to leave the semicolon (;). For example, change
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; Remove WHERE clause
To:
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;
(Note: Leave a semicolon (;) at the end)
3.6 The Missing County
Some cities and towns are incorporated or set up in a way that does not show a County. One example is Baltimore City. It is not part of any County. This affects the Zoom Level since an extra comma was not counted.
If you want the Zoom Level to reflect the same magnification for such a city as one that is part of a County, you could add an extra comma in the Place Field. For example “Baltimore City, Maryland, USA” could be entered as “Baltimore City, , Maryland, USA”. The Zoom level would be set at the proper magnification for a City. The extra commas do not seem to upset Google Maps.
However, if a city is large enough, do not bother with an extra comma. Treat the city as if it were viewed on a county level.
3.6.1 Run two Reports.
First, run the reports in sections 2.5 and 2.6. Those reports will update your current tng_places.
Then run this report. This report ONLY updates place and zoom levels for places that have the phrase “Baltimore City, Maryland”.
3.6.2 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 value */
UPDATE
tng_places
SET
zoom = CASE ((LENGTH(place) - LENGTH(REPLACE(place, ',', '')))+1)
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, ',', '')))+1)
WHEN 0 Then 6
WHEN 1 Then 5
WHEN 2 Then 4
WHEN 3 Then 3
WHEN 4 Then 2
else 1
END
WHERE
place LIKE '%Baltimore City, Maryland%';
/* 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 DOES 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 value */
UPDATE
tng_places
SET
zoom = CASE ((LENGTH(place) - LENGTH(REPLACE(place, ',', '')))+1)
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, ',', '')))+1)
WHEN 0 Then 5
WHEN 1 Then 4
WHEN 2 Then 3
WHEN 3 Then 2
else 1
END
WHERE
place LIKE '%Baltimore City, Maryland%';
/* 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]
If you want to apply changes to only one specific tree (TreeID = 'OurFamily') as outlined in section 2.6, add the following before the ending semicolon (;)
AND (gedcom = 'YourTreeID')
4. MANAGING PLACES
The following is outside of TNG SQL Reports and are suggestions for making your Google Maps experience more enjoyable.
4.1 Spelling Counts
Before Geocoding your TNG site, you can use TNG’s Places feature to resolve duplicate place names due to spelling variations or through the differences in Upper versus Lower Case characters. A place name is considered unique, if there is the slightest variation.
If you are using a personal genealogy program that exports its Gedcom to TNG, you will want to modify your place names from within that program. Consider deleting the old place names on TNG before uploading the improved place names.
4.2 Strange Places or “garbage in – garbage out”
Sometimes we enter places as we see them on a census document. For example, District 1, Cecil County, Maryland, USA is not an actual address that Google can decipher. It will create a push pin somewhere in Cecil County but that is all. You may have to change Enumeration Districts to a town or post office associated with them. In your citation you can specify how you got the place name.
Sometimes a place name is no longer used or an institution is demolished and you only have a street address. Sometimes the street address number changed for the same house. This happened in 1887 Baltimore. You might decide to enter the modern address so that the Maps API will precisely point to the spot. Be sure to include the old historic address in your notes.
If a town or province has undergone a name change, consider using the new name and reference the old name in your notes. If an institution has undergone a name change, you could try using the new name to see if it gets the coordinates that you want.
In some cases the street address is accurate but Google still misplaces the push pin. For example the address “New Cathedral Cemetery, 4300 Old Frederick Road, Baltimore City, Maryland, USA” is the accurate name of a cemetery. However, Google place a push pin in downtown Baltimore. If you change the name to “Cathedral Cemetery”, Google places the push pin accurately. The Google database is in error and needs to be updated.
Sometimes you have to set the geocodes by hand. Sections of a street may no longer exist and Google mishandles it. If someone was born, died or was married at sea. Google doesn’t know what to do with the Bay of Biscayne.
Google offers a utility to test an address. It is at the link location: http://gmaps-samples.googlecode.com/svn/trunk/geocoder/singlegeocode.html.
4.3 The Google Maps Feature Not Zooming In Close Enough
You may notice that even though the zoom settings on some of your places are set high, Google Maps is showing a terrain setting that is further away. This is easily changed in the Setup --> Configuration --> Map Settings of your TNG site. TNG may have defaulted to TERRAIN.
Next to MAP TYPE select either SATELLITE or HYBRID.
4.4 Nothing Is Perfect
Even after you try your best, not all of your place names will be precisely geocoded. The new automated method in version TNG v9 & v10 is better than entering each location by hand.
PLEASE NOTE
This guide may be copied for personal use. Further reproduction is prohibited without permission. Do not view or download from any site other than ConnerGenealogy.com. Other sites are not authorized and may contain computer viruses.
The SQL reports have been tested with MySQL database version 5.5.37.
This file and the reports it describes are the Copyright of Stephen A. Conner.
Google Maps API references are a trademark of Google Maps™ mapping service
phpMyAdmin http://www.phpmyadmin.net/home_page/index.php is released under GNU General Public License, version 2.
Robert and Barry Reynolds developed a Place Level SQL routine for Legacy GEDCOMS
TNG (The Next Generation of Genealogy Sitebuilding) is written by Darrin Lythgoe
Windows is a registered trademark of Microsoft Corporation in the United States and other countries.