FlicAnderson

SELECT 'F-' & Fiel.id AS recID, Fiel.Expedition AS expdID, Team.[name for display] AS collector, Fiel.[Collector Number] AS collNumFull, LnSy.id AS lnamID, LnSy.[Full Name] AS acceptDetAs, LnSy.sortName AS acceptDetNoAuth, Lnam.[Full Name] AS detAs, Fiel.[Latitude 1 Direction] AS lat1Dir, Fiel.[Latitude 1 Degrees] AS lat1Deg, Fiel.[Latitude 1 Minutes] AS lat1Min, Fiel.[Latitude 1 Seconds] AS lat1Sec, Fiel.[Latitude 1 Decimal] AS lat1Dec, IIf(IsNull(Fiel.[Latitude 1 Decimal]),Geog.[Latitude 1 Decimal],Fiel.[Latitude 1 Decimal]) AS anyLat, Fiel.[Longitude 1 Direction] AS lon1Dir, Fiel.[Longitude 1 Degrees] AS lon1Deg, Fiel.[Longitude 1 Minutes] AS lon1Min, Fiel.[Longitude 1 Seconds] AS lon1Sec, Fiel.[Longitude 1 Decimal] AS lon1Dec, IIf(IsNull(Fiel.[Longitude 1 Decimal]),Geog.[Longitude 1 Decimal],Fiel.[Longitude 1 Decimal]) AS anyLon, Fiel.coordinateSource AS coordSource, IIf(IsNull(Fiel.[Latitude 1 Decimal]),'Gazetteer','Record') AS coordSourcePlus, Fiel.coordinateAccuracy AS coordAccuracy, Fiel.coordinateAcc

FlicAnderson

CREATE TABLE FieldRexTemp
(
id AUTOINCREMENT PRIMARY KEY , 
recID TEXT(10), 
expdID TEXT, 
collector TEXT,
collNumFull TEXT, 
lnamID INTEGER, 
acceptDetAs TEXT,
acceptDetNoAuth TEXT, 

FlicAnderson

FlicAnderson

# open test connection
source("O://CMEP\ Projects/Scriptbox/database_connections/function_TESTPadmeArabiaCon.R")
TESTPadmeArabiaCon()

# get column details for FieldRexTemp table
tableDetails <- sqlColumns(con_TESTPadmeArabia, sqtable="FieldRexTemp", errors = FALSE, as.is = TRUE,
           special = FALSE, catalog = NULL, schema = NULL,
           literal = FALSE)

# show relevant columns

FlicAnderson

INSERT INTO FieldRexTemp ( recID, expdID, collector, collNumFull, lnamID, acceptDetAs, acceptDetNoAuth, detAs, lat1Dir, lat1Deg, lat1Min, lat1Sec, lat1Dec, AnyLat, lon1Dir, lon1Deg, lon1Min, lon1Sec, lon1Dec, AnyLon, coordSource, coordSourcePlus, coordAccuracy, coordAccuracyUnits, dateDD, dateMM, dateYYYY, fullLocation )
SELECT 
'F-' & Fiel.id AS recID, 
Fiel.Expedition AS expdID, 
Team.[name for display] AS collector, 
Fiel.[Collector Number] AS collNumFull, 
LnSy.id AS lnamID, 
LnSy.[Full Name] AS acceptDetAs, 
LnSy.sortName AS acceptDetNoAuth, 
Lnam.[Full Name] AS detAs, 

FlicAnderson

library(leaflet)
library(stringi)
library(htmltools)
library(RColorBrewer)

# get tracks
danny <- readLines("http://weather.unisys.com/hurricane/atlantic/2015/DANNY/track.dat")

# extract data
danny_dat <- read.table(textConnection(gsub("TROPICAL ", "TROPICAL_", danny[3:length(danny)])), 

FlicAnderson

FlicAnderson

FlicAnderson

SELECT Herb.[Collector Number], Lnam.[Full Name], LnSy.[Full Name] AS accepted, Geography.fullName, Team.Name
FROM (((((Determinations AS Dets RIGHT JOIN [Herbarium specimens] AS Herb ON Dets.[specimen key] = Herb.id) LEFT JOIN [Latin Names] AS Lnam ON Dets.[latin name key] = Lnam.id) LEFT JOIN [Synonyms tree] AS Synm ON Lnam.id = Synm.member) LEFT JOIN [Latin Names] AS LnSy ON Synm.[member of] = LnSy.id) LEFT JOIN Geography ON Herb.Locality = Geography.ID) LEFT JOIN Teams AS Team ON Herb.[Collector Key] = Team.id
WHERE ((Dets.Current)=True) AND ((LnSy.[Synonym of]) Is Null)
ORDER BY Herb.[Collector Number];

FlicAnderson

# How to run BFG to slim large repos: howToBFGrepo.sh
# ====================================================
# (20th November 2014)
# Author: Flic Anderson
# Written using Git Bash  v1.9.2; Windows 7; BFG Repo-Cleaner 1.11.8
# ~ standalone script to use in bash

# AIM: To remove stupidly large files from a repo that you've had in there for some time,
# .... figure out whether they are all over a certain size (eg. 500K) and that they are ALL deletable.
# .... delete what you can, and commit each deletion so it's recorded.