Mass Updates of Place Level and Zoom

From TNG_Wiki
Jump to: navigation, search

The following procedure describes steps that you can use to mass update the Place Level and Zoom for Places where the latitude and longitude where imported through a gedcom created by a desktop program like Legacy Family Tree 7 or RootsMagic 4.

Since Place Level and Zoom are TNG specific information for the Google Maps display, after importing the LATI and LONG into TNG, you may want to use the following approach to update your place level and zoom values for Places where latitude and longitude were assigned.

This procedure works for Legacy 7 imports in TNG 7.0 and also apply to RootsMagic 4 in TNG 7.1

SQL Updates

The two following SQL Updates can be made in using phpMyAdmin. First select the appropriate database and then select the tng_places table and click on the SQL tab and enter the following SQL statements in the SQL input field.

Set default Placelevel

The following SQL statement will set the default placelevel column to 3 which represents Town/City on the basis that the majority of your locations are Towns/Cities

UPDATE tng_places
SET placelevel = 3
WHERE longitude != '0'
AND placelevel = 0
LIMIT 1000 

Set default Zoom

The following SQL statement will set the default zoom column to 10

UPDATE tng_places
SET zoom = 10
WHERE longitude != '0'
AND zoom =0
LIMIT 1000 

Note that you can change the values for the mass update as you feel appropriate.

Export Excel Spreadsheet

After making the mass updates, you can export a Microsoft Excel spreadsheet of the tng_places table using phpMyAdmin:

  • Select the Export tab.
  • Select the Microsoft Excel 2000 under the Export column, .
  • Check the box for Put fields names in the first row under the Options column.
  • Check the box for Save as file.
  • Click the Go button.
  • Save the File on your computer.

Update Spreadsheet

On you local computer, you can then update your spreadsheet for Place Level and Zoom using the following approach or a similar one for setting the zoom and place level values:

  • zoom = 11 for place level = 2 (hospital/cemetery)
  • zoom = 10 for place level = 3 (town/city)
  • zoom = 7 or 8 for place level = 4 (county/shire/department)
  • zoom = 6 or 7 for place level = 5 (state/province/region)
  • zoom = 6 or 7 for place level = 6 (country)

Note that if you updated the default place level and zoom using the SQL update statements above then you only need to find the places which are not a Town/City

Save as csv file

When you are done updating your Excel spreadsheet, save it as a csv file:

  • Delete the heading row
  • Select the Save As option
  • Select the csv file type option

Import the csv file

Next using phpMyAdmin, import the csv file to update zoom and placelevel, after making a backup:

  • select your database
  • select the tng_places table
  • select the Empty tab to clear the table
  • select the Import tab
  • click the Browse button to find your updated csv file
  • Set the Character set of the file - normally latin1 or utf8
  • Set the Format of Import file to CSV using LOAD DATA
  • change Fields terminated by to use comma rather than semi-colon
  • Check the box in front of Replace table data with file
  • Click the Go button