Longer single line text

100%

Longer single line text

✓ Published0🌍 Public

Last edited Oct 11, 2012

Created on Oct 11, 2012

This example uses PostGIS’s ST_GeometryFromText function to load a single LINESTRING of GPS track points and render it as a path. The line is drawn from over two hundred coordinate pairs tracing a route through southern Idaho, with the SQL query creating the geometric line feature. The visualization highlights how raw coordinate data can be transformed into a continuous geographic path, emphasizing the shape and direction of the route. The author, andrewxhill, sourced this from a gist, demonstrating a minimal SQL approach to spatial data handling.

AI-generated description

Similar vizzes

Loading thumbnail…

New SQL statement for bigline generation

This SQL snippet constructs a long, detailed line geometry from a sequence of latitude and longitude coordinates using PostGIS. The example demonstrates how to define a complex LINESTRING with over 180 vertices in a single SQL statement, likely for generating a "big line" for mapping or geospatial analysis. The path traced by the coordinates suggests a real-world route or boundary, showcasing the ability to handle dense, high-resolution geographic data directly within a database query.

Aandrewxhill

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

71% match

Loading thumbnail…

Grid snapping only in SQL!

This example demonstrates how to snap a hand-drawn path to a street grid using only SQL and PostGIS spatial functions. It takes an input line geometry, finds the nearest high-order road segments from New York OpenStreetMap data, and iteratively interpolates points along those roads to create a new, grid-aligned linestring. The process uses `ST_PointN`, `ST_Line_Interpolate_Point`, `line_locate_point`, and the `ORDER BY <->` operator for nearest-neighbor queries, building the snapped result by alternating between forward and reverse passes.

Aandrewxhill

70% match

Loading thumbnail…

Gist b2644a6ec6b1fd1a95ef87d5a93ec93c

This example visualizes a GPS track as a three-dimensional path using a MultiLineString GeoJSON feature. The data, sourced from a gist by FergusDevelopmentLLC, is rendered with altitude encoded as the third coordinate in each vertex. The visualization highlights the elevation changes along the path through a continuous line, with the points tracing a looping route. The use of 3D coordinates allows the viewer to perceive the terrain's vertical profile alongside its horizontal extent, making it useful for analyzing elevation gain, slope, and path shape in a compact, geographic context.

WWill Carter

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

68% match

Loading thumbnail…

CartoDB SQL tricks: Various things that as a SQL-newbie I had to search for >10 minutes to find answers for.

This example demonstrates a collection of SQL recipes for solving common spatial data problems in CartoDB, ranging from merging neighboring county polygons with ST_Union to creating great circle lines via ST_MakeLine and ST_Segmentize. It shows practical techniques like updating geometries with conditional CASE statements, extracting coordinates with ST_X and ST_Y, stripping HTML tags using regexp_replace, and formatting zip codes with to_char. The code also covers performance strategies for large updates and querying spatial relationships with st_contains. Each snippet targets PostGIS functions within the CartoDB platform, emphasizing data preparation and geometric transformations.

Aandrewbt

66% match

Loading thumbnail…

Link two datasets into lines between nearest points

The visualization connects two point datasets by drawing a line between each point in one set and its nearest neighbor in the other. A nested SQL query computes the closest geometry for every row using the `<->` distance operator and `ST_MakeLine` to construct the connecting lines. The map renders the resulting `the_geom_webmercator` lines in CartoDB, pairing Citibike stations with points from a second dataset. The approach highlights spatial proximity relationships through direct, nearest-neighbor linkages.

Aandrewxhill

65% match

Loading thumbnail…

Finding longest path between two geoms

This example computes the longest straight-line distance between two geographic geometries: a point near the UK coast and a line formed by the union of two points further south. It uses PostGIS functions `ST_LongestLine` and `ST_SetSRID` to construct the geometries and find the connecting line, then `ST_Distance_Sphere` calculates the great-circle distance in meters. The SQL query reveals the underlying spatial computation, showing how PostGIS can determine the maximum separation between a point and a multi-point geometry on a spherical Earth model.

Aandrewxhill

65% match