Skip to content

Instantly share code, notes, and snippets.


Paul Ramsey pramsey

View GitHub Profile
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 postgis-svn
cd postgis-svn
CPPFLAGS=-I/usr/local/include LDFLAGS=-L/usr/local/lib ./configure --with-projdir=/usr/local --with-jsondir=/usr/local
pramsey /
Created Dec 16, 2018
Problems with PGDG Centos Upgrade Procedure when using PostGIS

Centos 7 Upgrade

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


Starting from a bare Centos 7 box:

# root
sudo bash

Parallel PostGIS Testing

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

createdb parallel
psql -c 'create extension postgis' -d parallel
shp2pgsql -s 3347 -I -D -W latin1 PD_A.shp pd | psql parallel
pramsey /
Last active Jul 14, 2020
Spatial SQL and PostGIS
pramsey / both_geoms.vrt
Created Jun 18, 2018
VRT file to write both 'the_geom' and 'the_geom_webmercator' columns
View both_geoms.vrt
<?xml version="1.0"?>
<OGRVRTLayer name="cartogeom">
<GeometryField name="the_geom" encoding="PointFromColumns" x="longitude" y="latitude">
<GeometryField name="the_geom_webmercator" encoding="PointFromColumns" x="longitude" y="latitude">
import sys
import os
import gzip
import random
import md5
from carto.auth import APIKeyAuthClient
from carto.sql import SQLClient
import requests
import hashlib
pramsey /
Last active Apr 13, 2018

So, it looks like COPY support will have to be divided into two parts, /copyfrom and /copyto, which is fine. They are both interesting, in that since the idea is to support scaling, the implementations absolutely must stream, rather than taking files or holding data in memory.

The parts needed are

The parts do in fact all fit together and work, as can been seen in this small proof-of-concept:

pramsey / rect_node_distance.sql
Created Feb 22, 2018
Performance Tests on Distance
View rect_node_distance.sql
-- Large primary geometry
select st_distance(e.geom, v.geom)
from ed_2017 e, va_ply_17 v
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
-- 25s
select _ST_DistanceRectTree(e.geom, v.geom)
from ed_2017 e, va_ply_17 v
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
pramsey /
Last active Oct 23, 2018
Carto Cosmos PostGIS && GIS Talk Notes

PostGIS && GIS


A simple browser-based terminal for running SQL against Carto using the SQL API

  • git clone
  • When you're done cloning, enter the directory and run ./
  • Point your browser at http://locahost:8000
You can’t perform that action at this time.