Skip to content

Instantly share code, notes, and snippets.

💭
Working

William Dollins geobabbler

View GitHub Profile
View schema_list_fk.sql
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
geobabbler / MBTilesProvider.cs
Created Feb 25, 2014
Simple class to access MBTiles in C#
View MBTilesProvider.cs
public class MBTilesProvider
{
public GeoExtent Bounds { get; private set; }
public CoordinatePair Center { get; private set; }
public int MinZoom { get; private set; }
public int MaxZoom { get; private set; }
public string Name { get; private set; }
public string Description { get; private set; }
public string MBTilesVersion { get; private set; }
public string Path { get; private set; }
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;
@geobabbler
geobabbler / GlobalMercator.cs
Created Feb 20, 2014
C# implementation of GlobalMercator class from globalmaptiles.py
View GlobalMercator.cs
/*
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
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 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 insert_data.sql
INSERT INTO simple_table(message, shape)
VALUES('test message', ST_GeomFromText('POINT(-76.6955 38.3754)', 4326));
You can’t perform that action at this time.