WalesHER GAT User Manual
Contents
1. 2098 FH GIS ArcMap Style A Asset m a Photo Catalogue Corrugated_test csv TAFA upload the data to m External Media Ls Database herwales_gat Ra Earth Maps z E tii A A i and us Create or Add to Table create permanent table x i System Status ai e Table Name a p Chose create permanent table J Load Data Land Us F Conditid Load Data Date Format Auto FoxPro F af Threats First Line As Column Namesiy csv E E E Risks Delimiting Character XML FA poner gq Enclosing Character Enter the table name NB t DBF A Ances Convert Datetimesto Date p prefixing with aa will place the E amp F Compare and Append Tables 7 GB images Perform Auto Inserts A table at the top of the list in the gf Compare Tables Values T Bibliography database making it easy to g amp Apply Change List 7 amp DAT GAT sources find NB Always use Test Character Set E GGAT CPAT sources underscores not spaces TA Administrator Menu O Associated Leave all other options to their Liy iG Quotam Infa AA Uictarir Cua default settings AS July 2013 Click on the green tick to upload the file Navigate to the Administrator menu in the main panel and click on Database Admin 3 3 0 to open phpMyAdmin In phpMyAdmin click on the database that you uploaded the csv file to from the list of database on the left hand side of the screen Next check t2. GAT now GAT GAT CORE FROM aa_corrugated_temp WHERE east gt 100000 and nrth gt 100000 The WHERE clause in this SQL is looking for eastings and northings that have values greater than 100000 in order to ensure that the locations data is a valid Ensure that the SQL is looking to the correct columns in the uploaded dataset for its data After you have backed up geo_locations and amended the SQL as appropriate to suit your dataset run the SQL in phpMyAdmin New data from your uploaded table should now appear in geo_locations If essential geo_locations data is missing from your uploaded dataset such as then simply run a geo Spatial query to populate the blank columns Please refer to the WalesHER GIS Mapper document for instructions on how to do this The following geo locations columns can currently be auto populated in this way community postcode Ipa and unitary 4 8 Migrating Data into her_management_record The SQL below will insert data from selected columns in a table to her_management_record AS July 2013 INSERT INTO her_management_record wat prn watprn category rec_by rec_year compiler compilero compiledon origin copyright eventprn SELECT GAT prn concat GAT prn category Batten A 2011 Angharad Stockwell GAT now GAT GAT 44367 FROM aa_corrugated temp Ensure that the SQL is looking to the correct columns in the uploaded dataset for its data After you have backed up her_man
3. Table g 30 gt gt gt z mode and repeat headers after 100 cells kod wat prn name form4 ngr east nrth community broadclass GAT 33317 Boat Store Building SH3166528110 231665 328110 Maritime Abersoch Roofed GAT 33318 Garage Building SH3137028150 231370 328150 Commercial Abersoch Roofed GAT 33319 Garages Building S Table Contents B115 Transport Abersoch Roofed GAT 33320 Agricultural Building SH7881571720 278815 371720 Agriculture Buildings Taly and Cafn Roofed Subsistence GAT 33321 Agricultural Building SH8084569000 280845 369000 Agriculture Buildings and South of Roofed Subsistence of a new table uploaded to WalesHER in phpMyAdmin For column names to remove odd characters or spaces between column names To do this click on the table you have uploaded go to the Structure tab and click on the Change tool on the column that you want to edit as shown below AS July 2013 phpiyAdmnin j locathost p herwales_gat p jj aatemp WBrowse E Structure Asal J Search Feinsert MHExport import Operations Empty X Drop ASRR Field Type Collation Attributes Null Default Extra Action Database rowid bigint 4 UNSIGNED No None auto_increment 2 X FF W0 _gat 132 d wat varchar 5 latini_swedish_ci Yes NULL T xXxa D herwales_gat 132 pu int 11 Yes NULL M aR BG 5 name varchar 55 _latint_swedish_ci Yes NULL m7 xam m x form4 varchar 19 s latin _swedish_c Yes NULL T a x T a SOT ee
4. line of the SQL points to the table and table columns in her_core that you want to insert data into the column names must match those in her_core The second line of the SQL points to the table and table columns that you want to draw the data from The order in which the column names appear in the first and second lines of the SQL must match For example in the SQL above the column wat in the her_core table will be populated with the AS July 2013 values contained in the column GAT from the aa_corrugated_temp table because the column wat is looking to the column GAT for the data Where the value will be the same for each record in a column it is possible to specify that value in the SQL This applies to all instances in line two where text values are enclosed in apostrophes or a number has been specified numbers do not need apostrophes For example in the SQL above the compiler column in her_core will always be populated with the value Angharad Stockwell and the restricted column will always be populated with the value 0 in each record The following section in line two of the SQL above is asking WalesHER to insert Batten 2011 after the last line of text in the desc_1 field in order to link the description to its source using the Harvard referencing system concat description Batten 2011 After you have backed up her_core amend the SQL as appropriate to suit your dataset and run in p
5. looking to the correct columns in the uploaded dataset for its data AS July 2013 After you have backed up her_core_site_type and amended the SQL as appropriate to suit your dataset run the SQL in phpMyAdmin New data from your uploaded table should now appear in her_core_site_type 4 5 Migrating Data into her_source1_link The SQL below will insert data from selected columns in a table to her_source1_link INSERT INTO her_sourcel_link wat prn watprn source_id compiler compilero compiledon origin copyright SELECT GAT prn concat GAT prn 61822 Angharad Stockwell GAT now GAT GAT FROM aa_corrugated_temp This SQL will link a bibliographic record in the her_source1 table to multiple PRNs To do this the bibliographic record must already be present in her_source1 Make a note of the bibliographic record s row ID this can be found in the rowid column in the her_source1 table in phpMyAdmin or in the rowid field in the Admin tab in the panel view as shown below Source soog Details Rec info Admin Compiler Angharad Stockwell x lhe at Compiled on 25 07 2013 Project ID Project name Copyright GAT Origin GAT Oasis rowid 618 Enter the source s row ID to the second line of the SQL to correspond with source_id in the first line Ensure that the SQL is looking to the correct columns in the uploaded dataset for its data After you have backed up her_source1_link and amended
6. the SQL as appropriate to suit your dataset run the SQL in phpMyAdmin New data from your uploaded table should now appear in her_source1_link 4 6 Migrating Data into her_status The SQL below will insert data from selected columns in a table to her_status AS July 2013 INSERT INTO her_status wat prn watprn status status_typ grade reference compiler compilero compiledon origin copyright SELECT GAT prn concat GAT prn status status type status grade status ref Angharad Stockwell GAT now GAT GAT FROM aa_corrugated temp WHERE length trim status type gt 0 The WHERE clause in this SQL is looking for values greater than O therefore the status table will only be populated for records with status information attached to them Ensure that the SQL is looking to the correct columns in the uploaded dataset for its data After you have backed up her_status and amended the SQL as appropriate to suit your dataset run the SQL in phpMyAdmin New data from your uploaded table should now appear in her_status 4 7 Migrating Data into geo_locations The SQL below will insert data from selected columns in a table to geo_locations INSERT INTO geo_locations geo get wat orn watprn ngr east nrth community unitary compiler compilero compiledon origin copyright r ec_type SELECT geomFromWKB point east nrth 0 GAT prn concat GAT prn ngr east nrth community unitary Angharad Stockwell
7. AS July 2013 WalesHER GAT User Manual Automated Data Upload User Levels 0 amp 1 This document provides guidance on uploading datasets in csv format to WalesHER using the Load Data tool and migration SQLs in phpMyAdmin 3 3 0 This guidance is for HER managers and other high level HER users only user level 0 1 as it requires access to phpMyAdmin 1 Preparing Datasets for Upload 1 1 Converting to csv Format 1 2 Column Names 2 Using the Load Data Tool 3 Editing Uploaded Datasets 4 Running Migration SQLs in phoMyAdmin 3 3 0 4 1 Backing up Tables 4 2 Migrating Data into her_core 4 3 Migrating Data into her_core_event_link 4 4 Migrating Data into her_core_site_type 4 5 Migrating Data into her_source1_link 4 6 Migrating Data into her_status 4 7 Migrating Data into geo_locations 4 8 Migrating Data into her_management_record 4 9 Migrating Data into her_condition AS July 2013 1 Preparing Datasets for Upload The Load Data tool will recognise data in the following formats FoxPro csv xml and dbf This guide recommends using csv format as it is the only format that will not truncate large memo fields which can affect the her_desci field in particular 1 1 Converting to csv Format Datasets deposited with the HER in xls or mdb format Microsoft Excel Access will need to be converted to csv format To convert an xsl file to csv double click on the file to open in Excel go to File and Save As and chose CS
8. E Decta geo oe kns ngr varchar 14 latini_swedish_ci Yes NULL a xa OG ER backa her core eveni Ink east int 11 Yes NULL a xmp management_rec MB backs her ste type nrth int 11 Yes NULL ZX RBA Pesce etree mn community varchar 50 latini_swedish_ci Yes NULL i L x v 72 F E backs_her_status prema broadclass varchar 32 latin_swedish_ci Yes NULL S TYxXK Bh FY fare gerne unitary varchar 10 latint_swedish_ci Yes NULL aS X y o iy F type varchar 23 latin _swedish_ci Yes NULL T27 xa ur BB cds core_ sie type period period varchar 15 latin _swedish_ci Yes NULL E aes A ee E a em atademit ar cds _enq_agri_scheme Shown on OS map int 11 Yes NULL SFA e e E aa C description text latint_swedish_ci Yes NULL m 24x TU B E cds_enq_country wea Status type varchar 6 latint_swedish_ci Yes NULL T r T amp E c s_cnq_genera acne Status varchar 17 latint_swedish_ci Yes NULL t 49 age o amp p T Seep Status grade varchar 5 _latint_swedish_ci Yes NULL T Xx Owe amp E casom Status ref int 11 Yes NULI Sfx BoB E ccs_pa aan r E cas T ae yaa e ION anai condition varchar 50 latini_swedish_ci Yes NULL GFX HOB SR cds_pre74county W cas_presscounty Category varchar 3 latint_swedrsh_ci Yes NULL E xun mpe s_preSocisirat iarann Corrugated theme varchar 15 _latint_swedish_ci Yes NULL GFJZFxXxXBBea amp This will take you to the edit page as shown below g localhost gt herwales_gat gt aatemp Field St
9. Ls shown below will also do this automatically 4 1 Backing up Tables Once you have uploaded your dataset to WalesHER using the steps above you are ready to migrate the data it contains to the relevant tables in the WalesHER databases it is very important to back up the tables you want to migrate data into before running the migration SQLs This will ensure that errors can be quickly rectified by restoring backup tables To back up tables in WalesHER follow the steps for copying tables provided in the WalesHER Moving amp Copying Tables document NB Prefixing the backup table names with backup will ensure that they appear together in the list of tables in phpMyAdmin making them easier to find when restoring or deleting them later on e g backup_her_core NB It is a good idea to keep the backup tables for at least a week after completing any data migration to allow for migration errors to be identified To restore a backup table simply delete the table that has been amended and rename the backup table e g backup_her_core becomes her_core 4 2 Migrating Data into her_core The SQL below will insert data from selected columns in a table to her_core INSERT INTO her_core wat prn watprn name desc_1 compiler compilero compiledon origin copyright restricted SELECT GAT prn concat GAT prn name concat description Batten 2011 Angharad Stockwell GAT now GAT GAT O FROM aa_corrugated_temp The first
10. V MS DOS from the Save as type drop down list and click Save To convert a mdb file to csv you will need to export the data to Excel and then follow the steps as above To export data from an Access database 2010 to Excel double click on the file to open in Access click on the table you want to export go to External Data and chose Excel 1 2 Column Names The column names in the csv file do not need to match the column names used in the WalesHER database The migration SQLs see section 4 below will do this work for you However spaces in column names should be replaced with an underscore e g NGR qualifier should be changed to NGR_qualifier This can also be done once the csv file has been uploaded to WalesHER see section 3 below 2 Using the Load Data Tool Login to WalesHER and navigate to the Load Data menu in the main panel Click on CSV and the Load Data tool will appear as shown below Historic Environment Records Group GAT af Log Out N m EE If the GIS module is selected FE Query Builder Browser Reporter Google Earth Load map into the top data panel O 3 MS Virtual Earth User Manual Open tiled slave window for dual monitors or wide aspect screen a B PENENT E pi as Use the browse tool to find and m ocations F Pick Tables Event EP Dimensions upload the data Enquiries B F Dimensions simple E c s only VI Site Typ Load CSV
11. agement_record and amended the SQL as appropriate to suit your dataset run the SQL in phoMyAdmin New data from your uploaded table should now appear in her_management_record 4 9 Migrating Data into her_condition The SQL below will insert data from selected columns in a table to her_condition INSERT INTO her_ condition wat prn watprn record_by year condesc compiler compilero compiledon origin copyright eventprn SELECT GAT prn concat GAT prn Batten A 2011 management_observation Angharad Stockwell GAT now GAT GAT 44367 FROM aa_corrugated temp Ensure that the SQL is looking to the correct columns in the uploaded dataset for its data After you have backed up her_condition and amended the SQL as appropriate to suit your dataset run the SQL in phpMyAdmin New data from your uploaded table should now appear in her_condition
12. atus ref Type INT Length Values 11 Default NULL m Collation x Attributes X Null 7 AUTO_INCREMENT Comments MIME type v Browser transformation lt Transformation options Edit the fields as appropriate and click Save to save your changes To abandon any edits simply click the back button in your browser or navigate to another table or database 4 Running Migration SQLs in phpMyAdmin 3 3 0 The migration SQLs are a pre prepared set of SQLs designed to move datasets from within one table to another in WalesHER quickly and efficiently The SQLs are designed to be altered and tailored to suit different datasets by the HER manager Use the SQLs below as a basis to create tailored SQLs to fit the datasets that you want to upload with the tables in WalesHER NB Take care to ensure that the restricted field is populated appropriately 1 restricted 0 unrestricted All core finds records and all metal detecting event records must be flagged as restricted AS July 2013 NB The following columns should be populated with the following values where they occur in every table wat GAT compiler Name of the person entering the record to the HER e g Angharad Stockwell compiler GAT origin GAT copyright GAT NB The compiledon column in all tables must always show the date on which the record is added to the HER This is auto filled when adding records manually in the panel view The now clause in the SQ
13. hat the data has been uploaded correctly by finding the table in the list if the table name is prefixed by aa then it will appear at the top of the list clicking on it and browsing the table as shown below A phpMyAdmin g3 localhost p herwales_gat p j aatemp li Browse g Structure SQL Search lnsert Export import Operations Empty Drop AER Showing rows 0 29 71 total Query took 0 0172 sec Database SELECT ERO m en Database _ Database Profiling Edit Explain herwales_gat 132 B aatemp in backs_geo_ locations hiama B backs_her_condition Sort by key None B backs_her_core_event_link backs_her_management_record Options B backs_her_site_type T rowid E backs_her_source1_link A backs_her_status cds_arte_material cds_arte_type A P x 1 cds_arte_typequal cds_community cds_condition_condition cds_condition_rating cds_core_site_type_period cds_enq_academic cds_enq_agri_scheme cds_eng_bub_body hf X 2 cds_eng_commercial cds_enq_country cds_enq_general cds_enq_location cds_eng_method cds_eng_purpose 7 cds_enq_region n se 3 cds_form E cds_pre96district E cds_risk_level E cds_risk_vulnerability E cds_source_type B cds_status A P x 5 l cds_threat_signif cds_threat_timescale 3 Editing Uploaded Datasets It is possible to edit the structure example you may want to change X row s starting from record Page number 1 L
14. hpMyAdmin New data from your uploaded table should now appear in her_core 4 3 Migrating Data into her_core_event_link The SQL below will insert data from selected columns in a table to her_core_even_link INSERT INTO her_core_event_link wat watprn ewatprn compiler compilero compiledon origin copyright SELECT GAT concat GAT orn concat GAT 44367 Angharad Stockwell GAT now GAT GAT FROM aa_corrugated_temp This SQL will link an event record in the her_event table to multiple core PRNs To do this the event record must already be present in her_event Make a note of the event PRN that you want to link to your dataset Enter the event PRN to the second line of the SQL as shown above Ensure that the SQL is looking to the correct columns in the uploaded dataset for its data After you have backed up her_core_event_link and amended the SQL as appropriate to suit your dataset run the SQL in phpMyAdmin New data from your uploaded table should now appear in her_core_event_link 4 4 Migrating Data into her_core_site_type The SQL below will insert data from selected columns in a table to her_core_site_type INSERT INTO her_core site type wat prn watprn broadclass type period compiler compilero compiledon origin copyright eventprn rank SELECT GAT prn concat GAT prn broadclass type period Angharad Stockwell GAT now GAT GAT 44367 1 FROM aa_corrugated_temp Ensure that the SQL is
Download Pdf Manuals
Related Search