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 / 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);
@pramsey
pramsey / readme-freebsd.txt
Last active Jan 18, 2019
FreeBSD 11.1 PostGIS Install
View readme-freebsd.txt
sudo pkg install postgresql11-server postgresql11-client \
json-c proj gdal libxml2 geos \
gmake git subversion gcc7 autoconf automake libtool flex bison cunit \
libxslt docbook-xsl ImageMagick7 dblatex
svn co https://svn.osgeo.org/postgis/trunk postgis-svn
cd postgis-svn
./autogen.sh
CPPFLAGS=-I/usr/local/include LDFLAGS=-L/usr/local/lib ./configure --with-projdir=/usr/local --with-jsondir=/usr/local
@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
You can’t perform that action at this time.