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

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 Oct 11, 2019
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
pramsey /
Last active Feb 4, 2018
Bibliography for the 2018 "small IT" talk
  • Google Presentation Link []
  • PDF Presentation Link []
  • "Ottawa turns to U.S. tech giants too often: internal memo" []
  • "Government as a Platform: the next phase of digital transformation" []
  • DevOps Real Talk [] "incremental change, tight feedback loops, shared knowledge, and mutual respect" "If you're a developer releasing large changesets, you're part of the problem."
  • The Government IT Self-Harm Playbook []
  • Better For Less (UK IT) [[](https:/
pramsey /
Created Dec 7, 2017
Carto Patched PostGIS/PostgreSQL Performance

Carto Patched PostGIS/PostgreSQL Performance

The REL_10_CARTO PostgreSQL branch and svn-2.4-cartodb branch of PostGIS carry patches to improve performance around our core use cases (generating resolution-appropriate data for rendering in Mapnik and creating vector tiles). They also include patches that make the PostgreSQL planner more likely to pick parallel plans, in particular when using PostGIS functions.

  • The first round of improvements went into PostGIS and were focussed on the functions used commonly in feeding Mapnik and MVT.
  • The second round went into both PostGIS and PostgreSQL and were focussed on improving parallel query for our use cases.
2.4 Aug 2.4cdb Oct 2.4cdb Dec
# features ms μs ms μs
pramsey / index.html
Last active Nov 21, 2017
GeoNames on new Carto.js
View index.html
<!DOCTYPE html>
<html lang="en">
<meta charset="UTF-8">
<title>Geonames Heatmap</title>
<!-- Include Leaflet 1.2.0 Library -->
<link rel="stylesheet" href="" />
<script src=""></script>
You can’t perform that action at this time.