Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

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

This comment has been minimized.

Copy link

commented May 28, 2019

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

@blackgis

This comment has been minimized.

Copy link

commented Jun 11, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.