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, |
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 |
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 |
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="" |
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)); | |
}); |
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" |
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" |
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(); |
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 || '",' || |
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