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

{ 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"}}

geobabbler

INSERT INTO simple_table(message, shape)
VALUES('test message', ST_GeomFromText('POINT(-76.6955 38.3754)', 4326));

geobabbler

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

geobabbler

CREATE TRIGGER sample_table_trigger
  AFTER INSERT
  ON public.simple_table
  FOR EACH ROW
  EXECUTE PROCEDURE public.sample_notify();

geobabbler

CREATE OR REPLACE FUNCTION public.sample_notify()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
  PERFORM pg_notify('actions', '{"type": "Feature", "geometry": ' || 
	st_asgeojson(NEW.shape) || 
	',"properties": {"message": "' || 
	'insert' || '",' ||
	'"schema": "' || TG_TABLE_SCHEMA || '",' ||

geobabbler

CREATE TABLE public.simple_table
(
  id integer NOT NULL DEFAULT nextval('simple_table_id_seq'::regclass),
  message text,
  shape geometry(Point,4326),
  CONSTRAINT pk_notify PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);