空间对象构造函数

ST_GeomFromGeoJSON

描述

接受一个以GeoJSON表示的空间对象为输入,输出一个空间对象。

函数声明

geometry ST_GeomFromGeoJSON(text  geomjson);

使用示例

SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt;
            wkt             
----------------------------
 POINT (-48.23456 20.12345)
(1 row)

ST_GeomFromText

描述

接受一个以Well-Known Text(WKT)表示的空间对象字符串为输入,输出一个空间对象。

函数声明

geometry ST_GeomFromText(text  WKT);

使用示例

SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)');

SELECT ST_GeomFromText('MULTILINESTRING((-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932))');

SELECT ST_GeomFromText('POINT(-71.064544 42.28787)');

SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');

ST_GeomFromWKB

描述

接受一个以Well-Known Binary(WKB)表示的空间对象字符串为输入,输出一个空间对象。

函数声明

geometry ST_GeomFromWKB(bytea  geom);

使用示例

select st_geomfromwkb('\x0102000000020000001F85EB51B87E5CC0D34D621058994340105839B4C87E5CC0295C8FC2F5984340' :: bytea);
 st_geomfromwkb(CAST('\x0102000000020000001F85EB51B87E5CC0D34D621058994340105839B4C87E5CC0295C8FC2F5984340' AS bytea)) 
-----------------------------------------------------------------------------------------------------------------------
 LINESTRING (-113.98 39.198, -113.981 39.195)
(1 row)



SELECT ST_AsText(
         ST_GeomFromWKB(
           ST_AsBinary('POINT(2 5)'::geometry)
         ));
 st_astext(st_geomfromwkb(st_asbinary(CAST('POINT(2 5)' AS geometry)))) 
------------------------------------------------------------------------
 POINT (2 5)
(1 row)

ST_LineFromText

描述

从给定的WKT表示法构造一个空间对象。

函数声明

geometry ST_LineFromText(text  WKT);

使用示例

SELECT ST_LineFromText('LINESTRING(1 2, 3 4)') AS aline;
         aline         
-----------------------
 LINESTRING (1 2, 3 4)
(1 row)

ST_LinestringFromWKB

描述

从给定的WKB表示法构造一个空间对象。

函数声明

geometry ST_LinestringFromWKB(bytea  WKB);

使用示例

SELECT ST_LineStringFromWKB(
    ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))) AS aline;
         aline         
-----------------------
 LINESTRING (1 2, 3 4)

ST_MakeLine

描述

从Point、MultiPoint或者Line等空间对象,构造一个LineString空间对象。

函数声明

geometry ST_MakeLine(geometry set geoms);
geometry ST_MakeLine(geometry geom1, geometry geom2);
geometry ST_MakeLine(geometry[] geoms_array);

使用示例

select st_makeline('POINT (0 0)', 'POINT (1 1)');
 st_makeline('POINT (0 0)', 'POINT (1 1)') 
-------------------------------------------
 LINESTRING (0 0, 1 1)
(1 row)

select st_makeline(['POINT (0 0)' :: geometry, 'POINT (1 1)' :: geometry, 'POINT (2 2)' :: geometry]);
 st_makeline([CAST('POINT (0 0)' AS geometry), CAST('POINT (1 1)' AS geometry), CAST('POINT (2 2)' AS geometry)]) 
------------------------------------------------------------------------------------------------------------------
 LINESTRING (0 0, 1 1, 2 2)
(1 row)

create table car_trace ( plate text, position geometry, ts timestamp);
insert into car_trace values ('C12345', 'POINT (1 2)', '2019-12-12 10:00:00');
insert into car_trace values ('C12345', 'POINT (1 3)', '2019-12-12 10:01:00');
insert into car_trace values ('C12345', 'POINT (1 4)', '2019-12-12 10:02:00');
insert into car_trace values ('A88888', 'POINT (8 8)', '2019-12-12 10:00:00');
insert into car_trace values ('A88888', 'POINT (9 8)', '2019-12-12 10:01:00');

select plate, st_makeline(position) from
       (select plate, ts, position from car_trace order by plate, ts) AS pos
       group by plate;
 plate  |   st_makeline(position)    
--------+----------------------------
 A88888 | LINESTRING (8 8, 9 8)
 C12345 | LINESTRING (1 2, 1 3, 1 4)
(2 rows)

ST_PointFromText

描述

从给定的WKT表示法字符串来构造一个Point空间对象。

函数声明

geometry ST_PointFromText(text  WKT);

使用示例

SELECT ST_PointFromText('POINT(-71.064544 42.28787)');

ST_PointFromWKB

描述

从给定的WKB表示法来构造一个Point空间对象。

函数声明

geometry ST_GeomFromWKB(bytea  geom);

使用示例

SELECT
  ST_AsText(
    ST_PointFromWKB(
      ST_AsBinary('POINT(2 5)'::geometry)
    )
  );
 st_astext(st_pointfromwkb(st_asbinary(CAST('POINT(2 5)' AS geometry)))) 
-------------------------------------------------------------------------
 POINT (2 5)
(1 row)

SELECT
  ST_AsText(
    ST_PointFromWKB(
      ST_AsBinary('LINESTRING(2 5, 2 6)'::geometry)
    )
  );
 st_astext(st_pointfromwkb(st_asbinary(CAST('LINESTRING(2 5, 2 6)' AS geometry)))) 
-----------------------------------------------------------------------------------
 LINESTRING (2 5, 2 6)
(1 row)

ST_WKBToSQL

描述

从给定的WKB表示法来构造一个空间对象。这是ST_GeomFromWKB函数的别名。

函数声明

geometry ST_WKBToSQL(bytea  WKB);

使用示例

参见ST_GeomFromWKB

ST_WKTToSQL

描述

从给定的Well-Known Text(WKT)表示法字符串构造一个空间对象。这是函数ST_GeomFromText的别名。

函数声明

geometry ST_WKTToSQL(text  WKT);

使用示例

参见ST_GeomFromText
阿里云首页 时间序列数据库 TSDB 相关技术圈