Skip to content

Instantly share code, notes, and snippets.

Avatar

Paul Ramsey pramsey

View GitHub Profile
@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
@pramsey
pramsey / 0-spatial-sql-postgis.md
Last active Jul 14, 2020
Spatial SQL and PostGIS
View 0-spatial-sql-postgis.md
@pramsey
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"?>
<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">
View copy-test.py
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 Apr 13, 2018
SQL API COPY
View index.md

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 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
pramsey / postgis-gis.md
Last active Oct 23, 2018
Carto Cosmos PostGIS && GIS Talk Notes
View postgis-gis.md

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
You can’t perform that action at this time.