Shell script to import data into Postgres from https://www.nhgis.org. If you use this, you'll need to edit line 14 to include the census data you're importing.

100%

Shell script to import data into Postgres from https://www.nhgis.org. If you use this, you'll need to edit line 14 to include the census data you're importing.

✓ Published0🌍 Public

Last edited Oct 11, 2013

Created on Oct 11, 2013

This script sets up a Postgres database to hold U.S. Census block-group data, combining demographic race counts with spatial geometries. It first imports CSV race data into a table, then converts shapefiles to a uniform projection (EPSG:4326) using `ogr2ogr` and appends them via `shp2pgsql`. The code uses PostGIS for geometry support and creates a spatial index on the `geom` column. It relies on command-line tools like `dropdb`, `createdb`, and `psql`, processing files from the NHGIS source archive. The final output enables querying joined attribute and spatial data.

AI-generated description

Similar vizzes

Loading thumbnail…

just a bookmark of random psql queries.

This example visualizes spatial relationships between neighborhoods, city blocks, and demographic data in Portland and Laurelhurst through a series of SQL queries. The code shows how geographic joins and intersections are performed using PostGIS functions like `ST_Intersects`, combining building footprints, block polygons, and census block-group data. The queries calculate race-based percentages, such as percent white and percent black, from census fields like `h7x002` and `h7x003`, while filtering for specific places and positive population counts. The gist demonstrates iterative query building for choropleth-style analysis, revealing how raw geographic and tabular data are merged to support subsequent mapping or charting in a visualization tool.

CCaged

63% match

Loading thumbnail…

Simple shell script to merge shapefiles from INE

This example shows a shell script that downloads, reprojects, and merges Mexican electoral geography shapefiles from the INE’s distritación 2017 federal collection into a single output file. It processes layers such as `SECCION`, `ENTIDAD`, and `DISTRITO` for all 32 states, using `ogr2ogr` from GDAL to reproject each source into EPSG:4326 and append the features. The script handles a structural anomaly for file number 06 by adjusting the source path, and it relies on `/vsizip//vsicurl/` for remote zip access. The merged result is saved as a new shapefile named with the layer and `-wgs84` suffix.

GGeoWill

55% match

Loading thumbnail…

Gist 1e418bc5c048fed5ef2604a1902eb735

This example demonstrates a spatial correction of a U.S. District Court jurisdiction boundary using PostGIS. It shows how an erroneous polygon (fid 57) is split into two parts, with one part merged into a neighboring jurisdiction (fid 58) and the other retained in place. The SQL uses `st_dump` to extract the polygon components, then `st_union` and `st_multi` to recompute the merged and retained geometries. The `UPDATE` statement with a `CASE` expression applies the corrected shapes back to the table, relying on PostGIS geometry functions for the operation.

CCaged

55% match

Loading thumbnail…

Gist 0c701eddfaa4b93ee172

Two terminal sessions show failed attempts to import OpenStreetMap data into a PostgreSQL database using Osmosis, a Java command-line tool. The visualization captures log output from sequential runs of the `osmosis` command, displaying errors from Java's `java.net.ConnectException` when the database connection is refused. The first attempt fails because the `--truncate-pgsql` task receives an unexpected `port` argument, while the second fails because PostgreSQL is not running. The display highlights the pipeline's reliance on database configuration and connection handling, rendered as plain terminal text.

CCaged

55% match

Loading thumbnail…

copy to temp table method for updating addressbase

This example demonstrates a PostgreSQL method for efficiently replacing the contents of a large AddressBase database table by loading incoming CSV data into temporary tables, building indexes and constraints there, and then using a transactional rename swap to minimize downtime. The script first copies data from `addressbase_cleaned.csv` and `uprn-to-councils.csv` into empty temp tables, then adds primary keys, GiST spatial indexes on `location`, and B-tree indexes on `postcode` and `uprn`. Within a `BEGIN`/`COMMIT` block, it drops foreign keys, renames the existing tables to `_old`, promotes the temp tables to the main names, and re-adds the foreign key constraints. Timings for each operation illustrate the relative cost of loading, indexing, and swapping.

GGeoWill

54% match

Loading thumbnail…

Gist 652595e55960a8e04a29

This example visualizes the spatial relationships between metropolitan statistical areas and their constituent counties, townships, and other administrative geometries. The code first builds a combined geometry table using SQL, merging overlapping regions across multiple spatial layers while excluding special cases like Boston. It then renders these shapes using D3.js, likely leveraging `d3.geoPath` for projection and canvas or SVG drawing. The query explicitly handles hierarchical and overlapping geographies, prioritizing township-level detail where available and falling back to county unions for other areas. The final visualization shows how different government boundaries interlock within U.S. metropolitan regions, with the SQL preprocessing ensuring no duplicated or missing polygons.

CCaged

54% match

Loading thumbnail…

PostgreSQL command line cheatsheet

This example presents a text-based cheat sheet for PostgreSQL command-line tools, rendering a curated list of psql commands, configuration snippets, and handy SQL queries in a plain markdown document. It shows the core syntax for database management tasks, such as connecting to databases, listing tables and views, managing user roles, and adjusting server logging verbosity, alongside query examples for inspecting indexes, active sessions, and table sizes. The visualization relies on static code blocks and inline formatting, with no interactive graphics or JavaScript libraries; instead, it leverages the raw text content from a gist, emphasizing practical API references like `psql` meta-commands and `pg_stat_activity` queries.

AAngieLiang

52% match

Loading thumbnail…

PostgreSQL installation tutorial

This example presents a step-by-step guide for installing PostgreSQL across macOS, Linux, and Windows. It shows the terminal command `psql -U postgres` as the target for verifying a successful setup, then details OS-specific actions, such as using the Postgres.app on macOS, compiling from source via `wget` on Linux, and using the EnterpriseDB installer on Windows. The tutorial also covers checking the `$PATH` variable and verifying the server port with `lsof`.

115Dkatz

50% match