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 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.