Skip to content

Instantly share code, notes, and snippets.

@pramsey
Created September 10, 2018 19:41
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save pramsey/126a5a384c3fca554d6be99328da11aa to your computer and use it in GitHub Desktop.

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

Derive the point tables from the polygon table.

-- One point per polygon
CREATE TABLE pts AS 
SELECT 
  ST_PointOnSurface(geom)::Geometry(point, 3347) AS geom, 
  gid, fed_num 
FROM pd;

CREATE INDEX pts_gix 
  ON pts USING GIST (geom);
  
-- Ten points per polygon (for about 700K points):
CREATE TABLE pts_10 AS 
SELECT 
  (ST_Dump(ST_GeneratePoints(geom, 10))).geom::Geometry(point, 3347) AS geom, 
  gid, fed_num 
FROM pd;

CREATE INDEX pts_10_gix 
  ON pts_10 USING GIST (geom);

-- One hundred points per polygon (for about 7M points):
CREATE TABLE pts_100 AS 
SELECT 
  (ST_Dump(ST_GeneratePoints(geom, 100))).geom::Geometry(point, 3347) AS geom, 
  gid, fed_num 
FROM pd;

CREATE INDEX pts_100_gix 
  ON pts_100 USING GIST (geom);
@mwtoews
Copy link

mwtoews commented May 28, 2019

now that it's there, add a seed parameter to ST_GeneratePoints to make this example reproducable

@blackgis
Copy link

gid is not standard, use id instead, gid should be only optional

@lefcgis
Copy link

lefcgis commented Nov 12, 2020

I've got a 3.60 Ghz processor and run marvelous!! 🗡️

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment