Values of set and span types can be converted to one another or converted to and from PostgreSQL range types using the function CAST or using the :: notation.
Convert a base value to a set, span, or span set value
base::{set,span,spanset}
set(base) → set
span(base) → span
spanset(base) → spanset
SELECT CAST(timestamptz '2001-01-01 08:00:00' AS tstzset);
-- {2001-01-01 08:00:00}
SELECT timestamptz '2001-01-01 08:00:00'::tstzspan;
-- [2001-01-01 08:00:00, 2001-01-01 08:00:00]
SELECT spanset(timestamptz '2001-01-01 08:00:00');
-- {[2001-01-01 08:00:00, 2001-01-01 08:00:00]}
Convert a set value to a span set value
set::spanset spanset(set) → spanset
SELECT spanset(tstzset '{2001-01-01 08:00:00, 2001-01-01 08:15:00, 2001-01-01 08:25:00}');
/* {[2001-01-01 08:00:00, 2001-01-01 08:00:00], [2001-01-01 08:15:00,
2001-01-01 08:15:00],
[2001-01-01 08:25:00, 2001-01-01 08:25:00]} */
Convert a geo set into the collection of its values
geomset::geometry geogset::geography geometry(geomset) → geometry geography(geogset) → geography
The collection takes its type from the types of the values alone, as the PostGIS function ST_Collect builds it from them, so a set of points, a set of one point included, answers a multipoint.
SELECT ST_AsText(geomset '{"Point(1 1)", "Point(2 2)"}'::geometry);
-- MULTIPOINT((1 1),(2 2))
SELECT ST_AsText(geomset '{"Point(1 1)"}'::geometry);
-- MULTIPOINT((1 1))
SELECT ST_AsText(geometry(geomset '{"Point(1 1)", "Linestring(0 0,1 1)"}'));
-- GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1))
Convert between a geometry and a geography
geomset::geogset geogset::geomset geogset(geomset) → geogset geomset(geogset) → geomset
A spatial value of any type converts to a geometry and to a geography, whether it is planar or geodetic. The conversion changes the type and never the coordinates, so a measure then follows the target: on the plane for a geometry, on the surface of the earth for a geography. As a geography, a value with no SRID has the SRID 4326 and a value in a projected reference system raises an error, as in PostGIS. The rule holds for every spatial type and every temporal one, each of which reaches the same conversion of its geometry or geography values.
SELECT ST_AsEWKT(geometry 'Point(1 2)'::geography);
-- SRID=4326;POINT(1 2)
SELECT asEWKT(geomset '{"Point(1 2)"}'::geogset);
-- SRID=4326;{"POINT(1 2)"}
SELECT asEWKT(geogset '{"Point(1 2)"}'::geomset);
-- SRID=4326;{"POINT(1 2)"}
SELECT geomset 'SRID=3857;{"Point(1 2)"}'::geogset;
-- ERROR: Only lon/lat coordinate systems are supported
Convert a span value to a span set value
span::spanset spanset(span) → spanset
SELECT floatspan '[1.5,2.5]'::floatspanset;
-- {[1.5, 2.5]}
SELECT tstzspan '[2001-01-01 08:00:00, 2001-01-01 08:30:00)'::tstzspanset;
-- {[2001-01-01 08:00:00, 2001-01-01 08:30:00)}
Convert a set or a span set into a span, ignoring the potential time gaps
{set,spanset}::span
span({set,spanset}) → span
SELECT span(dateset '{2001-01-01, 2001-01-03, 2001-01-05}');
-- [2001-01-01, 2001-01-06)
SELECT span(tstzspanset '{[2001-01-01, 2001-01-02), [2001-01-03, 2001-01-04)}');
-- [2001-01-01, 2001-01-04)
Convert a span value to and from a PostgreSQL range value
{span,range}::{range,span}
range(span) → range
span(range) → span
Notice that PostgreSQL range values accept empty ranges and ranges with infinite values, which are not allowed as span values in MobilityDB
SELECT intspan '[10, 20)'::int4range; -- [10,20) SELECT tstzspan '[2001-01-01 08:00:00, 2001-01-01 08:30:00)'::tstzrange; -- ["2001-01-01 08:00:00","2001-01-01 08:30:00") SELECT int4range '[10, 20)'::intspan; -- [10,20) SELECT int4range 'empty'::intspan; -- ERROR: Range cannot be empty SELECT int4range '[10,)'::intspan; -- ERROR: Range bounds cannot be infinite SELECT tstzrange '[2001-01-01 08:00:00, 2001-01-01 08:30:00)'::tstzspan; -- [2001-01-01 08:00:00, 2001-01-01 08:30:00)
Convert a span set value to and from a PostgreSQL multirange value
{spanset,multirange}::{multirange,spanset}
multirange(spanset) → multirange
spanset(multirange) → spanset
SELECT intspanset '{[1,2],[4,5]}'::int4multirange;
-- {[1,3),[4,6)}
SELECT tstzspanset '{[2001-01-01,2001-01-02],[2001-01-04,2001-01-05]}'::tstzmultirange;
-- {[2001-01-01,2001-01-02],[2001-01-04,2001-01-05]}
SELECT int4multirange '{[1,2],[4,5]}'::intspanset;
-- {[1, 3), [4, 6)}
SELECT tstzmultirange '{[2001-01-01,2001-01-02],[2001-01-04,2001-01-05]}'::tstzspanset;
-- {[2001-01-01, 2001-01-02], [2001-01-04, 2001-01-05]}