Paul Ramsey pramsey

Centos 7 Upgrade

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

Setup

Starting from a bare Centos 7 box:

# root

sudo bash

Spatial SQL and PostGIS

This set of instructions goes with this presentation deck.

Tobler's First Law of Geography
2016 Precinct Level Presidential Election Results
Canada Geographic Information System
Learn about early GIS by watching "Data for Decision"
geojson.io

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

Spatial SQL and PostGIS

This set of instructions goes with this presentation deck.

Installing PostGIS

PGDG Yum Repository

https://yum.postgresql.org/

yum install postgis24_10

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 node-postgres add-on for handling streaming COPY, https://github.com/brianc/node-pg-copy-streams
The node middleware for reading multi-part form data https://github.com/expressjs/multer, so that a POST file upload can be read
An implementation of a multer custom storage engine to bind the upload stream to the copy stream https://github.com/expressjs/multer/blob/master/StorageEngine.md

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

	<html>
	<head>
	<style>
	html {
	font-family: sans-serif;
	}
	.geomA {
	marker-end: url(#vertexA); marker-mid: url(#vertexA); fill:#bbbbff; fill-opacity:0.5; stroke:#0000ff; stroke-width:1; stroke-opacity:1; stroke-miterlimit:4; stroke-linejoin:miter; stroke-linecap:square;
	}

	/*
	cc projtimer.c -I/usr/local/include -L/usr/local/lib -lproj -o projtimer
	./projtimer epsg:4326 epsg:4326
	./projtimer epsg:4326 epsg:26910
	*/

	#include <stdio.h>
	#include <stdlib.h>
	#include <string.h>
	#include <sys/time.h>

	<?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

	-- 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';