Postgres/PostGIS query for buffer and spatial 'shifted' point

100%

Postgres/PostGIS query for buffer and spatial 'shifted' point

✓ Published0🌍 Public

Last edited Dec 29, 2020

Created on Dec 28, 2020

This example shows how PostGIS builds a composite set of geographic buffers and a projected point for use in a Tableau dashboard. It first creates a donut-shaped buffer by taking the difference between two concentric buffer polygons around a point defined by longitude and latitude. It then projects a new point three times the buffer distance away, rotates it 90 degrees, and buffers it into a small circle. The code uses `ST_MakePoint`, `ST_Buffer`, `ST_Difference`, `ST_Project`, and `st_collect` to assemble the result as a geography MultiPolygon.

AI-generated description

Similar vizzes

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

68% match

Loading thumbnail…

Creates a bounding circle from the_geom_webmercator on cartodb

This example shows a bounding circle generated around grouped point data, visualized as a single polygon overlay on a CartoDB map. It displays the spatial extent of each group by computing a circular buffer from the group’s centroid. The code uses PostGIS functions—`ST_Collect`, `ST_Envelope`, `ST_ConvexHull`, `ST_Centroid`, `ST_LongestLine`, and `ST_Buffer`—to derive the circle from the `the_geom_webmercator` column. The result is rendered as a geographic layer, with the circle’s radius determined by the longest distance from the centroid to the group’s convex hull.

Aandrewxhill

65% match

Loading thumbnail…

Gist 6114786

This example implements a private torque-style time-series visualization for geospatial data using PostgreSQL and PostGIS. It defines custom SQL functions that aggregate point features into web-Mercator grid cells, grouping counts by time steps and returning arrays of values and dates for rendering. The code uses `ST_SnapToGrid`, `CDB_XYZ_Resolution`, and `CDB_XYZ_Extent` to tile data at a given zoom level, with two variants: a standard response for instantaneous counts and a cumulative response that includes expiration dates for each cell. A metadata function returns the spatial bounds and time range from the source table, supporting both timestamped and numeric columns.

Aandrewxhill

64% match

Loading thumbnail…

Create an equal area grid over a buffer of points

This example visualizes a rectangular grid of points covering a 50-kilometer buffer around input locations, with each point representing the centroid of a grid cell in an equal-area projection. The SQL query first buffers the original NAD83 geometries, then unions and reprojects them into the Lambert equal-area projection (SRID 97314) to create an envelope, and finally generates a 1000-by-1000-meter grid using CartoDB’s `CDB_RectangleGrid` function. Each cell’s centroid is reprojected back to WGS84 (SRID 4326) for display, ensuring spatially uniform sampling across the buffered area.

Aandrewxhill

62% match

Loading thumbnail…

Gist 1353427

This example shows how to sum raster pixel values from a PostGIS database that fall within a specified multi-polygon region, using SQL queries to perform spatial intersection tests. The code defines a multi-polygon geometry and iterates over a 10×10 grid of pixel offsets, translating each pixel’s envelope and checking for intersection with the polygon. It uses PostGIS functions like `ST_Value`, `ST_Intersects`, `ST_Translate`, and `ST_MakeEnvelope` to calculate the total sum and count of intersecting raster cells, demonstrating a spatial aggregation workflow.

Aandrewxhill

62% match

Loading thumbnail…

SQL for building a dynamic topo

This example builds a dynamic topographic map from traffic data by buffering and dissolving point geometries into nested contour layers. It first generates a series of buffer rings around each data point, scales the car counts across nine distance levels, and then unions those buffers into isolines representing traffic intensity. The SQL query uses PostGIS functions like `ST_Transform`, `ST_Buffer`, and `ST_Union`, while a second pass outputs the original points. The result combines colored topo bands and overlaid points, with the visualization likely rendered using a web-mapping library that consumes the `the_geom_webmercator` geometries.

Aandrewxhill

61% match

Loading thumbnail…

PlpgSQL to cross dateline when drawing two points

This example demonstrates a PostGIS/PLpgSQL function that corrects lines crossing the 180-degree antimeridian when drawn on a web Mercator map. It takes two point geometries and constructs a MultiLineString that breaks the crossing segment at the map’s edge, inserting a vertical line at ±20037508.34 to create a continuous visual path. The code uses ST_Transform, ST_MakeLine, and ST_Collect to handle the geometry, and defines a custom aggregate (axh_crossdateline) to process ordered point sequences. The function expects SRID 3857 inputs and returns the adjusted line for seamless rendering.

Aandrewxhill

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

61% match