geobabbler

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

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 

geobabbler

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

#! <?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=""

geobabbler

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));
});

geobabbler

#!/bin/bash
 
###################################################
# Bash script to pull JHU data and update 
###################################################
 
#Set the value of variable
database="$1"
user="$2"
host="$3"

geobabbler

#!/bin/bash
 
###################################################
# Bash script to pull JHU data and update 
###################################################
 
#Set the value of variable
database="$1"
user="$2"
host="$3"

geobabbler

CREATE TRIGGER jhu_county_trigger
  AFTER UPDATE
  ON public.jhu_county
  FOR EACH ROW
  WHEN(NEW.watched = TRUE)
  EXECUTE PROCEDURE public.jhu_county_watched();

geobabbler

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 || '",' ||

geobabbler

-- 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;