Model / geo · All operations

Work with geography

Geocode, spatial operations on geometry, grid cells, routing.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the h3 extension.

Shape of the result
one row out per row in, with columns added or changed
Level
needs a DuckDB extension

The DuckDB step

Hexagonal cell for a point

It reads the customers table the proofs share. Create it first:

SQL
CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
INSTALL h3 FROM community; LOAD h3;
CREATE TEMP TABLE result AS
SELECT customer, h3_latlng_to_cell_string(lat, lon, 5) AS cell, h3_get_resolution(h3_latlng_to_cell(lat, lon, 5)) AS resolution,
       h3_latlng_to_cell_string(lat + 0.0001, lon, 5) AS cell_nearby FROM customers;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(DISTINCT cell) = 4 AND min(resolution) = 5 AND max(resolution) = 5 AND count(*) FILTER (WHERE cell = cell_nearby) = 4 FROM result)) THEN 'as expected' ELSE error('four cities did not fall in four cells at resolution 5, or a point ten metres away fell in another cell') END AS verdict;
  • It needs the h3 extension.
  • The statement checks its own result and raises an error when it is wrong: “four cities did not fall in four cells at resolution 5, or a point ten metres away fell in another cell”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Bin points into grid cells

Assign points to geohash or grid cells, or build a grid or density surface over an area.

Spatial function libraries

Families of spatial functions and extensions that cover many of these tasks at once.

Read parts of a geometry

Extract coordinates, points, sub-geometries, bounding boxes, type or text and binary forms of a geometry.

Test how shapes relate

Return true or false for whether two geometries intersect, contain, touch or lie within a distance of each other.

Change or set the coordinate system

Reproject geometry into another coordinate reference system, assign one, or swap X and Y.

Simplify, smooth or repair shapes

Reduce, round off or fix the vertices of a geometry without changing what it represents.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (17)
  • Bufferread 2026-09-27

    Expand or contract a polygon or polyline by a set distance

  • Create Pointsread 2026-09-27

    Create point spatial objects from X and Y coordinate fields

  • Distanceread 2026-09-27

    Compute point-to-point, point-to-edge or drive distance between two spatial objects

  • Generalizeread 2026-09-27

    Reduce the node count of a polygon or polyline with Ramer-Douglas-Peucker

  • Heat Mapread 2026-09-27

    Generate polygons for levels of density of point records over an area

  • Make Gridread 2026-09-27

    Create a grid of cells over the extent of input spatial objects

  • Non Overlap Drivetimeread 2026-09-27

    Create drive-time trade areas for a point file that do not overlap

  • Poly-Buildread 2026-09-27

    Draw a polygon or polyline through a group of points in a given order

  • Poly-Splitread 2026-09-27

    Split polygons or polylines into component points, lines or regions

  • Reverse Geocoderread 2026-09-27

    Turn latitude and longitude into an address through the TomTom reverse geocoding API

  • Smoothread 2026-09-27

    Round the angles of a polygon or polyline by adding nodes with a B-spline

  • Spatial Inforead 2026-09-27

    Extract area, length, centroid, bounding box, point count and type from a spatial object

  • Spatial Processread 2026-09-27

    Combine or cut spatial objects: intersection, difference, combine, inverse

  • Street Geocoderread 2026-09-27

    Geocode a multi-line address to a point by address interpolation

  • Trade Arearead 2026-09-27

    Create regions around points by radius or drive time

  • US Geocoderread 2026-09-27

    Geocode a US customer file by chaining CASS, Street Geocoder and ZIP9 Coder

  • US ZIP9 Coderread 2026-09-27

    Append latitude, longitude and census geography for US ZIP+4 codes

Apache NiFi (3)
  • GeoEnrichIPread 2026-09-27

    Look up geolocation for an IP address in a MaxMind database and add it to attributes

  • GeoEnrichIPRecordread 2026-09-27

    Record-oriented IP geolocation lookup against a MaxMind database

  • GeohashRecordread 2026-09-27

    Encode latitude and longitude to a geohash, or decode a geohash, per record

Bruin (1)
dbt-utils (1)
  • haversine_distanceread 2026-09-27

    Compute great-circle distance between two lat/lon points in miles or kilometres

DuckDB (1)
Duckle (13)
  • Clip Geometryread 2026-09-25

    two-input overlay keeping the part of each geometry inside a clip layer

  • Create Geometryread 2026-09-25

    build a geometry column from X/Y, WKT or WKB

  • Define Projectionread 2026-09-25

    assign a CRS without moving coordinates

  • Erase Geometryread 2026-09-25

    two-input overlay subtracting an erase layer with ST_Difference

  • Flip Coordinatesread 2026-09-25

    swap X and Y of every vertex with ST_FlipCoordinates

  • Repair Geometryread 2026-09-25

    repair invalid geometries with ST_MakeValid

  • Reproject Geometryread 2026-09-25

    reproject a geometry column with ST_Transform

  • Spatial Arearead 2026-09-25

    area of each polygon, planar or spheroid by CRS

  • Spatial Bufferread 2026-09-25

    buffered geometry around each row with ST_Buffer

  • Spatial Distanceread 2026-09-25

    distance from each row to a target geometry, planar or spheroid by CRS

  • Spatial Intersectsread 2026-09-25

    boolean column: does the row intersect a target geometry

  • Spatial Lengthread 2026-09-25

    length of each line, planar or spheroid by CRS

  • Spatial Perimeterread 2026-09-25

    perimeter of each polygon, planar or spheroid by CRS

Ibis (55)
  • GeoArearead 2026-09-27

    Area of a geometry

  • GeoAsBinaryread 2026-09-27

    Well-Known Binary form of a geometry, without its SRID

  • GeoAsEWKBread 2026-09-27

    Extended Well-Known Binary form of a geometry, with its SRID

  • GeoAsEWKTread 2026-09-27

    Extended Well-Known Text form of a geometry, with its SRID

  • GeoAsTextread 2026-09-27

    Well-Known Text form of a geometry, without its SRID

  • GeoAzimuthread 2026-09-27

    Angle in radians of the line running between two points

  • GeoBufferread 2026-09-27

    Grow a geometry outward by a radius

  • GeoCentroidread 2026-09-27

    Geometric centre of a geometry

  • GeoContainsread 2026-09-27

    Test whether the first geometry contains the second

  • GeoContainsProperlyread 2026-09-27

    Test containment where the two geometries share no boundary points

  • GeoConvertread 2026-09-27

    Reproject a geometry from a source CRS to a target CRS

  • GeoCoveredByread 2026-09-27

    Test that no point of the first geometry lies outside the second

  • GeoCoversread 2026-09-27

    Test that no point of the second geometry lies outside the first

  • GeoCrossesread 2026-09-27

    Test whether two geometries share some but not all interior points

  • GeoDFullyWithinread 2026-09-27

    Test whether two geometries lie entirely within a distance of each other

  • GeoDWithinread 2026-09-27

    Test whether two geometries come within a distance of each other

  • GeoDifferenceread 2026-09-27

    Part of the first geometry the second does not cover

  • GeoDisjointread 2026-09-27

    Test whether two geometries do not intersect

  • GeoDistanceread 2026-09-27

    Minimum distance between two geometries

  • GeoEndPointread 2026-09-27

    Last point of a LineString

  • GeoEnveloperead 2026-09-27

    Bounding box of a geometry

  • GeoEqualsread 2026-09-27

    Test whether two geometries describe the same shape

  • GeoFlipCoordinatesread 2026-09-27

    Swap the X and Y coordinates of a geometry

  • GeoGeometryNread 2026-09-27

    Nth geometry of a multi-geometry

  • GeoGeometryTyperead 2026-09-27

    Type name of a geometry

  • GeoIntersectionread 2026-09-27

    Geometry that two geometries have in common

  • GeoIntersectsread 2026-09-27

    Test whether two geometries intersect in 2D

  • GeoIsValidread 2026-09-27

    Test whether a geometry is well-formed

  • GeoLengthread 2026-09-27

    Length of a geometry

  • GeoLineLocatePointread 2026-09-27

    Fraction of the way along a line at which a point falls

  • GeoLineMergeread 2026-09-27

    Merge a MultiLineString into a LineString

  • GeoLineSubstringread 2026-09-27

    Clip the section of a LineString between two fractions of its length

  • GeoMaxDistanceread 2026-09-27

    Largest 2D distance between two geometries

  • GeoNPointsread 2026-09-27

    Number of points in a geometry

  • GeoNRingsread 2026-09-27

    Number of rings in a polygon or multipolygon

  • GeoOrderingEqualsread 2026-09-27

    Test whether two geometries are equal and list their points in the same order

  • GeoOverlapsread 2026-09-27

    Test whether same-dimension geometries overlap without one containing the other

  • GeoPerimeterread 2026-09-27

    Perimeter of a geometry

  • GeoPointread 2026-09-27

    Build a point from X and Y values

  • GeoPointNread 2026-09-27

    Nth point of a LineString

  • GeoSRIDread 2026-09-27

    Spatial reference identifier of a geometry

  • GeoSetSRIDread 2026-09-27

    Assign a spatial reference identifier to a geometry

  • GeoSimplifyread 2026-09-27

    Simplify a geometry within a tolerance

  • GeoStartPointread 2026-09-27

    First point of a LineString

  • GeoTouchesread 2026-09-27

    Test whether geometries share a boundary point but no interior points

  • GeoTransformread 2026-09-27

    Reproject a geometry into a new SRID

  • GeoUnaryUnionread 2026-09-27

    Aggregate a column of geometries into their union

  • GeoUnionread 2026-09-27

    Union of two geometries

  • GeoWithinread 2026-09-27

    Test whether the first geometry lies completely inside the second

  • GeoXread 2026-09-27

    X coordinate of a point

  • GeoXMaxread 2026-09-27

    Largest X of a geometry's bounding box

  • GeoXMinread 2026-09-27

    Smallest X of a geometry's bounding box

  • GeoYread 2026-09-27

    Y coordinate of a point

  • GeoYMaxread 2026-09-27

    Largest Y of a geometry's bounding box

  • GeoYMinread 2026-09-27

    Smallest Y of a geometry's bounding box

KNIME Analytics Platform (2)
Pentaho Data Integration (1)
  • MaxMind GeoIP Lookupread 2026-09-27

    Look up an IPv4 address in a MaxMind database, adding geography, ISP or organisation

SQLMesh (1)
  • @HAVERSINE_DISTANCEread 2026-09-27

    Compute haversine distance between two lat/lon points in miles or kilometres

Tableau Prep (1)
  • Spatial functions (group)read 2026-09-27

    13 functions: MAKEPOINT, BUFFER, DISTANCE, INTERSECTS, AREA; build and measure geometry

Talend (4)
  • tAddLocationFromIPread 2026-09-27

    Look up the country, region, city and coordinates of IP addresses in an IP location database file

  • tGoogleAddressRowread 2026-09-27

    Geocode addresses to coordinates and other geographic details through the Google Places API

  • tGoogleGeocoderread 2026-09-27

    Geocode addresses to coordinates through the Google Places API

  • tGoogleMapLookupread 2026-09-27

    Look up detailed geographic information for coordinates through the Google Places API

VisiData (1)

Background

A typical geoprocessing operation in a geographic information system (GIS) takes an input dataset, operates on it and returns the result as an output dataset.[1] Common geoprocessing operations include feature overlay, feature selection and analysis, topology processing, raster processing and data conversion.[1] Vector GIS work typically involves map overlay, combining map layers by predefined rules, and buffering, finding regions within a distance of features.[2]

When to use it

A spatial reference system (SRS), or coordinate reference system (CRS), is a framework for precisely measuring locations on, or relative to, the surface of Earth as coordinates.[3] A particular SRS specification comprises a choice of Earth ellipsoid, horizontal datum, map projection (except in the geographic coordinate system), origin point and unit of measure.[3] Thousands of coordinate systems have been specified for use around the world or in specific regions, which makes transformations between them necessary.[3]

In PostGIS, a geodetic SRS uses angular coordinates (longitude and latitude), while a projected SRS flattens the Earth onto a plane so that distance, area and angle can be measured directly.[4] PostGIS functions that process pairs of geometries, such as overlay and relationship functions, require both inputs to be in the same spatial reference system.[4] Before measuring or combining shapes, find out which coordinate system each column is in, and bring them into one system before anything else. (Meridian's view)

The method

Simple Features, officially Simple Feature Access, is a set of standards for storing and accessing geographic features made of mostly two-dimensional geometries such as points, lines and polygons.[5] Simple Features is formalised by both the Open Geospatial Consortium (OGC) and the International Organization for Standardization (ISO).[5] The Simple Features specification defines spatial predicates and several spatial operators that generate new geometries from existing geometries.[5]

In DuckDB, the GEOMETRY type conceptually follows the core data model of the Simple Features standard and can represent seven types of shape.[6] In DuckDB, GEOMETRY became a built-in data type in version 1.5, but almost all of the functions for calculating distances, areas and intersections are still part of the spatial extension.[6]

Compared with the alternatives

In PostGIS, functions on the geometry type calculate areas, distances, lengths and intersections with straight lines and Cartesian mathematics, which is faster but inaccurate on the Earth's spheroidal surface.[4] In PostGIS, functions on the geography type calculate with arcs on the sphere, where the shortest path between two points is a great circle arc.[4] In PostGIS, the geography type has fewer functions defined on it than the geometry type, and those that are defined take more CPU time to run.[4] In PostGIS, a suitable projection and the geometry type may be the best solution for data contained in a small area, while the geography type may suit data that is global or covers a continental region.[4]

Sources

  1. [1]Geographic information system - Wikipediasecondary source
  2. [2]Spatial analysis - Wikipediasecondary source
  3. [3]Spatial reference system - Wikipediasecondary source
  4. [4]Chapter 4. Data Managementdocumentation
  5. [5]Simple Features - Wikipediasecondary source
  6. [6]Geometry Data Type – DuckDBdocumentation