Skip to content

Instantly share code, notes, and snippets.

💭
Working

William Dollins geobabbler

💭
Working
Block or report user

Report or block geobabbler

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View mvw_cell_tower_names.sql
-- 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
View mvw_hex_freq_geom.sql
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
View hex_freq_geom.sql
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;
View hew_freq.sql
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;
View sample_output.js
{ 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"}}
View insert_data.sql
INSERT INTO simple_table(message, shape)
VALUES('test message', ST_GeomFromText('POINT(-76.6955 38.3754)', 4326));
View pubsub.js
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));
});
View notify_trigger.sql
CREATE TRIGGER sample_table_trigger
AFTER INSERT
ON public.simple_table
FOR EACH ROW
EXECUTE PROCEDURE public.sample_notify();
View notify_trigger_func.sql
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 || '",' ||
View simple_spatial_table.sql
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
);
You can’t perform that action at this time.