Skip to content

Instantly share code, notes, and snippets.

Paul Ramsey pramsey

View GitHub Profile
@pramsey
pramsey / 0-spatial-sql-postgis.md
Last active May 17, 2020
Spatial SQL and PostGIS
View 0-spatial-sql-postgis.md
View voronoi.sql
CREATE OR REPLACE
-- Tile generating function takes in tile coordinates
-- and parameters and returns a tile in a bytea return.
-- We take in a click point and a count of how many
-- hydrants to build our voronoi with.
FUNCTION public.hydrants_delaunay(
z integer, x integer, y integer,
lon float8 default -123.129,
lat float8 default 49.253,
@pramsey
pramsey / early.txt
Last active Jan 21, 2020
FOSS4G 2020 Early Acceptance Letter
View early.txt
Subject: FOSS4G 2020 Early Paper Deadline is Feb 4!
FOSS4G 2020 will be in Calgary, Canada this year, from August 24-29.
It seems so far away, and yet, the first submission deadline is almost here...
https://2020.foss4g.org/early-acceptance/
If you submit your talk to the Call for Papers before February 4,
you will be eligible for Early Acceptance!
@pramsey
pramsey / vsicurl_ogr_fdw.sql
Last active Nov 25, 2019
OGR FDW against remote static files
View vsicurl_ogr_fdw.sql
--
-- Not only does this FDW definition read a remote file over HTTP and provide
-- a tabular view of the data, it also does so even though the file
-- is encased in a ZIP file!
-- See https://gdal.org/user/virtual_file_systems.html for more magic.
--
CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;
CREATE SERVER myserver
@pramsey
pramsey / remote-regress.sh
Created Jul 22, 2019
PostGIS Remote Regress
View remote-regress.sh
VER=2.4
svn co https://svn.osgeo.org/postgis/branches/${VER}/ postgis-${VER}-svn
cd postgis-${VER}-svn
touch loader/shp2pgsql loader/pgsql2shp
chmod 755 loader/shp2pgsql loader/pgsql2shp
touch raster/loader/raster2pgsql
chmod 755 raster/loader/raster2pgsql
cd regress
@pramsey
pramsey / centos-7-postgis-upgrade.md
Created Dec 16, 2018
Problems with PGDG Centos Upgrade Procedure when using PostGIS
View centos-7-postgis-upgrade.md

Centos 7 Upgrade

PgSQL 10 / PostGIS 2.4 => PgSQL 11 / PostGIS 2.5

Setup

Starting from a bare Centos 7 box:

# root
sudo bash
View parallel-postgis.md

Parallel PostGIS Testing

Create a database, enable PostGIS and load the polling divisions.

createdb parallel
psql -c 'create extension postgis' -d parallel
wget http://ftp.geogratis.gc.ca/pub/nrcan_rncan/vector/electoral/2015/pd338.2015.zip
unzip pd338.2015.zip
shp2pgsql -s 3347 -I -D -W latin1 PD_A.shp pd | psql parallel
@pramsey
pramsey / streets.sql
Last active May 31, 2019
Streets Data
View streets.sql
-- shp2pgsql -s 26918 -D -I -i nyc_streets | psql streets
-- Generate gaussian offset random points from input roads
-- add more roads in the 'gid' list as desired
DROP TABLE IF EXISTS pts;
CREATE TABLE pts AS
WITH s AS (
SELECT
gid,
nextval('nyc_streets_gid_seq') AS a,
You can’t perform that action at this time.