-- Ucmp_loc2 table - Elib database -- 10/3/2005 G.O. CREATE TABLE ucmp_loc2 ( # ID numbers l_seq_num serial, ucmp_coll char(4), # P,I,M,V (up to 4) loc_ID_num varchar(24) unique not null, # (loc_prefix.loc_num.loc_suffix) loc_num int unsigned not null, # loc_prefix varchar(4), # "PB" loc_suffix varchar(6), # "x" loc_name varchar(128), # "Chalk Bluffs General" other_loc_no varchar(255), # loc_archives varchar(255), # for storing Archon IDs. Added August 2011. jg # geopolitical cont_ocean varchar(64), # "Africa" country_std varchar(64), # "Myanmar" country_orig varchar(64), # "Burma" state_prov_std varchar(64), # "Kwangsi Province" state_prov_orig varchar(64), # "Kiangsi" island_group varchar(64), # "Hawaiian Islands" island varchar(64), # "Oahu" county_std varchar(64), # "Nevada County" county_orig varchar(64), # "Nevada" # geologic era_eon varchar(64), period varchar(64), epoch varchar(64), stage varchar(64), stage_local varchar(64), storage_age varchar(64), biozone varchar(128), flora_fauna varchar(64), grp varchar(64), formation varchar(64), formation_marine varchar(64), # added May 10 2018: marine, non-marine, marine + non-marine member varchar(64), lithology varchar(128), habitat varchar(128), # spatial map_name varchar(64), # "Santa Susana Quad1903" map_scale varchar(16), # "24000" map_source varchar(64), # "USGS" map_edition varchar(32), # "1987" elev_orig_units varchar(16), # "feet" or "meters" elev_min_ft decimal(12,4), elev_max_ft decimal(12,4), elev_var_ft decimal(12,4), # variance elev_min_m decimal(12,4), elev_max_m decimal(12,4), elev_var_m decimal(12,4), depth_orig_units varchar(16), # "feet" or "meters" or "fathoms" depth_min_fthm decimal(12,4), depth_max_fthm decimal(12,4), depth_var_fthm decimal(12,4), depth_min_m decimal(12,4), depth_max_m decimal(12,4), depth_var_m decimal(12,4), depth_min_ft decimal(12,4), depth_max_ft decimal(12,4), depth_error_ft decimal(12,4), bore_depth_m varchar(16), # NOTE: Ken has ranges in these fields - leave as text bore_depth_ft varchar(16), lat_deg int(3) unsigned, lat_min int(3) unsigned, lat_sec decimal(5,2), lat_dir char(1), long_deg int(3) unsigned, long_min int(3) unsigned, long_sec decimal(5,2), long_dir char(1), lat_decimal decimal(12,8), long_decimal decimal(12,8), georeferencedBy varchar(255); georeferencedDate date, georeferenceProtocol text, georeferenceSources varchar(255), georeferenceVerificationStatus varchar(255), georeferenceRemarks text, fuzzy_lat decimal(12,8), fuzzy_long decimal(12,8), ll_variance_orig varchar(64), # "25' +- 1'" ll_variance_m integer, # changed from decimal(12,8) on Dec 12, 2016 datum varchar(50), maxerrorinmeters decimal(10,4), # as of May 2013 not in use - see variance township varchar(16), #changed June 2013 from char(4) town_dir char(1), #range char(4), # As of Mar 2013 (and maybe since long ago), range is a reserved word in mysql. # If query this field, use backticks: select `range` where `range` ... range varchar(16), #changed June 2013 from char(4) range_dir char(1), section varchar(16), #changed June 2013 from char(6) subdiv varchar(64), # collecting event loc_citation text, collector varchar(255), # can include dates coll_date varchar(64), landowner varchar(128), # parks ex: "Channel Islands National Park (NPS)" field_notes varchar(255), field_numbers varchar(255), accn_no varchar(32), loc_description text, # administrative rack varchar(32), # incr. from 16 7/21/2006 bay varchar(32), # incr. from 16 7/21/2006 loc_index_date datetime, # date record added to the database 2005-11-20 21:49:27 last_modified_date datetime, last_modified_by varchar(64), mod_history varchar(255), creation_date datetime, created_by varchar(64), bulk char(3), # yes, no, null washed char(3), slides char(3), remarks varchar(255), # collection managers remarks db_notes varchar(255), # batchID ex: "batchID2013-12-09_17:13:37" loc_pic varchar(19), # CalPhotos ID access varchar(24), # yes/no loc_namesoup text, # added Aug 16, 2011 other_storage_loc varchar(255), # added May 3, 2013 for Caltrans project loc_count int unsigned, # control count for pollen samples added 5/7/2013 project varchar(255), # source/project for Caltrans added 5/7/2013 tmp1 varchar(255), # P & V: Supplemental file (old DB field) tmp2 varchar(255), # P & V: Photo Coverage (old DB field) abs_age decimal (8,3), # absolute/ calibrated age added 5/10/2013 abs_age_diff decimal (8,3), # +- for absolute age abs_age_units char(3), # Ka,Ma,Ga,YBP (thous,mil,bil,calibrated years) abs_age_method varchar(32), # CV: radiocarbon, argon-argon, etc. batch_id ); create index ul2_ucmp_coll on ucmp_loc2 (ucmp_coll); create index ul2_loc_num on ucmp_loc2 (loc_num); create index ul2_loc_ID_num on ucmp_loc2 (loc_ID_num); create index ul2_loc_prefix on ucmp_loc2 (loc_prefix); create index ul2_loc_name on ucmp_loc2 (loc_name); create index ul2_other_loc_no on ucmp_loc2 (other_loc_no); create fulltext index loc_description on ucmp_loc2 (loc_description); create index ul2_cont_ocean on ucmp_loc2 (cont_ocean); create index ul2_country_std on ucmp_loc2 (country_std); create index ul2_country_orig on ucmp_loc2 (country_orig); create index ul2_creation_date on ucmp_loc2 (creation_date); create index ul2_state_prov_std on ucmp_loc2 (state_prov_std); create index ul2_state_prov_orig on ucmp_loc2 (state_prov_orig); create index ul2_county_std on ucmp_loc2 (county_std); create index ul2_county_orig on ucmp_loc2 (county_orig); create index ul2_lat_deg on ucmp_loc2 (lat_deg); create index ul2_long_deg on ucmp_loc2 (long_deg); create index ul2_lat_decimal on ucmp_loc2 (lat_decimal); create index ul2_long_decimal on ucmp_loc2 (long_decimal); create index ul2_era_eon on ucmp_loc2 (era_eon); create index ul2_period on ucmp_loc2 (period); create index ul2_epoch on ucmp_loc2 (epoch); create index ul2_stage on ucmp_loc2 (stage); create index ul2_storage_age on ucmp_loc2 (storage_age); create index ul2_biozone on ucmp_loc2 (biozone); create index ul2_flora_fauna on ucmp_loc2 (flora_fauna); create index ul2_grp on ucmp_loc2 (grp); create index ul2_formation on ucmp_loc2 (formation); create index ul2_formation_marine on ucmp_loc2 (formation_marine); create index ul2_member on ucmp_loc2 (member); create index ul2_lithology on ucmp_loc2 (lithology); create index ul2_habitat on ucmp_loc2 (habitat); create fulltext index ul2_loc_citation on ucmp_loc2 (loc_citation); create index ul2_collector on ucmp_loc2 (collector); create index ul2_field_notes on ucmp_loc2 (field_notes); create index ul2_field_numbers on ucmp_loc2 (field_numbers); create index ul2_accn_no on ucmp_loc2 (accn_no); create index ul2_remarks on ucmp_loc2 (remarks); # 5 min 41.45 sec create fulltext index ul2_loc_namesoup on ucmp_loc2 (loc_namesoup);