processing

CORE
This module contains functions that computes new geographies by processing existing geographies.
ST_DELAUNAYLINES
ST_DELAUNAYLINES(points)
Description
Calculates the Delaunay triangulation of the points provided. An array of linestrings in GeoJSON format is returned.
points: ARRAY array of points in GeoJSON format casted to STRING.
Due to technical limitations of the underlying libraries used, the input points' coordinates are truncated to 5 decimal places in order to avoid problems that happen with close but distinct input points. This limits the precision of the results and can alter slightly the position of the resulting polygons (about 1 meter). This can also result in some points being merged together, so that fewer polygons than expected may result.
Return type
ARRAY
Examples
SELECT CARTO.CARTO.ST_DELAUNAYLINES(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  )
);
--  "{\"coordinates\":[[-75.833,39.284],[-75.221,39.125],[-75.6,39.984],[-75.833,39.284]],\"type\":\"LineString\"}",
--  "{\"coordinates\":[[-75.833,39.284],[-75.521,39.325],[-75.6,39.984],[-75.833,39.284]],\"type\":\"LineString\"}",
--  "{\"coordinates\":[[-75.833,39.284],[-75.521,39.325],[-75.221,39.125],[-75.833,39.284]],\"type\":\"LineString\"}",
--  "{\"coordinates\":[[-75.521,39.325],[-75.221,39.125],[-75.6,39.984],[-75.521,39.325]],\"type\":\"LineString\"}"
Note that if some points are very close together (about 1 meter) they may be merged and the result may have fewer lines than expected, for example these four points result in two lines:
SELECT CARTO.CARTO.ST_DELAUNAYLINES(
  ARRAY_CONSTRUCT(
    '{"coordinates":[4.1829523,43.6347910],"type":"Point"}',
    '{"coordinates":[4.1829967,43.6347137],"type":"Point"}',
    '{"coordinates":[4.1829955,43.6347143],"type":"Point"}',
    '{"coordinates":[4.1829321,43.6347500],"type":"Point"}'
  )
);
-- [
--   "{\"coordinates\":[[4.18293,43.63475],[4.183,43.63471],[4.18295,43.63479],[4.18293,43.63475]],\"type\":\"LineString\"}"
-- ]
ST_DELAUNAYLINES
ST_DELAUNAYLINES(points)
Description
Calculates the Delaunay triangulation of the points provided. An array of polygons in GeoJSON format is returned.
points: ARRAY array of points in GeoJSON format casted to STRING.
Due to technical limitations of the underlying libraries used, the input points' coordinates are truncated to 5 decimal places in order to avoid problems that happen with close but distinct input points. This limits the precision of the results and can alter slightly the position of the resulting polygons (about 1 meter). This can also result in some points being merged together, so that fewer polygons than expected may result.
Return type
ARRAY
Examples
SELECT CARTO.CARTO.ST_DELAUNAYPOLYGONS(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  )
);
-- "{\"coordinates\":[[[-75.833,39.284],[-75.221,39.125],[-75.6,39.984],[-75.833,39.284]]],\"type\":\"Polygon\"}",
-- "{\"coordinates\":[[[-75.833,39.284],[-75.521,39.325],[-75.6,39.984],[-75.833,39.284]]],\"type\":\"Polygon\"}",
-- "{\"coordinates\":[[[-75.833,39.284],[-75.521,39.325],[-75.221,39.125],[-75.833,39.284]]],\"type\":\"Polygon\"}",
-- "{\"coordinates\":[[[-75.521,39.325],[-75.221,39.125],[-75.6,39.984],[-75.521,39.325]]],\"type\":\"Polygon\"}"
Note that if some points are very close together (about 1 meter) they may be merged and the result may have fewer triangles than expected, for example these four points result in one triangle:
SELECT CARTO.CARTO.ST_DELAUNAYPOLYGONS(
  ARRAY_CONSTRUCT(
    '{"coordinates":[4.1829523,43.6347910],"type":"Point"}',
    '{"coordinates":[4.1829967,43.6347137],"type":"Point"}',
    '{"coordinates":[4.1829955,43.6347143],"type":"Point"}',
    '{"coordinates":[4.1829321,43.6347500],"type":"Point"}'
  )
);
-- [
--   "{\"coordinates\":[[[4.18293,43.63475],[4.183,43.63471],[4.18295,43.63479],[4.18293,43.63475]]],\"type\":\"Polygon\"}"
-- ]
ST_POLYGONIZE
ST_POLYGONIZE(lines)
Description
Creates a set of polygons from geographies which contain lines that represent the their edges.
lines: ARRAY array of lines which represent the polygons edges in GeoJSON format casted to STRING.
Return type
ARRAY
Example
SELECT CARTO.CARTO.ST_POLYGONIZE(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(
      ST_GEOGRAPHYFROMWKT(
        'LINESTRING(-74.5366825512491 43.6889777784079, -70.7632814028801 42.9679602005825, -70.2005131676838 43.8455720129728, -74.5366825512491 43.6889777784079)'
      )
    )::STRING,
    ST_ASGEOJSON(
      ST_GEOGRAPHYFROMWKT(
        'LINESTRING(-73.9704330709753 35.3953164724094, -72.514071762468 36.5823995124737, -73.3262122666779 41.2706174323278, -73.9704330709753 35.3953164724094)'
      )
    )::STRING
  )
);
-- "{\"coordinates\":[[[-74.5366825512491,43.6889777784079],[-70.7632814028801,42.9679602005825],[-70.2005131676838,43.8455720129728],[-74.5366825512491,43.6889777784079]]],\"type\":\"Polygon\"}",
-- "{\"coordinates\":[[[-73.9704330709753,35.3953164724094],[-72.514071762468,36.5823995124737],[-73.3262122666779,41.2706174323278],[-73.9704330709753,35.3953164724094]]],\"type\":\"Polygon\"}"
ST_VORONOILINES
ST_VORONOILINES(points [, bbox])
Description
Calculates the Voronoi diagram of the points provided. An array of linestrings in GeoJSON format is returned.
points: ARRAY array of points in GeoJSON format casted to STRING.
bbox (optional): ARRAY clipping bounding box. By default the [-180,-85,180,85] bbox will be used.
Due to technical limitations of the underlying libraries used, the input points' coordinates are truncated to 5 decimal places in order to avoid problems that happen with close but distinct input points. This limits the precision of the results and can alter slightly the position of the resulting lines (about 1 meter). This can also result in some points being merged together, so that fewer lines than input points may result.
Return type
ARRAY
Examples
SELECT CARTO.CARTO.ST_VORONOILINES(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  )
);
--  "{\"type\":\"LineString\",\"coordinates\":[[-180,74.34550785714367],[-75.72047348298037,39.63532260219203],[-75.6178875502008,38.854668674698786],[-107.79581617647065,-85],[-180,-85],[-180,74.34550785714367]]}",
--  "{\"type\":\"LineString\",\"coordinates\":[[27.59130606860346,85],[-75.04333534909291,39.716496976360624],[-75.72047348298037,39.63532260219203],[-180,74.34550785714367],[-180,85],[27.59130606860346,85]]}",
--  "{\"type\":\"LineString\",\"coordinates\":[[-107.79581617647065,-85],[-75.6178875502008,38.854668674698786],[-75.04333534909291,39.716496976360624],[27.59130606860346,85],[180,85],[180,-85],[-107.79581617647065,-85]]}",
--  "{\"type\":\"LineString\",\"coordinates\":[[-75.72047348298037,39.63532260219203],[-75.04333534909291,39.716496976360624],[-75.6178875502008,38.854668674698786],[-75.72047348298037,39.63532260219203]]}"
SELECT CARTO.CARTO.ST_VORONOILINES(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  ),
  ARRAY_CONSTRUCT(-76.0, 35.0, -70.0, 45.0)
);
--  "{\"type\":\"LineString\",\"coordinates\":[[-76,39.728365000000004],[-75.72047348298037,39.63532260219203],[-75.6178875502008,38.854668674698786],[-76,37.38389622641511],[-76,39.728365000000004]]}",
--  "{\"type\":\"LineString\",\"coordinates\":[[-70,41.941670547147794],[-75.04333534909291,39.716496976360624],[-75.72047348298037,39.63532260219203],[-76,39.728365000000004],[-76,45],[-70,45],[-70,41.941670547147794]]}",
--  "{\"type\":\"LineString\",\"coordinates\":[[-76,37.38389622641511],[-75.6178875502008,38.854668674698786],[-75.04333534909291,39.716496976360624],[-70,41.941670547147794],[-70,35],[-76,35],[-76,37.38389622641511]]}",
--  "{\"type\":\"LineString\",\"coordinates\":[[-75.72047348298037,39.63532260219203],[-75.04333534909291,39.716496976360624],[-75.6178875502008,38.854668674698786],[-75.72047348298037,39.63532260219203]]}"
Note that if some points are very close together (about 1 meter) they may be merged and the result may have fewer lines than points, for example these three points result in two lines
SELECT CARTO.CARTO.ST_VORONOILINES(
  ARRAY_CONSTRUCT(
    '{"coordinates":[4.1829523,43.6347910],"type":"Point"}',
    '{"coordinates":[4.1829967,43.6347137],"type":"Point"}',
    '{"coordinates":[4.1829955,43.6347143],"type":"Point"}'
  ),
  ARRAY_CONSTRUCT(4.182, 43.634, 4.183, 43.635)
);
-- [
--   "{\"type\":\"LineString\",\"coordinates\":[[4.183,43.634765625],[4.182,43.63414062499997],[4.182,43.635],[4.183,43.635],[4.183,43.634765625]]}",
--   "{\"type\":\"LineString\",\"coordinates\":[[4.182,43.63414062499997],[4.183,43.634765625],[4.183,43.634],[4.182,43.634],[4.182,43.63414062499997]]}"
-- ]
ST_VORONOIPOLYGONS
ST_VORONOIPOLYGONS(points [, bbox])
Description
Calculates the Voronoi diagram of the points provided. An array of polygons in GeoJSON format is returned.
points: ARRAY array of points in GeoJSON format casted to STRING.
bbox (optional): ARRAY clipping bounding box. By default the [-180,-85,180,85] bbox will be used.
Due to technical limitations of the underlying libraries used, the input points' coordinates are truncated to 5 decimal places in order to avoid problems that happen with close but distinct input points. This limits the precision of the results and can alter slightly the position of the resulting polygons (about 1 meter). This can also result in some points being merged together, so that fewer polygons than input points may result.
Return type
ARRAY
Examples
SELECT CARTO.CARTO.ST_VORONOIPOLYGONS(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  )
);
--  "{\"type\":\"Polygon\",\"coordinates\":[[[-180,74.34550785714367],[-75.72047348298037,39.63532260219203],[-75.6178875502008,38.854668674698786],[-107.79581617647065,-85],[-180,-85],[-180,74.34550785714367]]]}",
--  "{\"type\":\"Polygon\",\"coordinates\":[[[27.59130606860346,85],[-75.04333534909291,39.716496976360624],[-75.72047348298037,39.63532260219203],[-180,74.34550785714367],[-180,85],[27.59130606860346,85]]]}",
--  "{\"type\":\"Polygon\",\"coordinates\":[[[-107.79581617647065,-85],[-75.6178875502008,38.854668674698786],[-75.04333534909291,39.716496976360624],[27.59130606860346,85],[180,85],[180,-85],[-107.79581617647065,-85]]]}",
--  "{\"type\":\"Polygon\",\"coordinates\":[[[-75.72047348298037,39.63532260219203],[-75.04333534909291,39.716496976360624],[-75.6178875502008,38.854668674698786],[-75.72047348298037,39.63532260219203]]]}"
SELECT CARTO.CARTO.ST_VORONOIPOLYGONS(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  ),
  ARRAY_CONSTRUCT(-76.0, 35.0, -70.0, 45.0)
);
--  "{\"type\":\"Polygon\",\"coordinates\":[[[-76,39.728365000000004],[-75.72047348298037,39.63532260219203],[-75.6178875502008,38.854668674698786],[-76,37.38389622641511],[-76,39.728365000000004]]]}",
--  "{\"type\":\"Polygon\",\"coordinates\":[[[-70,41.941670547147794],[-75.04333534909291,39.716496976360624],[-75.72047348298037,39.63532260219203],[-76,39.728365000000004],[-76,45],[-70,45],[-70,41.941670547147794]]]}",
--  "{\"type\":\"Polygon\",\"coordinates\":[[[-76,37.38389622641511],[-75.6178875502008,38.854668674698786],[-75.04333534909291,39.716496976360624],[-70,41.941670547147794],[-70,35],[-76,35],[-76,37.38389622641511]]]}",
--  "{\"type\":\"Polygon\",\"coordinates\":[[[-75.72047348298037,39.63532260219203],[-75.04333534909291,39.716496976360624],[-75.6178875502008,38.854668674698786],[-75.72047348298037,39.63532260219203]]]}"
Note that if some points are very close together (about 1 meter) they may be merged and the result may have fewer polygons than points, for example these three points result in two polygons:
SELECT CARTO.CARTO.ST_VORONOIPOLYGONS(
  ARRAY_CONSTRUCT(
    '{"coordinates":[4.1829523,43.6347910],"type":"Point"}',
    '{"coordinates":[4.1829967,43.6347137],"type":"Point"}',
    '{"coordinates":[4.1829955,43.6347143],"type":"Point"}'
  ),
  ARRAY_CONSTRUCT(4.182, 43.634, 4.183, 43.635)
);
-- [
--  "{\"type\":\"Polygon\",\"coordinates\":[[[4.183,43.634765625],[4.182,43.63414062499997],[4.182,43.635],[4.183,43.635],[4.183,43.634765625]]]}",
--   "{\"type\":\"Polygon\",\"coordinates\":[[[4.182,43.63414062499997],[4.183,43.634765625],[4.183,43.634],[4.182,43.634],[4.-- 182,43.63414062499997]]]}"
-- ]
Additional examples
​Analyzing store location coverage using a Voronoi diagram​
placekey
quadbin
Last modified 6mo ago