Finding longest path between two geoms

100%

Finding longest path between two geoms

✓ Published0🌍 Public

Last edited Jan 7, 2012

Created on Jan 7, 2012

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.

AI-generated description

Similar vizzes

Loading thumbnail…

Longer single line text

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.

Aandrewxhill

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

64% match

Loading thumbnail…

PLPGSQL Function to cross the dateline

This example provides a PL/pgSQL function, `AXH_CrossDateLine`, that constructs a valid geometry for a line crossing the antimeridian in Web Mercator projection (EPSG:3857). It takes two input points, identifies the western and easternmost coordinates, and calculates the latitude where the line intersects the projection’s edge using proportional math. The function then returns a multi-part line by splitting the path at the ±20037508.34 meter boundaries, using PostGIS functions like `ST_MakeLine`, `ST_SetSRID`, and `ST_Collect`. This utility ensures dateline-crossing routes render correctly in web maps.

Aandrewxhill

63% match

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

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

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

58% match

Loading thumbnail…

cdb_latlng

This example defines a set of PostgreSQL functions for converting latitude and longitude values into spatial geometries. It shows how to construct valid 4326-point geometries from numeric, floating-point, or text inputs, including support for comma-style decimal separators. The code uses PostGIS functions such as `ST_SetSRID` and `ST_MakePoint` to build the geometry, along with custom helpers like `CDB_DecimalDegreeLiteral` for parsing text coordinates. It handles both individual lat/lng arguments and a single space-separated coordinate string, making it a flexible utility for geospatial data preprocessing in SQL.

Aandrewxhill

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

56% match