Last active
October 10, 2025 19:51
-
-
Save pramsey/c57f284413570713c89466e311d232a3 to your computer and use it in GitHub Desktop.
SQL to create a table with one row per person in Canada
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Download the 2021 census populations from here | |
| -- https://www12.statcan.gc.ca/census-recensement/2021/geo/aip-pia/attribute-attribs/files-fichiers/2021_92-151_X.zip | |
| -- Download the census block shape files from here | |
| -- https://www12.statcan.gc.ca/census-recensement/2021/geo/sip-pis/boundary-limites/files-fichiers/ldb_000b21a_e.zip | |
| -- Load the shapefiles into your database like this | |
| -- shp2pgsql -s 3347 -D -I ldb_000b21a_e census_blocks | psql postgis | |
| -- Create the table definition for the population csv file | |
| DROP TABLE IF EXISTS census_popn; | |
| CREATE TABLE census_popn ( | |
| PRUID_PRIDU int2, | |
| PRDGUID_PRIDUGD text, | |
| PRNAME_PRNOM text, | |
| PRENAME_PRANOM text, | |
| PRFNAME_PRFNOM text, | |
| PREABBR_PRAABBREV text, | |
| PRFABBR_PRFABBREV text, | |
| CDUID_DRIDU text, | |
| CDDGUID_DRIDUGD text, | |
| CDNAME_DRNOM text, | |
| CDTYPE_DRGENRE text, | |
| FEDUID_CEFIDU text, | |
| FEDDGUID_CEFIDUGD text, | |
| FEDNAME_CEFNOM text, | |
| CSDUID_SDRIDU text, | |
| CSDDGUID_SDRIDUGD text, | |
| CSDNAME_SDRNOM text, | |
| CSDTYPE_SDRGENRE text, | |
| DPLUID_LDIDU text, | |
| DPLDGUID_LDIDUGD text, | |
| DPLNAME_LDNOM text, | |
| DPLTYPE_LDGENRE text, | |
| ERUID_REIDU text, | |
| ERDGUID_REIDUGD text, | |
| ERNAME_RENOM text, | |
| CCSUID_SRUIDU text, | |
| CCSDGUID_SRUIDUGD text, | |
| CCSNAME_SRUNOM text, | |
| SACTYPE_CSSGENRE int2, | |
| SACCODE_CSSCODE text, | |
| CMAPUID_RMRPIDU text, | |
| CMAPDGUID_RMRPIDUGD text, | |
| CMAUID_RMRIDU text, | |
| CMADGUID_RMRIDUGD text, | |
| CMANAME_RMRNOM text, | |
| CMATYPE_RMRGENRE text, | |
| CTUID_SRIDU text, | |
| CTDGUID_SRIDUGD text, | |
| CTCODE_SRCODE text, | |
| CTNAME_SRNOM text, | |
| POPCTRRAPUID_CTRPOPRRPIDU text, | |
| POPCTRRAPDGUID_CTRPOPRRPIDUGD text, | |
| POPCTRRAUID_CTRPOPRRIDU text, | |
| POPCTRRADGUID_CTRPOPRRIDUGD text, | |
| POPCTRRANAME_CTRPOPRRNOM text, | |
| POPCTRRATYPE_CTRPOPRRGENRE int2, | |
| POPCTRRACLASS_CTRPOPRRCLASSE int2, | |
| DAUID_ADIDU text, | |
| DADGUID_ADIDUGD text, | |
| DARPLAMX_ADLAMX float8, | |
| DARPLAMY_ADLAMY float8, | |
| DARPLAT_ADLAT float8, | |
| DARPLONG_ADLONG float8, | |
| DBUID_IDIDU text, | |
| DBDGUID_IDIDUGD text, | |
| DBPOP2021_IDPOP2021 integer, | |
| DBTDWELL2021_IDTLOG2021 integer, | |
| DBURDWELL2021_IDRHLOG2021 integer, | |
| DBAREA2021_IDSUP2021 float8, | |
| DBIR2021_IDRI2021 text, | |
| ADAUID_ADAIDU text, | |
| ADADGUID_ADAIDUGD text, | |
| ADACODE_ADACODE text | |
| ); | |
| -- Load the population table from csv file | |
| \COPY census_popn FROM '2021_92-151_X.csv' | |
| WITH (FORMAT csv, ENCODING 'latin1', HEADER true, NULL '') | |
| -- Index the block key for the populations | |
| CREATE INDEX census_popn_dbuid_ididu_x ON census_popn (dbuid_ididu); | |
| -- Index the block key for the shapes | |
| CREATE INDEX census_blocks_dbuid_x ON census_blocks (dbuid); | |
| -- Join the block shapes to the population data and | |
| -- emit one point for every person in each block | |
| DROP TABLE IF EXISTS census_people; | |
| CREATE TABLE census_people_random AS | |
| SELECT dbuid, (ST_Dump(ST_GeneratePoints(geom, DBPOP2021_IDPOP2021))).geom::geometry(Point, 3347) | |
| FROM census_blocks | |
| JOIN census_popn ON (dbuid = dbuid_ididu) | |
| ORDER BY random(); | |
| CREATE INDEX census_people_geom_x | |
| ON census_people USING GIST (geom); | |
| CREATE VIEW census_people_sample AS | |
| SELECT geom::geometry(Point, 3347), dbuid | |
| FROM census_people TABLESAMPLE SYSTEM(1); | |
| CREATE VIEW census_people_sample_bernoulli AS | |
| SELECT geom::geometry(Point, 3347), dbuid | |
| FROM census_people_random TABLESAMPLE BERNOULLI(1); | |
| CREATE VIEW census_people_sample_random AS | |
| SELECT geom::geometry(Point, 3347), dbuid | |
| FROM census_people_random TABLESAMPLE SYSTEM(1); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment