Lin, Cheng-Tao mutolisp

## postgis1216.sql
SELECT
	id, ST_BUFFER(geom_centroid, 0.0083333) as geom_buff
FROM
(SELECT
   1 as id,
   ST_SRID(ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326)) as srid,
   st_centroid(ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326)) as geom_centroid,
   ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326) as geom)
AS s;


## create_test2.sql
DROP TABLE IF EXISTS test;
CREATE TABLE test (
	id serial,
	long double precision,
	lat double precision,
	country varchar,
	species varchar,
	geom geometry(POINT, 4326),
	CONSTRAINT pk_test PRIMARY KEY (id)
);

## 20201014_data_visualisation2.r
library(sp)
library(gstat)
library(raster)

graycol <- gray.colors(4, 0.55, 0.95)
# use IDW to generate zn.idw

data(meuse)
coordinates(meuse) <- c('x', 'y')
data(meuse.grid)

## dummy.csv

          
            mountain
            aspect
            species
            cover
            r

            
              SEN
              W11
              A
              30
              1

            
              SEN
              W11
              B
              10
              3

            
              SEN
              W11
              C
              0.5
              2

            
              SEN
              W13
              A
              15
              4

            
              SEN
              W13
              D
              5
              3

            
              SEN
              W13
              E
              3
              2

            
              SEN
              W13
              F
              0.1
              1

            
              SEN
              W13
              G
              10
              4

            
              SUN
              E11
              A
              15
              2

## twTransform.r
# install sp package before use
# read coordinates csv
twTransform <- function(input = file.choose(), x='x', y='y' ,from = 'wgs84', to= 'twd97'){
  require(sp)
  coords <- read.csv(input, sep = ',')
  coords <- cbind(coords[x], coords[y])
  colnames(coords) <- c('x', 'y')
  pts <- coordinates(coords)
  # projection
  wgs84 <- CRS('+proj=longlat +ellps=WGS84')

## retrieveCoords.R
library(data.table)
library(jsonlite)
library(readxl)
library(curl)
# Get your API from GCP
API_KEY <- '&key=$YOUR_API_KEY'
URL <- 'https://maps.googleapis.com/maps/api/geocode/json?address='
# Data source:
# # Elementary high school: https://data.gov.tw/dataset/6087
# # Junior high school: https://data.gov.tw/dataset/6088

## query.sql
 SELECT
  time_bucket('1 day', timestamp) AS period,
  summit,
  direction,
  avg(temperature),
  max(temperature),
  min(temperature)
 FROM
  temperature_hyper
 WHERE

## seqParser_preinstall.r

# install required packages
requiredPkgs <- c("BiocManager", "seqinr", "data.table", "devtools")
for ( i in 1:length(requiredPkgs)){
  if (!requireNamespace(requiredPkgs[i], quietly = TRUE))
    install.packages(requiredPkgs[i])
}
BiocManager::install("Biostrings")

devtools::install_github(repo = 'mutolisp/seqParser')

## taicol_family.csv

          
            class
            family
            family_c

            
              Hydrozoa
              Abylidae
              多面水母科

            
              Magnoliopsida
              Acanthaceae
              爵床科

            
              Asteroidea
              Acanthasteridae
              長棘海星科

            
              Anthozoa
              Acanthigorgiidae
              棘柳珊瑚科

            
              Gastropoda
              Acanthinulidae
              菜種蝸牛科

            
              Polyplacophora
              Acanthochitonidae
              毛膚石鱉科

            
              Trematoda
              Acanthocolpidae
              棘體科

            
              Insecta
              Acanthosomatidae
              角蝽科(同蝽科)

            
              Trematoda
              Acanthostomidae
              棘盤科

## rawnames.txt
Aloe plicatilis (L.) Mill.
Arisaema thunbergii subsp. autumnale J.C. Wang, J. Murata & Ohashi
Begonia austrotaiwanensis Y.K. Chen & C.I. Peng
Berberis mingetsensis Hayata
Boehmeria nivea var. tenacissima (Gaudich.) Miq.
Brachypodium sylvaticum (Huds.) P. Beauv.
Bromus carinatus Hook. & Arn.
Bulbophyllum confragosum Lin & Chang
Callicarpa japonica Thunb.
Clematis tsugetorum Ohwi
	SELECT
	id, ST_BUFFER(geom_centroid, 0.0083333) as geom_buff
	FROM
	(SELECT
	1 as id,
	ST_SRID(ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326)) as srid,
	st_centroid(ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326)) as geom_centroid,
	ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326) as geom)
	AS s;
	DROP TABLE IF EXISTS test;
	CREATE TABLE test (
	id serial,
	long double precision,
	lat double precision,
	country varchar,
	species varchar,
	geom geometry(POINT, 4326),
	CONSTRAINT pk_test PRIMARY KEY (id)
	);
	library(sp)
	library(gstat)
	library(raster)

	graycol <- gray.colors(4, 0.55, 0.95)
	# use IDW to generate zn.idw

	data(meuse)
	coordinates(meuse) <- c('x', 'y')
	data(meuse.grid)
mountain	aspect	species	cover	r
SEN	W11	A	30	1
SEN	W11	B	10	3
SEN	W11	C	0.5	2
SEN	W13	A	15	4
SEN	W13	D	5	3
SEN	W13	E	3	2
SEN	W13	F	0.1	1
SEN	W13	G	10	4
SUN	E11	A	15	2
	# install sp package before use
	# read coordinates csv
	twTransform <- function(input = file.choose(), x='x', y='y' ,from = 'wgs84', to= 'twd97'){
	require(sp)
	coords <- read.csv(input, sep = ',')
	coords <- cbind(coords[x], coords[y])
	colnames(coords) <- c('x', 'y')
	pts <- coordinates(coords)
	# projection
	wgs84 <- CRS('+proj=longlat +ellps=WGS84')
	library(data.table)
	library(jsonlite)
	library(readxl)
	library(curl)
	# Get your API from GCP
	API_KEY <- '&key=$YOUR_API_KEY'
	URL <- 'https://maps.googleapis.com/maps/api/geocode/json?address='
	# Data source:
	# # Elementary high school: https://data.gov.tw/dataset/6087
	# # Junior high school: https://data.gov.tw/dataset/6088
	SELECT
	time_bucket('1 day', timestamp) AS period,
	summit,
	direction,
	avg(temperature),
	max(temperature),
	min(temperature)
	FROM
	temperature_hyper
	WHERE

	# install required packages
	requiredPkgs <- c("BiocManager", "seqinr", "data.table", "devtools")
	for ( i in 1:length(requiredPkgs)){
	if (!requireNamespace(requiredPkgs[i], quietly = TRUE))
	install.packages(requiredPkgs[i])
	}
	BiocManager::install("Biostrings")

	devtools::install_github(repo = 'mutolisp/seqParser')
class	family	family_c
Hydrozoa	Abylidae	多面水母科
Magnoliopsida	Acanthaceae	爵床科
Asteroidea	Acanthasteridae	長棘海星科
Anthozoa	Acanthigorgiidae	棘柳珊瑚科
Gastropoda	Acanthinulidae	菜種蝸牛科
Polyplacophora	Acanthochitonidae	毛膚石鱉科
Trematoda	Acanthocolpidae	棘體科
Insecta	Acanthosomatidae	角蝽科(同蝽科)
Trematoda	Acanthostomidae	棘盤科
	Aloe plicatilis (L.) Mill.
	Arisaema thunbergii subsp. autumnale J.C. Wang, J. Murata & Ohashi
	Begonia austrotaiwanensis Y.K. Chen & C.I. Peng
	Berberis mingetsensis Hayata
	Boehmeria nivea var. tenacissima (Gaudich.) Miq.
	Brachypodium sylvaticum (Huds.) P. Beauv.
	Bromus carinatus Hook. & Arn.
	Bulbophyllum confragosum Lin & Chang
	Callicarpa japonica Thunb.
	Clematis tsugetorum Ohwi