just a bookmark of random psql queries.

100%

just a bookmark of random psql queries.

✓ Published0🌍 Public

Last edited Nov 4, 2013

Created on Oct 25, 2013

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.

AI-generated description

Similar vizzes

Loading thumbnail…

Scoring buildings based on proximity to neighborhood features

This example visualizes building scores in Portland based on proximity to nearby amenities, assigning higher ranks to structures near supermarkets, rail stops, and parks. It shows a ranked map of buildings, with scores aggregated from overlapping amenity zones. The workflow uses PostGIS spatial SQL functions like `st_expand` and `st_intersects` to generate buffered zones, then sums scores per building via a `union` query and stores results in a new table. The rendering likely relies on a mapping library such as Leaflet or Mapbox, though the code excerpt focuses on the spatial data preparation rather than the front-end visualization.

CCaged

73% 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

73% match

Loading thumbnail…

neighborhood mongo

Neighborhood mongo maps Portland neighborhood polygons stored as GeoJSON in MongoDB, rendered on a zoomable, panning map. Each polygon is drawn as an SVG path using a geographic projection, with neighborhood boundaries overlaid on muted base tiles. Hovering highlights a district and reveals its name, while the geo-coordinates in the data drive the shape and position of each region. The visualization demonstrates how spatial querying and rendering of GeoJSON features can be handled directly from a MongoDB document store.

CCaged

68% match

Loading thumbnail…

Portland blockgroup TopoJSON

This example visualizes the boundaries of Portland block groups using a TopoJSON file. The map is rendered with D3, which reads the topology and converts it into SVG paths on the fly. The block groups are drawn as distinct polygon features, with each region represented by arcs from the TopoJSON topology. The geographic boundaries are likely derived from Census data, and the visualization highlights the spatial layout of Portland’s neighborhoods at the block-group level. The author, Caged, encoded the TopoJSON structure to share this map, making it easy to reuse for choropleth or interactive map projects. The display emphasizes boundary shapes rather than quantitative values, offering a clean basemap for further analysis.

CCaged

65% match

Loading thumbnail…

Tilemill PostGIS layer subquery showing layer clipping via polygon intersection.

This example demonstrates how to clip building footprint data to a specific neighborhood boundary using a spatial intersection query in Tilemill. The visualization shows only the buildings that fall within the LAURELHURST neighborhood, with their geometries trimmed to the neighborhood polygon’s edge. The underlying SQL query joins the `buildings` and `neighborhoods` tables via PostGIS’s `ST_Intersects` function, selecting the boundary polygon as `bounding_area`. The map renders the resulting clipped layer, effectively masking out any building portions that extend beyond the neighborhood’s borders. This technique is useful for isolating features within a defined administrative or custom area.

CCaged

64% match

Loading thumbnail…

Gist 877801

This example demonstrates a debugging session for a geospatial query on a Portland crime database using MongoDB. The visualization is not a traditional chart but rather a log of command-line interactions in the MongoDB shell, revealing a critical bug in the `geoNear` command with spherical geometry. The author, Caged, queries crime records at a specific latitude/longitude coordinate and discovers that `$centerSphere` queries return no results due to a "spherical distance would require wrapping" error, while a `geoNear` command returns records at a distance of 1.803 even for the exact same location. The gist highlights the inconsistency and potential bug in MongoDB's spherical distance calculations, where identical coordinates yield non-zero distances. The visualization would likely plot these crime locations on a map, with a note or highlight on the unexpected distance value, illustrating the importance of accurate geospatial querying and the quirks of early MongoDB versions.

CCaged

64% match

Loading thumbnail…

Gist 05f4cffac50217bd0c33a538cb013563

This example visualizes geographic routing and geocoding workflows in R, showing how spatial data can be processed and mapped. It installs and loads packages for text mining, word clouds, and interactive map viewing, including `ggplot2`, `tm`, `wordcloud`, and `RColorBrewer` for static and corpus-based visualizations. The code also integrates the `photon` and `osrm` packages via `devtools` to access geocoding and routing APIs, enabling point-and-route calculations. The visualization pipeline combines spatial operations with text analytics, demonstrating a multi-step data enrichment and mapping process.

RRCura

63% match

Loading thumbnail…

Portland parks

The visualization displays the geographic boundaries of parks throughout Portland, Oregon, using a TopoJSON file that encodes each park as a polygon or multipolygon. It renders these shapes on a map-like canvas, showing the distribution and relative sizes of green spaces across the city. The implementation uses D3’s `d3.geoPath` to project and draw the topology, with the data sourced from a gist by Caged. The scene relies on D3’s geographic projection and path generation to convert the TopoJSON arcs into visible, filled polygons.

CCaged

63% match