cdb_latlng

100%

✓ Published0🌍 Public

Last edited Apr 28, 2014

Created on Apr 28, 2014

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.

AI-generated description

Similar vizzes

Loading thumbnail…

parenthesis enclosed NYC open data lat/lon to the_geom

A SQL snippet converts NYC Open Data latitude/longitude coordinates stored as parenthesized text in a column into the `the_geom` geometry field for PostGIS. The example shows how to parse a string like `(lat, lon)` by stripping parentheses, splitting on commas, and reordering the values into `ST_MakePoint` before applying `ST_SetSRID` with EPSG:4326. This enables spatial queries and map rendering from raw tabular coordinate data. The command targets a placeholder table and column, making it adaptable to any dataset with similar text-encoded points.

Aandrewxhill

72% 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…

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

67% match

Loading thumbnail…

First pass at a simple point clustering algo for plpgsql

This example demonstrates a PostgreSQL/PostGIS clustering algorithm for point data, grouping nearby features into cluster IDs with sizes based on geographic distance at a given zoom level. The function `axh_cdb_row_cluster` iterates over rows, expands a search radius using `CDB_XYZ_Resolution`, and aggregates IDs via `array_agg`. It relies on `ST_Distance` with geography casts and plpgsql loops, returning a table of cluster identifiers. The code shows an iterative, resolution-adaptive approach rather than a visualization library, focusing on spatial SQL logic.

Aandrewxhill

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

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

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

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

65% match