Skip to content

Instantly share code, notes, and snippets.

View pramsey's full-sized avatar

Paul Ramsey pramsey

View GitHub Profile
@pramsey
pramsey / pgconf-eu-keynote.md
Last active June 16, 2020 06:03
PGConf.eu Keynote Notes

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 / 0-spatial-sql-postgis.md
Last active November 13, 2023 18:33
Spatial SQL and PostGIS
@pramsey
pramsey / both_geoms.vrt
Created June 18, 2018 17:55
VRT file to write both 'the_geom' and 'the_geom_webmercator' columns
<?xml version="1.0"?>
<OGRVRTDataSource>
<OGRVRTWarpedLayer>
<OGRVRTLayer name="cartogeom">
<SrcDataSource>cartogeom.csv</SrcDataSource>
<GeometryField name="the_geom" encoding="PointFromColumns" x="longitude" y="latitude">
<GeometryType>wkbPoint</GeometryType>
<SRS>EPSG:4326</SRS>
</GeometryField>
<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
pramsey / index.md
Last active April 13, 2018 12:50
SQL API COPY

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
pramsey / rect_node_distance.sql
Created February 22, 2018 19:40
Performance Tests on Distance
-- 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
pramsey / postgis-gis.md
Last active October 23, 2018 21:32
Carto Cosmos PostGIS && GIS Talk Notes

PostGIS && GIS

cdb-manager

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

  • http://github.com/cartodb/cdb-manager
  • git clone git@github.com:CartoDB/cdb-manager.git
  • When you're done cloning, enter the directory and run ./httpserv.py
  • Point your browser at http://locahost:8000
@pramsey
pramsey / small-it-bibliography.md
Last active February 4, 2018 23:05
Bibliography for the 2018 "small IT" talk
  • Google Presentation Link [goo.gl]
  • PDF Presentation Link [s3.cleverelephant.ca]
  • "Ottawa turns to U.S. tech giants too often: internal memo" [cbc.ca]
  • "Government as a Platform: the next phase of digital transformation" [gds.blog.gov.uk]
  • DevOps Real Talk [www.theregister.co.uk] "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 [medium.com]
  • Better For Less (UK IT) [[drive.google.com](https:/
@pramsey
pramsey / performance.md
Created December 7, 2017 18:26
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