View vw_stops.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
ARRAY_TO_STRING(ARRAY_AGG(CONCAT(st_x(q.loc),',',st_y(q.loc))), ';') AS coords | |
FROM ( | |
WITH | |
DATA AS ( | |
SELECT | |
loc, | |
resource_id, | |
wkt, | |
capture_date, |
View bigquery_sample_20210222.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH data AS ( | |
SELECT name, loc, resource_id, wkt, capture_date, (LAG(capture_date) OVER (PARTITION BY resource_id ORDER BY capture_date ASC)) AS prev_date, | |
(LAG(wkt) OVER (PARTITION BY resource_id ORDER BY capture_date ASC)) AS prev_loc | |
FROM | |
(SELECT name, resource_id, loc, st_astext(loc) as wkt, capture_date FROM `my_project.my_dataset.geo_sample` | |
order by capture_date desc) q | |
ORDER BY capture_date | |
) | |
SELECT name, resource_id, loc, capture_date FROM data | |
WHERE |
View notify_with_mail.js
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var PGPubsub = require('pg-pubsub'); | |
var nodemailer = require('nodemailer'); | |
var pubsubInstance = new PGPubsub('postgres://password:user@host:port/database'); // don't hard code this in real life. get it from a config or environment variable | |
var transporter = nodemailer.createTransport({ | |
service: 'gmail', | |
auth: { | |
user: "email@gmail.com", // don't hard code this in real life. get it from a config or environment variable | |
pass: "complex_gmail_password" // don't hard code this in real life. get it from a config or environment variable |
View jhu_counties2geopackage.fmw
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! <?xml version="1.0" encoding="UTF-8" ?> | |
#! <WORKSPACE | |
# Command-line to run this workspace: | |
# /opt/fme-desktop-2020/fme /home/myname/jhu_counties2gpkg.fmw | |
# --SourceDataset_GEOJSON "https://services1.arcgis.com/0MSEUqKaxRlEPj5g/ArcGIS/rest/services/ncov_cases_US/FeatureServer/0/query?where=1%3D1&outFields=*&f=geojson&token=" | |
# --DestDataset_OGCGEOPACKAGE "/home/myname/jhu_county.gpkg" | |
# | |
#! ATTR_TYPE_ENCODING="SDF" | |
#! BEGIN_PYTHON="" | |
#! BEGIN_TCL="" |
View jhu_listen.js
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var PGPubsub = require('pg-pubsub'); | |
var pubsubInstance = new PGPubsub('postgres://password:user@host:5432/database'); | |
pubsubInstance.addChannel('jhucounty', function (channelPayload) { | |
console.log(channelPayload); | |
console.log('---------------------------------'); | |
console.log(JSON.stringify(channelPayload)); | |
}); |
View jhu_update.sh
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
################################################### | |
# Bash script to pull JHU data and update | |
################################################### | |
#Set the value of variable | |
database="$1" | |
user="$2" | |
host="$3" |
View jhu_update.sh
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
################################################### | |
# Bash script to pull JHU data and update | |
################################################### | |
#Set the value of variable | |
database="$1" | |
user="$2" | |
host="$3" |
View jhu_trigger.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TRIGGER jhu_county_trigger | |
AFTER UPDATE | |
ON public.jhu_county | |
FOR EACH ROW | |
WHEN(NEW.watched = TRUE) | |
EXECUTE PROCEDURE public.jhu_county_watched(); |
View jhu_trigger_func.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION public.jhu_county_watched() | |
RETURNS trigger AS | |
$BODY$ | |
DECLARE | |
BEGIN | |
PERFORM pg_notify('jhucounty', '{"type": "Feature", "geometry": ' || | |
st_asgeojson(NEW.wkb_geometry) || | |
',"properties": {"message": "' || | |
'update' || '",' || | |
'"fips": "' || NEW.fips || '",' || |
View watched_column.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Column: watched | |
-- ALTER TABLE public.jhu_county DROP COLUMN watched; | |
ALTER TABLE public.jhu_county ADD COLUMN watched boolean; | |
ALTER TABLE public.jhu_county ALTER COLUMN watched SET DEFAULT false; |
NewerOlder