Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
Working

William Dollins geobabbler

💭
Working
View GitHub Profile
@geobabbler
geobabbler / vw_stops.sql
Created Mar 11, 2021
SQL Snippet for 2020-03-11 post on geoMusings
View vw_stops.sql
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,
@geobabbler
geobabbler / bigquery_sample_20210222.sql
Created Feb 22, 2021
SQL sample for blog post - 22 Feb 2021
View bigquery_sample_20210222.sql
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
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
@geobabbler
geobabbler / jhu_counties2geopackage.fmw
Last active Apr 1, 2020
FME workspace to scrape JHU COVID-19 county-level data into a geopackage.
View jhu_counties2geopackage.fmw
#! <?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
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
#!/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
#!/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
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
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
-- 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;