Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
Working

William Dollins geobabbler

💭
Working
View GitHub Profile
@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; }
@geobabbler
geobabbler / PostgisDialectExtensions.java
Last active Aug 13, 2021
Simple extension to PostGIS dialect for Hibernate
View PostgisDialectExtensions.java
package com.geomusings.dialect;
//import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.CustomType;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.hibernate.spatial.dialect.postgis.PostgisDialect;
import org.hibernate.usertype.UserType;
import org.hibernate.spatial.SpatialAggregate;
@geobabbler
geobabbler / vw_stops.sql
Created Mar 11, 2021
SQL Snippet for 2020-03-11 post on geoMusings
View vw_stops.sql
SELECT
ARRAY_TO_STRING(ARRAY_AGG(CONCAT(st_x(q.loc),',',st_y(q.loc))), ';') AS coords
FROM (
WITH
DATA AS (
SELECT
loc,
resource_id,
wkt,
capture_date,
@geobabbler
geobabbler / bigquery_sample_20210222.sql
Created Feb 22, 2021
SQL sample for blog post - 22 Feb 2021
View bigquery_sample_20210222.sql
WITH data AS (
SELECT name, loc, resource_id, wkt, capture_date, (LAG(capture_date) OVER (PARTITION BY resource_id ORDER BY capture_date ASC)) AS prev_date,
(LAG(wkt) OVER (PARTITION BY resource_id ORDER BY capture_date ASC)) AS prev_loc
FROM
(SELECT name, resource_id, loc, st_astext(loc) as wkt, capture_date FROM `my_project.my_dataset.geo_sample`
order by capture_date desc) q
ORDER BY capture_date
)
SELECT name, resource_id, loc, capture_date FROM data
WHERE
View notify_with_mail.js
var PGPubsub = require('pg-pubsub');
var nodemailer = require('nodemailer');
var pubsubInstance = new PGPubsub('postgres://password:user@host:port/database'); // don't hard code this in real life. get it from a config or environment variable
var transporter = nodemailer.createTransport({
service: 'gmail',
auth: {
user: "email@gmail.com", // don't hard code this in real life. get it from a config or environment variable
pass: "complex_gmail_password" // don't hard code this in real life. get it from a config or environment variable
@geobabbler
geobabbler / jhu_counties2geopackage.fmw
Last active Apr 1, 2020
FME workspace to scrape JHU COVID-19 county-level data into a geopackage.
View jhu_counties2geopackage.fmw
#! <?xml version="1.0" encoding="UTF-8" ?>
#! <WORKSPACE
# Command-line to run this workspace:
# /opt/fme-desktop-2020/fme /home/myname/jhu_counties2gpkg.fmw
# --SourceDataset_GEOJSON "https://services1.arcgis.com/0MSEUqKaxRlEPj5g/ArcGIS/rest/services/ncov_cases_US/FeatureServer/0/query?where=1%3D1&outFields=*&f=geojson&token="
# --DestDataset_OGCGEOPACKAGE "/home/myname/jhu_county.gpkg"
#
#! ATTR_TYPE_ENCODING="SDF"
#! BEGIN_PYTHON=""
#! BEGIN_TCL=""
View jhu_listen.js
var PGPubsub = require('pg-pubsub');
var pubsubInstance = new PGPubsub('postgres://password:user@host:5432/database');
pubsubInstance.addChannel('jhucounty', function (channelPayload) {
console.log(channelPayload);
console.log('---------------------------------');
console.log(JSON.stringify(channelPayload));
});
View jhu_update.sh
#!/bin/bash
###################################################
# Bash script to pull JHU data and update
###################################################
#Set the value of variable
database="$1"
user="$2"
host="$3"
View jhu_update.sh
#!/bin/bash
###################################################
# Bash script to pull JHU data and update
###################################################
#Set the value of variable
database="$1"
user="$2"
host="$3"
View jhu_initial_pull.sh
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