Skip to content

Instantly share code, notes, and snippets.

Paul Ramsey pramsey

Block or report user

Report or block pramsey

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
@pramsey
pramsey / vsicurl_ogr_fdw.sql
Last active Nov 6, 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 / 0-spatial-sql-postgis.md
Last active Oct 11, 2019
Spatial SQL and PostGIS
View 0-spatial-sql-postgis.md
@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,
@pramsey
pramsey / addressnormsearch.md
Created May 2, 2019
Quick and Dirty Address Matching with LibPostal
View addressnormsearch.md

Quick and Dirty Address Matching with LibPostal

Most businesses have databases of previous customers, and data analysts will frequently be asked to join arbitrary data to the customer tables in order to provide analysis.

Unfortunately joining address data together is notoriously difficult:

  • The same address can be expressed in many ways
  • The parts of addresses are not always clear
  • There are valid lexically very similar addresses very nearby any given address
View 3005.txt
-- Resolving 3005 via Proj4
postgis=# select st_astext(st_transform('SRID=4326;POINT(-125 45)',3005));
st_astext
------------------------------------------
POINT(1079515.82040926 561.626612238131)
(1 row)
-- Also resolving 3005 via Proj4!!!!
postgis=# select st_astext(st_transform('SRID=4326;POINT(-125 45)','EPSG:3005'));
st_astext
View proj.c
#include <stdio.h>
#include <strings.h>
#include <stdlib.h>
#include <math.h>
#include <regex.h>
#include "/usr/local/include/proj.h"
static void print_coord(PJ_COORD *c)
{
printf("COORD %g %g\n", c->xyzt.x, c->xyzt.y);
You can’t perform that action at this time.