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; |
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
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 |
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
-- 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 |
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 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 |
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 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; |
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 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; |
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
{ type: 'Feature', | |
geometry: { type: 'Point', coordinates: [ -76.6955, 38.3754 ] }, | |
properties: | |
{ message: 'insert', | |
schema: 'public', | |
table: 'simple_table', | |
id: '12' } } | |
--------------------------------- | |
{"type":"Feature","geometry":{"type":"Point","coordinates":[-76.6955,38.3754]},"properties":{"message":"insert","schema":"public","table":"simple_table","id":"12"}} |
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
INSERT INTO simple_table(message, shape) | |
VALUES('test message', ST_GeomFromText('POINT(-76.6955 38.3754)', 4326)); |
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://postgres:user@localhost:5432/database'); | |
pubsubInstance.addChannel('actions', 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
CREATE TRIGGER sample_table_trigger | |
AFTER INSERT | |
ON public.simple_table | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.sample_notify(); |