ruk·si

🐘 PostgreSQL
Paths

Updated at 2016-01-01 19:55

Paths are represented by lists of connected points.

CREATE TABLE trails (
    name    text,
    route   path
);

You can enclose paths with () or []

  • () = closed, the first and last points are considered to be connected
  • [] = open, the first and last points are not connected
  • if not given, () closed path is assumed
INSERT INTO trails VALUES (
    'Hiking Route #1',
    '[
        (37.172,            -122.22261666667),
        (37.171616666667,   -122.22385),
        (37.1735,           -122.2236),
        (37.175416666667,   -122.223),
        (37.1758,           -122.22378333333)
    ]'
);

INSERT INTO trails VALUES (
    'Hiking Route #2',
    '(
        (37.172,            -122.22261666667),
        (37.171616666667,   -122.22385),
        (37.1735,           -122.2236),
        (37.175416666667,   -122.223),
        (37.1758,           -122.22378333333)
    )'
);

Paths, like most geographic data types, can be used with many helpful functions.

SELECT
    name,
    length(route),
    isopen(route),
    isclosed(route),
    npoints(route),     -- number of point
    area(route)         -- only closed paths have area
FROM trails;
-- "Hiking Route #1";   0.00607186960352518;  t;  f;  5;
-- "Hiking Route #2";   0.0100469307471545;   f;  t;  5;  1.24111102195457e-06

Sources