Paul Ramsey pramsey

## vrt2pgsql.py
import sys
import xml.etree.ElementTree as ET
import argparse

######################################################################

parser = argparse.ArgumentParser()
parser.add_argument('-f', '--file',
                    required=True,
                    dest='file',

## invalid.html
<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;
}

## projtimer.c
/*
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>

## parallel-postgis.md

      
              1 file
            
          
              0 forks
            
          
              3 comments
            
          
              2 stars
            
          
                pramsey
                / parallel-postgis.md
            
            
              Created
              September 10, 2018 19:41
            
          
    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

  
## pgconf-eu-keynote.md

      
              1 file
            
          
              1 fork
            
          
              0 comments
            
          
              1 star
            
          
                pramsey
                / pgconf-eu-keynote.md
            
            
              Last active
              June 16, 2020 06:03
            
              
                PGConf.eu Keynote Notes
              
          
    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


## postgis-gis.md

      
              1 file
            
          
              1 fork
            
          
              0 comments
            
          
              1 star
            
          
                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


## 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">

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

## index.md

      
              1 file
            
          
              0 forks
            
          
              0 comments
            
          
              0 stars
            
          
                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 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:

  
## 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';
	import sys
	import xml.etree.ElementTree as ET
	import argparse

	######################################################################

	parser = argparse.ArgumentParser()
	parser.add_argument('-f', '--file',
	required=True,
	dest='file',
	<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';