› 推特 ›阿里云技术文档›正文

空间对象构造函数_空间函数SQL参考_时空数据库_时序时空数据库TSDB

admin 阿里云技术文档 2020-02-11 213 0
阿里云服务器优惠

空间对象构造函数

ST_BdPolyFromText

描述

基于给定的由WKT（Well-Known Text） MultiLineString表示的一条或多条封闭曲线为基础来构造一个Polygon。

函数声明

  1. geometry ST_BdPolyFromText(text  WKT, integer  srid);

ST_BdMPolyFromText

描述

基于给定的由WKT（Well-Known Text） MultiLineString表示的一条或多条封闭曲线为基础来构造一个MultiPolygon。

函数声明

  1. geometry ST_BdMPolyFromText(text  WKT, integer  srid);

ST_Box2dFromGeoHash

描述

返回与给定GeoHash字符串对应的Box2D对象。

函数声明

  1. box2d ST_Box2dFromGeoHash(text  geohash, integer  precision=full_precision_of_geohash);

使用示例

  1. SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0');
  3.                 st_geomfromgeohash
  4. --------------------------------------------------
  5.  BOX(-115.172816 36.114646,-115.172816 36.114646)
  7. SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 0);
  9.  st_box2dfromgeohash
  10. ----------------------
  11.  BOX(-180 -90,180 90)
  13.  SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10);
  14.                             st_box2dfromgeohash
  15. ---------------------------------------------------------------------------
  16.  BOX(-115.17282128334 36.1146408319473,-115.172810554504 36.1146461963654)

ST_GeogFromText

描述

返回与给定的WKT（Well-Known Text）或扩展WKT表示法相对应的地理对象。

函数声明

  1. geography ST_GeogFromText(text  EWKT);

使用示例

  2. --- converting lon lat coords to geography
  3. ALTER TABLE sometable ADD COLUMN geog geography(POINT,4326);
  4. UPDATE sometable SET geog = ST_GeogFromText('SRID=4326;POINT(' || lon || ' ' || lat || ')');
  6. --- specify a geography point using EPSG:4267, NAD27
  7. SELECT ST_AsEWKT(ST_GeogFromText('SRID=4267;POINT(-77.0092 38.889588)'));
  8.               st_asewkt              
  9. -------------------------------------
  10.  SRID=4267;POINT(-77.0092 38.889588)

ST_GeographyFromText

描述

返回与给定的WKT或扩展WKT表示法相对应的地理对象。

函数声明

  1. geography ST_GeographyFromText(text  EWKT);

使用示例

  1. SELECT ST_AsEWKT(ST_GeographyFromText('SRID=4267;POINT(-77.0092 38.889588)'));
  2.               st_asewkt              
  3. -------------------------------------
  4.  SRID=4267;POINT(-77.0092 38.889588)

ST_GeogFromWKB

描述

返回与给定的Well-Known Binary (WKB)或extended Well Known Binary (EWKB)表示法相对应的地理对象。

函数声明

  1. geography ST_GeogFromWKB(bytea  wkb);

使用示例

  1. --Although bytea rep contains single \, these need to be escaped when inserting into a table
  2. SELECT ST_AsText(
  3. ST_GeogFromWKB(E'\\001\\002\\000\\000\\000\\002\\000\\000\\000\\037\\205\\353Q\\270~\\\\\\300\\323Mb\\020X\\231C@\\020X9\\264\\310~\\\\\\300)\\\\\\217\\302\\365\\230C@')
  4. );
  5.                       st_astext
  6. ------------------------------------------------------
  7.  LINESTRING(-113.98 39.198,-113.981 39.195)
  8. (1 row)

ST_GeomFromTWKB

描述

返回一个与给定的TWKB（Tiny Well-Known Binary）表示法相对应的几何对象。

函数声明

  1. geometry ST_GeomFromTWKB(bytea  twkb);

使用示例

  2. SELECT ST_AsText(ST_GeomFromTWKB(ST_AsTWKB('LINESTRING(126 34, 127 35)'::geometry)));
  4.          st_astext
  5. -----------------------------
  6.  LINESTRING(126 34, 127 35)
  7. (1 row)
  10. SELECT ST_AsEWKT(
  11.   ST_GeomFromTWKB(E'\\x620002f7f40dbce4040105')
  12. );
  13.                       st_asewkt
  14. ------------------------------------------------------
  15. LINESTRING(-113.98 39.198,-113.981 39.195)
  16. (1 row)

ST_GeomCollFromText

描述

以给定的WKT表示法和SRID构造相应的几何对象集合，如果未指定SRID，则其默认值为0。

函数声明

  1. geometry ST_GeomCollFromText(text  WKT, integer  srid);
  2. geometry ST_GeomCollFromText(text  WKT);

使用示例

  1. SELECT ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 2),LINESTRING(1 2, 3 4))');

ST_GeomFromEWKB

描述

返回一个与给定的Extended Well-Known Binary(EWKB)表示法相对应的几何对象。

函数声明

  1. geometry ST_GeomFromEWKB(bytea  EWKB);

使用示例

  1. SELECT ST_GeomFromEWKB(E'\\001\\002\\000\\000 \\255\\020\\000\\000\\003\\000\\000\\000\\344J=
  2. \\013B\\312Q\\300n\\303(\\010\\036!E@''\\277E''K
  3. \\312Q\\300\\366{b\\235*!E@\\225|\\354.P\\312Q
  4. \\300p\\231\\323e1!E@');

ST_GeomFromEWKT

描述

返回一个与给定的Extended Well-Known Text(EWKT)表示法相对应的几何对象。

函数声明

  1. geometry ST_GeomFromEWKT(text  EWKT);

使用示例

  1. SELECT ST_GeomFromEWKT('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)');
  2. SELECT ST_GeomFromEWKT('SRID=4269;MULTILINESTRING((-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932))');
  4. SELECT ST_GeomFromEWKT('SRID=4269;POINT(-71.064544 42.28787)');
  6. SELECT ST_GeomFromEWKT('SRID=4269;POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
  7. -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');
  9. SELECT ST_GeomFromEWKT('SRID=4269;MULTIPOLYGON(((-71.1031880899493 42.3152774590236,
  10. -71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,
  11. -71.1023097974109 42.3151969047397,-71.1019285062273 42.3147384934248,
  12. -71.102505233663 42.3144722937587,-71.10277487471 42.3141658254797,
  13. -71.103113945163 42.3142739188902,-71.10324876416 42.31402489987,
  14. -71.1033002961013 42.3140393340215,-71.1033488797549 42.3139495090772,
  15. -71.103396240451 42.3138632439557,-71.1041521907712 42.3141153348029,
  16. -71.1041411411543 42.3141545014533,-71.1041287795912 42.3142114839058,
  17. -71.1041188134329 42.3142693656241,-71.1041112482575 42.3143272556118,
  18. -71.1041072845732 42.3143851580048,-71.1041057218871 42.3144430686681,
  19. -71.1041065602059 42.3145009876017,-71.1041097995362 42.3145589148055,
  20. -71.1041166403905 42.3146168544148,-71.1041258822717 42.3146748022936,
  21. -71.1041375307579 42.3147318674446,-71.1041492906949 42.3147711126569,
  22. -71.1041598612795 42.314808571739,-71.1042515013869 42.3151287620809,
  23. -71.1041173835118 42.3150739481917,-71.1040809891419 42.3151344119048,
  24. -71.1040438678912 42.3151191367447,-71.1040194562988 42.3151832057859,
  25. -71.1038734225584 42.3151140942995,-71.1038446938243 42.3151006300338,
  26. -71.1038315271889 42.315094347535,-71.1037393329282 42.315054824985,
  27. -71.1035447555574 42.3152608696313,-71.1033436658644 42.3151648370544,
  28. -71.1032580383161 42.3152269126061,-71.103223066939 42.3152517403219,
  29. -71.1031880899493 42.3152774590236)),
  30. ((-71.1043632495873 42.315113108546,-71.1043583974082 42.3151211109857,
  31. -71.1043443253471 42.3150676015829,-71.1043850704575 42.3150793250568,-71.1043632495873 42.315113108546)))');

ST_GeometryFromText

描述

返回一个与给定的Well-Known Text(WKT)表示法相对应的几何对象。与ST_GeomFromText 函数相同。

函数声明

  1. geometry ST_GeometryFromText(text  WKT);
  2. geometry ST_GeometryFromText(text  WKT, integer  srid);

使用示例

  1. 参见ST_GeomFromText；

ST_GeomFromGeoHash

描述

返回一个与给定的GeoHash字符串相对应的几何对象。

函数声明

  1. geometry ST_GeomFromGeoHash(text  geohash, integer  precision=full_precision_of_geohash);

使用示例

  1. SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0'));
  2.                                                         st_astext
  3. --------------------------------------------------------------------------------------------------------------------------
  4.  POLYGON((-115.172816 36.114646,-115.172816 36.114646,-115.172816 36.114646,-115.172816 36.114646,-115.172816 36.114646))
  6. SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 4));
  7.                                                           st_astext
  8. ------------------------------------------------------------------------------------------------------------------------------
  9.  POLYGON((-115.3125 36.03515625,-115.3125 36.2109375,-114.9609375 36.2109375,-114.9609375 36.03515625,-115.3125 36.03515625))
  11. SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10));
  12.                                                                                        st_astext
  13. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  14.  POLYGON((-115.17282128334 36.1146408319473,-115.17282128334 36.1146461963654,-115.172810554504 36.1146461963654,-115.172810554504 36.1146408319473,-115.17282128334 36.1146408319473))

ST_GeomFromGML

描述

接受一个以GML表示的几何对象字符串为输入，输出一个几何对象。

函数声明

  1. geometry ST_GeomFromGML(text  geomgml);
  2. geometry ST_GeomFromGML(text  geomgml, integer  srid);

使用示例

  1. SELECT ST_GeomFromGML('
  2.         <gml:LineString srsName="EPSG:4269">
  3.             <gml:coordinates>
  4.                 -71.16028,42.258729 -71.160837,42.259112 -71.161143,42.25932
  5.             </gml:coordinates>
  6.         </gml:LineString>');

ST_GeomFromGeoJSON

描述

接受一个以GeoJSON表示的几何对象为输入，输出一个几何对象。

函数声明

  1. geometry ST_GeomFromGeoJSON(text  geomjson);
  2. geometry ST_GeomFromGeoJSON(json  geomjson);
  3. geometry ST_GeomFromGeoJSON(jsonb  geomjson);

使用示例

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

ST_GeomFromKML

描述

接受一个以KML表示的几何对象字符串为输入，输出一个几何对象。

函数声明

  1. geometry ST_GeomFromKML(text  geomkml);

使用示例

  1. SELECT ST_GeomFromKML('
  2.         <LineString>
  3.             <coordinates>-71.1663,42.2614
  4.                 -71.1667,42.2616</coordinates>
  5.         </LineString>');

ST_GMLToSQL

描述

接受一个以GML表示的几何对象字符串为输入，输出一个几何对象。等同于ST_GeomFromGML。

函数声明

  1. geometry ST_GMLToSQL(text  geomgml);
  2. geometry ST_GMLToSQL(text  geomgml, integer  srid);

使用示例

  1. None

ST_GeomFromText

描述

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

函数声明

  1. geometry ST_GeomFromText(text  WKT);
  2. geometry ST_GeomFromText(text  WKT, integer  srid);

使用示例

  1. SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)');
  2. SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4269);
  4. SELECT ST_GeomFromText('MULTILINESTRING((-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932))');
  6. SELECT ST_GeomFromText('POINT(-71.064544 42.28787)');
  8. SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
  9. -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');
  11. SELECT ST_GeomFromText('MULTIPOLYGON(((-71.1031880899493 42.3152774590236,
  12. -71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,
  13. -71.1023097974109 42.3151969047397,-71.1019285062273 42.3147384934248,
  14. -71.102505233663 42.3144722937587,-71.10277487471 42.3141658254797,
  15. -71.103113945163 42.3142739188902,-71.10324876416 42.31402489987,
  16. -71.1033002961013 42.3140393340215,-71.1033488797549 42.3139495090772,
  17. -71.103396240451 42.3138632439557,-71.1041521907712 42.3141153348029,
  18. -71.1041411411543 42.3141545014533,-71.1041287795912 42.3142114839058,
  19. -71.1041188134329 42.3142693656241,-71.1041112482575 42.3143272556118,
  20. -71.1041072845732 42.3143851580048,-71.1041057218871 42.3144430686681,
  21. -71.1041065602059 42.3145009876017,-71.1041097995362 42.3145589148055,
  22. -71.1041166403905 42.3146168544148,-71.1041258822717 42.3146748022936,
  23. -71.1041375307579 42.3147318674446,-71.1041492906949 42.3147711126569,
  24. -71.1041598612795 42.314808571739,-71.1042515013869 42.3151287620809,
  25. -71.1041173835118 42.3150739481917,-71.1040809891419 42.3151344119048,
  26. -71.1040438678912 42.3151191367447,-71.1040194562988 42.3151832057859,
  27. -71.1038734225584 42.3151140942995,-71.1038446938243 42.3151006300338,
  28. -71.1038315271889 42.315094347535,-71.1037393329282 42.315054824985,
  29. -71.1035447555574 42.3152608696313,-71.1033436658644 42.3151648370544,
  30. -71.1032580383161 42.3152269126061,-71.103223066939 42.3152517403219,
  31. -71.1031880899493 42.3152774590236)),
  32. ((-71.1043632495873 42.315113108546,-71.1043583974082 42.3151211109857,
  33. -71.1043443253471 42.3150676015829,-71.1043850704575 42.3150793250568,-71.1043632495873 42.315113108546)))',4326);
  35. SELECT ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)');

ST_GeomFromWKB

描述

接受一个以Well-Known Binary(WKB)表示的几何对象字符串为输入，输出一个几何对象，参数SRID是可选的。

函数声明

  1. geometry ST_GeomFromWKB(bytea  geom);
  2. geometry ST_GeomFromWKB(bytea  geom, integer  srid);

使用示例

  1. --Although bytea rep contains single \, these need to be escaped when inserting into a table
  2.         -- unless standard_conforming_strings is set to on.
  3. SELECT ST_AsEWKT(
  4. ST_GeomFromWKB(E'\\001\\002\\000\\000\\000\\002\\000\\000\\000\\037\\205\\353Q\\270~\\\\\\300\\323Mb\\020X\\231C@\\020X9\\264\\310~\\\\\\300)\\\\\\217\\302\\365\\230C@',4326)
  5. );
  6.                       st_asewkt
  7. ------------------------------------------------------
  8.  SRID=4326;LINESTRING(-113.98 39.198,-113.981 39.195)
  9. (1 row)
  11. SELECT
  12.   ST_AsText(
  13.     ST_GeomFromWKB(
  14.       ST_AsEWKB('POINT(2 5)'::geometry)
  15.     )
  16.   );
  17.  st_astext
  18. ------------
  19.  POINT(2 5)
  20. (1 row)

ST_LineFromEncodedPolyline

描述

从给定的Polyline的编码构造一个LineString对象。

函数声明

  1. geometry ST_LineFromEncodedPolyline(text  polyline, integer  precision=5);

使用示例

  2. -- Create a line string from a polyline
  3. SELECT ST_AsEWKT(ST_LineFromEncodedPolyline('_p~iF~ps|U_ulLnnqC_mqNvxq`@'));
  4. -- result --
  5. SRID=4326;LINESTRING(-120.2 38.5,-120.95 40.7,-126.453 43.252)
  7. -- Select different precision that was used for polyline encoding
  8. SELECT ST_AsEWKT(ST_LineFromEncodedPolyline('_p~iF~ps|U_ulLnnqC_mqNvxq`@',6));
  9. -- result --
  10. SRID=4326;LINESTRING(-12.02 3.85,-12.095 4.07,-12.6453 4.3252)

ST_LineFromMultiPoint

描述

从一个MultiPoint几何对象构造一个LineString对象。

函数声明

  1. geometry ST_LineFromMultiPoint(geometry  aMultiPoint);

使用示例

  2. --Create a 3d line string from a 3d multipoint
  3. SELECT ST_AsEWKT(ST_LineFromMultiPoint(ST_GeomFromEWKT('MULTIPOINT(1 2 3, 4 5 6, 7 8 9)')));
  4. --result--
  5. LINESTRING(1 2 3,4 5 6,7 8 9)

ST_LineFromText

描述

从给定的WKT表示法和SRID构造一个几何对象，如果未指定SRID，则默认值为0。

函数声明

  1. geometry ST_LineFromText(text  WKT);
  2. geometry ST_LineFromText(text  WKT, integer  srid);

使用示例

  1. SELECT ST_LineFromText('LINESTRING(1 2, 3 4)') AS aline, ST_LineFromText('POINT(1 2)') AS null_return;
  2. aline                            | null_return
  3. ------------------------------------------------
  4. 010200000002000000000000000000F ... | t

ST_LineFromWKB

描述

从给定的WKB表示法和SRID构造一个几何对象。

函数声明

  1. geometry ST_LineFromWKB(bytea  WKB);
  2. geometry ST_LineFromWKB(bytea  WKB, integer  srid);

使用示例

  1. SELECT ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))) AS aline,
  2.         ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('POINT(1 2)'))) IS NULL AS null_return;
  3. aline                            | null_return
  4. ------------------------------------------------
  5. 010200000002000000000000000000F ... | t

ST_LinestringFromWKB

描述

从给定的WKB表示法和SRID构造一个几何对象。

函数声明

  1. geometry ST_LinestringFromWKB(bytea  WKB);
  2. geometry ST_LinestringFromWKB(bytea  WKB, integer  srid);

使用示例

  1. SELECT
  2.   ST_LineStringFromWKB(
  3.     ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))
  4.   ) AS aline,
  5.   ST_LinestringFromWKB(
  6.     ST_AsBinary(ST_GeomFromText('POINT(1 2)'))
  7.   ) IS NULL AS null_return;
  8.    aline                            | null_return
  9. ------------------------------------------------
  10. 010200000002000000000000000000F ... | t

ST_MakeBox2D

描述

从给定的左下角和右上角两个点，构造一个Box2D几何对象。

函数声明

  1. box2d ST_MakeBox2D(geometry  pointLowLeft, geometry  pointUpRight);

使用示例

  2. --Return all features that fall reside or partly reside in a US national atlas coordinate bounding box
  3. --It is assumed here that the geometries are stored with SRID = 2163 (US National atlas equal area)
  4. SELECT feature_id, feature_name, the_geom
  5. FROM features
  6. WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-989502.1875, 528439.5625),
  7.     ST_Point(-987121.375 ,529933.1875)),2163)

ST_3DMakeBox

描述

从给定的两个对角点，构造一个Box3D对象。

函数声明

  1. box3d ST_3DMakeBox(geometry  point3DLowLeftBottom, geometry  point3DUpRightTop);

使用示例

  2. SELECT ST_3DMakeBox(ST_MakePoint(-989502.1875, 528439.5625, 10),
  3.     ST_MakePoint(-987121.375 ,529933.1875, 10)) As abb3d
  5. --bb3d--
  6. --------
  7. BOX3D(-989502.1875 528439.5625 10,-987121.375 529933.1875 10)

ST_MakeLine

描述

从Point、MultiPoint或者Line等几何对象，构造一个LineString几何对象。

函数声明

  1. geometry ST_MakeLine(geometry set geoms);
  2. geometry ST_MakeLine(geometry geom1, geometry geom2);
  3. geometry ST_MakeLine(geometry[] geoms_array);

使用示例

  1. SELECT gps.gps_track, ST_MakeLine(gps.the_geom) As newgeom
  2.     FROM (SELECT gps_track, gps_time, the_geom
  3.             FROM gps_points ORDER BY gps_track, gps_time) As gps
  4.     GROUP BY gps.gps_track;

ST_MakeEnvelope

描述

从给定的最小值和最大值构造一个Polygon。输入值必须在给定的SRID的有效范围内。

函数声明

  1. geometry ST_MakeEnvelope(double precision xmin, double precision ymin, double precision xmax, double precision ymax, integer  srid=unknown);

使用示例

  2. SELECT ST_AsText(ST_MakeEnvelope(10, 10, 11, 11, 4326));
  4. st_asewkt
  5. -----------
  6. POLYGON((10 10, 10 11, 11 11, 11 10, 10 10))

ST_MakePolygon

描述

从给定的边界构造一个Polygon对象。输入必须是一个封闭的LineString。

函数声明

  1. geometry ST_MakePolygon(geometry linestring);

使用示例

  2. --2d line
  3. SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)'));
  4. --If linestring is not closed
  5. --you can add the start point to close it
  6. SELECT ST_MakePolygon(ST_AddPoint(foo.open_line, ST_StartPoint(foo.open_line)))
  7. FROM (
  8. SELECT ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5)') As open_line) As foo;
  10. --3d closed line
  11. SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(75.15 29.53 1,77 29 1,77.6 29.5 1, 75.15 29.53 1)'));
  13. st_asewkt
  14. -----------
  15. POLYGON((75.15 29.53 1,77 29 1,77.6 29.5 1,75.15 29.53 1))
  17. --measured line --
  18. SELECT ST_MakePolygon(ST_GeomFromText('LINESTRINGM(75.15 29.53 1,77 29 1,77.6 29.5 2, 75.15 29.53 2)'));
  20. st_asewkt
  21. ----------
  22. POLYGONM((75.15 29.53 1,77 29 1,77.6 29.5 2,75.15 29.53 2))

ST_MakePoint

描述

构造一个2D, 3DZ或者4D的点。

函数声明

  1. geometry ST_MakePoint(double precision x, double precision y);

使用示例

  1. --Return point with unknown SRID
  2. SELECT ST_MakePoint(-71.1043443253471, 42.3150676015829);
  4. --Return point marked as WGS 84 long lat
  5. SELECT ST_SetSRID(ST_MakePoint(-71.1043443253471, 42.3150676015829),4326);
  7. --Return a 3D point (e.g. has altitude)
  8. SELECT ST_MakePoint(1, 2,1.5);
  10. --Get z of point
  11. SELECT ST_Z(ST_MakePoint(1, 2,1.5));
  12. result
  13. -------
  14. 1.5

ST_MakePointM

描述

给定x、y和m坐标值来构造一个PointM对象。

函数声明

  1. geometry ST_MakePointM(float x, float y, float m);

使用示例

  2. --Return EWKT representation of point with unknown SRID
  3. SELECT ST_AsEWKT(ST_MakePointM(-71.1043443253471, 42.3150676015829, 10));
  5. --result
  6.                    st_asewkt
  7. -----------------------------------------------
  8.  POINTM(-71.1043443253471 42.3150676015829 10)
  10. --Return EWKT representation of point with measure marked as WGS 84 long lat
  11. SELECT ST_AsEWKT(ST_SetSRID(ST_MakePointM(-71.1043443253471, 42.3150676015829,10),4326));
  13.                         st_asewkt
  14. ---------------------------------------------------------
  15. SRID=4326;POINTM(-71.1043443253471 42.3150676015829 10)
  17. --Return a 3d point (e.g. has altitude)
  18. SELECT ST_MakePoint(1, 2,1.5);
  20. --Get m of point
  21. SELECT ST_M(ST_MakePointM(-71.1043443253471, 42.3150676015829,10));
  22. result
  23. -------
  24. 10

ST_MLineFromText

描述

从给定的WKT表示法字符串构造一个MultiLineString对象。

函数声明

  1. geometry ST_MLineFromText(text  WKT, integer  srid);
  2. geometry ST_MLineFromText(text  WKT);

使用示例

  1. SELECT ST_MLineFromText('MULTILINESTRING((1 2, 3 4), (4 5, 6 7))');

ST_MPointFromText

描述

从给定的WKT表示法字符串和SRID来构造一个MultiPoint几何对象，如果SRID未指定，则默认为0。

函数声明

  1. geometry ST_MPointFromText(text  WKT, integer  srid);
  2. geometry ST_MPointFromText(text  WKT);

使用示例

  1. SELECT ST_MPointFromText('MULTIPOINT(1 2, 3 4)');
  2. SELECT ST_MPointFromText('MULTIPOINT(-70.9590 42.1180, -70.9611 42.1223)', 4326);

ST_MPolyFromText

描述

从给定的WKT表示法字符串和SRID来构造一个MultiPolygon几何对象，如果SRID未指定，则默认为0。

函数声明

  1. geometry ST_MPolyFromText(text  WKT, integer  srid);
  2. geometry ST_MPolyFromText(text  WKT);

使用示例

  1. SELECT ST_MPolyFromText('MULTIPOLYGON(((0 0 1,20 0 1,20 20 1,0 20 1,0 0 1),(5 5 3,5 7 3,7 7 3,7 5 3,5 5 3)))');
  2. SELECt ST_MPolyFromText('MULTIPOLYGON(((-70.916 42.1002,-70.9468 42.0946,-70.9765 42.0872,-70.9754 42.0875,-70.9749 42.0879,-70.9752 42.0881,-70.9754 42.0891,-70.9758 42.0894,-70.9759 42.0897,-70.9759 42.0899,-70.9754 42.0902,-70.9756 42.0906,-70.9753 42.0907,-70.9753 42.0917,-70.9757 42.0924,-70.9755 42.0928,-70.9755 42.0942,-70.9751 42.0948,-70.9755 42.0953,-70.9751 42.0958,-70.9751 42.0962,-70.9759 42.0983,-70.9767 42.0987,-70.9768 42.0991,-70.9771 42.0997,-70.9771 42.1003,-70.9768 42.1005,-70.977 42.1011,-70.9766 42.1019,-70.9768 42.1026,-70.9769 42.1033,-70.9775 42.1042,-70.9773 42.1043,-70.9776 42.1043,-70.9778 42.1048,-70.9773 42.1058,-70.9774 42.1061,-70.9779 42.1065,-70.9782 42.1078,-70.9788 42.1085,-70.9798 42.1087,-70.9806 42.109,-70.9807 42.1093,-70.9806 42.1099,-70.9809 42.1109,-70.9808 42.1112,-70.9798 42.1116,-70.9792 42.1127,-70.979 42.1129,-70.9787 42.1134,-70.979 42.1139,-70.9791 42.1141,-70.9987 42.1116,-71.0022 42.1273,
  3.     -70.9408 42.1513,-70.9315 42.1165,-70.916 42.1002)))',4326);

ST_Point

描述

以给定的坐标值构造一个Point对象，是OGC标准中ST_MakePoint的别名。

函数声明

  1. geometry ST_Point(float  x_lon, float  y_lat);

使用示例

  1. SELECT ST_SetSRID(ST_Point(-71.1043443253471, 42.3150676015829),4326)

ST_PointFromGeoHash

描述

从给定的GeoHash字符串中构造相应的Point对象。

函数声明

  1. point ST_PointFromGeoHash(text  geohash, integer  precision=full_precision_of_geohash);

使用示例

  1. SELECT ST_AsText(ST_PointFromGeoHash('9qqj7nmxncgyy4d0dbxqz0'));
  2.           st_astext
  3. ------------------------------
  4.  POINT(-115.172816 36.114646)
  6. SELECT ST_AsText(ST_PointFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 4));
  7.              st_astext
  8. -----------------------------------
  9.  POINT(-115.13671875 36.123046875)
  11. SELECT ST_AsText(ST_PointFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10));
  12.                  st_astext
  13. -------------------------------------------
  14.  POINT(-115.172815918922 36.1146435141563)

ST_PointFromText

描述

从给定的WKT表示法字符串和SRID来构造一个Point几何对象，如果SRID未指定，则默认为0。

函数声明

  1. geometry ST_PointFromText(text  WKT);
  2. geometry ST_PointFromText(text  WKT, integer  srid);

使用示例

  1. SELECT ST_PointFromText('POINT(-71.064544 42.28787)');
  2. SELECT ST_PointFromText('POINT(-71.064544 42.28787)', 4326);

ST_PointFromWKB

描述

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

函数声明

  1. geometry ST_GeomFromWKB(bytea  geom);
  2. geometry ST_GeomFromWKB(bytea  geom, integer  srid);

使用示例

  1. SELECT
  2.   ST_AsText(
  3.     ST_PointFromWKB(
  4.       ST_AsEWKB('POINT(2 5)'::geometry)
  5.     )
  6.   );
  7.  st_astext
  8. ------------
  9.  POINT(2 5)
  10. (1 row)
  12. SELECT
  13.   ST_AsText(
  14.     ST_PointFromWKB(
  15.       ST_AsEWKB('LINESTRING(2 5, 2 6)'::geometry)
  16.     )
  17.   );
  18.  st_astext
  19. -----------
  21. (1 row)

ST_Polygon

描述

从给定的LineString对象和SRID构造一个Polygon对象。

函数声明

  1. geometry ST_Polygon(geometry  aLineString, integer  srid);

使用示例

  1. --a 2d polygon
  2. SELECT ST_Polygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)'), 4326);
  4. --result--
  5. POLYGON((75.15 29.53,77 29,77.6 29.5,75.15 29.53))
  6. --a 3d polygon
  7. SELECT ST_AsEWKT(ST_Polygon(ST_GeomFromEWKT('LINESTRING(75.15 29.53 1,77 29 1,77.6 29.5 1, 75.15 29.53 1)'), 4326));
  9. result
  10. ------
  11. SRID=4326;POLYGON((75.15 29.53 1,77 29 1,77.6 29.5 1,75.15 29.53 1))

ST_PolygonFromText

描述

从给定的WKT表示法字符串和SRID来构造一个Polygon几何对象，如果SRID未指定，则默认为0。

函数声明

  1. geometry ST_PolygonFromText(text  WKT);
  2. geometry ST_PolygonFromText(text  WKT, integer  srid);

使用示例

  1. SELECT ST_PolygonFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
  2. -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');
  3. st_polygonfromtext
  4. ------------------
  5. 010300000001000000050000006...
  8. SELECT ST_PolygonFromText('POINT(1 2)') IS NULL as point_is_notpoly;
  10. point_is_not_poly
  11. ----------
  12. t

ST_WKBToSQL

描述

从给定的WKB表示法来构造一个几何对象。这是不带SRID参数的ST_GeomFromWKB函数的别名。

函数声明

  1. geometry ST_WKBToSQL(bytea  WKB);

使用示例

  1. 参见ST_GeomFromWKB

ST_WKTToSQL

描述

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

函数声明

  1. geometry ST_WKTToSQL(text  WKT);

使用示例

  1. 参见ST_GeomFromText
版权声明

本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。

评论

-----