PlpgSQL to cross dateline when drawing two points

100%

PlpgSQL to cross dateline when drawing two points

✓ Published0🌍 Public

Last edited Oct 28, 2014

Created on Aug 3, 2014

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.

AI-generated description

Similar vizzes

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

89% match

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

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

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

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

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

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

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

64% match