Gist 1e418bc5c048fed5ef2604a1902eb735

100%

Gist 1e418bc5c048fed5ef2604a1902eb735

✓ Published0🌍 Public

Last edited Apr 1, 2020

Created on Apr 1, 2020

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.

AI-generated description

Similar vizzes

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

67% match

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

62% match

Loading thumbnail…

Naive dispersal of overlapping points in PostGIS

This example shows how to disperse overlapping point markers that share identical coordinates in a PostGIS database. It first groups points that occupy the same location and counts their multiplicity, then creates buffer polygons whose sizes scale with the number of overlapping points. Using a custom `RandomPoint` function, it randomly relocates each original point within its corresponding buffer, effectively separating coincident markers so all inspections become visible. The visualization relies on PostGIS spatial SQL queries—including `st_equals`, `st_buffer`, and `st_contains`—to generate the dispersal, with the random-point logic implemented in PL/pgSQL and executed directly in the database.

CCaged

60% match

Loading thumbnail…

Gist d8085662349426513b73

This map visualizes a geospatial polygon from a GeoJSON file, outlining a specific land parcel or boundary area with high coordinate precision. The shape is rendered as a MultiPolygon, with vertices tracing a detailed, irregular perimeter typical of cadastral or survey data. The minimal, line-only representation emphasizes the geometry’s raw form, highlighting the boundary’s complexity and the spatial distribution of the region without additional contextual layers. The visualization focuses on the raw geographic footprint, making the coordinate data the primary visual element.

DDylanFM

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

59% match

Loading thumbnail…

Gist 1a7ae0708d7a8ee9e113f13654d39a4e

This example visualizes a single geographic polygon representing an outline near Denver, Colorado, derived from a GeoJSON FeatureCollection. It shows the shape’s boundary as a closed loop, with coordinates defining a series of vertices that trace an irregular, elongated region. The rendering relies on a mapping library that processes GeoJSON data, likely using `d3.geoPath` to project the polygon onto a canvas or SVG, while the precise coordinate pairs (longitude and latitude) suggest the use of a geographic projection. The source gist provides the raw GeoJSON, and the visualization emphasizes the polygon’s spatial extent without additional layers or interaction.

WWill Carter

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

58% match

Loading thumbnail…

Overly complicated way of "pick a postcode that is a) inside the (multi-)polygon and b) far away from edges"

A static map highlights postcodes within a chosen administrative boundary by rendering the polygon's internal buffer and displaying point locations that fall inside it. The SQL query first simplifies the largest polygon part, then computes a buffer distance based on the average bounding box dimensions. It uses PostGIS functions like `st_buffer`, `st_simplify`, and `st_within` to filter UK postcode points from the `uk_geo_utils_onspd` dataset, returning a single matching postcode and its geometry. The result shows only the buffered region and the selected point, not the original boundary.

GGeoWill

57% match