geobabbler

ogr2ogr -overwrite -f "PostGreSQL" 
PG:"host=$host user=$user dbname=$database password=$pw" 
'https://services1.arcgis.com/0MSEUqKaxRlEPj5g/ArcGIS/rest/services/ncov_cases_US/FeatureServer/0/query?where=1%3D1&outFields=*&f=geojson&token='
-nln jhu_county

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;

geobabbler

SELECT sql
  FROM (
        SELECT sql sql, type type, tbl_name tbl_name, name name
          FROM sqlite_master
         UNION ALL
        SELECT sql, type, tbl_name, name
          FROM sqlite_temp_master
       )
 WHERE type != 'meta'
   AND sql NOTNULL

geobabbler

-- Materialized View: public.mvw_cellular_test

CREATE MATERIALIZED VIEW public.mvw_cellular_test AS 
 SELECT cellular_test.id,
    cellular_test.geom,
    cellular_test.licensee,
    cellular_test.loccity,
    us_states_lookup.name AS state_name
   FROM cellular_test
     INNER JOIN us_states_lookup ON cellular_test.locstate::text = us_states_lookup.abbr::text

geobabbler

CREATE MATERIALIZED VIEW public.mvw_cellular_count_geom_hex AS 
 SELECT uuid_generate_v4() AS oid,
    vw_cellular_count_geom_hex.id,
    vw_cellular_count_geom_hex.shape,
    COALESCE(vw_cellular_count_geom_hex.features, 0::bigint)::integer AS features
   FROM vw_cellular_count_geom_hex
WITH DATA;

CREATE INDEX sidx_mvw_cellular_count_geom_hex_shape
  ON public.mvw_cellular_count_geom_hex

geobabbler

 CREATE OR REPLACE VIEW public.vw_cellular_count_geom_hex AS 
 SELECT us_hex_grid.id,
    us_hex_grid.shape,
    vw_cellular_freq_hex.features
   FROM us_hex_grid
     LEFT JOIN vw_cellular_freq_hex ON us_hex_grid.id = vw_cellular_freq_hex.id;

geobabbler

 SELECT us_hex_grid.id,
    count(cellular.id) AS features
   FROM us_hex_grid,
    cellular
  WHERE st_contains(us_hex_grid.shape, cellular.geom)
  GROUP BY us_hex_grid.id;

geobabbler

/*
    GlobalMercator.cs
    Copyright (c) 2014 Bill Dollins. All rights reserved.
    http://blog.geomusings.com
*************************************************************   
	Based on GlobalMapTiles.js - part of Aggregate Map Tools
	Version 1.0
	Copyright (c) 2009 The Bivings Group
	All rights reserved.
	Author: John Bafford