全部产品

空间对象输出函数

更新时间:2019-07-08 18:09:58

ST_AsBinary

描述

返回给定几何对象或地理对象的WKB表示,其中不包含SRID元数据信息。

函数声明

  1. bytea ST_AsBinary(geometry g1);
  2. bytea ST_AsBinary(geometry g1, text NDR_or_XDR);
  3. bytea ST_AsBinary(geography g1);
  4. bytea ST_AsBinary(geography g1, text NDR_or_XDR);

使用示例

  1. SELECT ST_AsBinary(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
  2. st_asbinary
  3. --------------------------------
  4. \001\003\000\000\000\001\000\000\000\005
  5. \000\000\000\000\000\000\000\000\000\000
  6. \000\000\000\000\000\000\000\000\000\000
  7. \000\000\000\000\000\000\000\000\000\000
  8. \000\000\000\360?\000\000\000\000\000\000
  9. \360?\000\000\000\000\000\000\360?\000\000
  10. \000\000\000\000\360?\000\000\000\000\000
  11. \000\000\000\000\000\000\000\000\000\000\000
  12. \000\000\000\000\000\000\000\000
  13. (1 row)

ST_AsEncodedPolyline

描述

返回给定的LineString几何对象的Encoded Polyline表示。

函数声明

  1. text ST_AsEncodedPolyline(geometry geom, integer precision=5);

使用示例

  1. SELECT ST_AsEncodedPolyline(GeomFromEWKT('SRID=4326;LINESTRING(-120.2 38.5,-120.95 40.7,-126.453 43.252)'));
  2. --result--
  3. |_p~iF~ps|U_ulLnnqC_mqNvxq`@

ST_AsEWKB

描述

返回给定几何对象的扩展WKB表示,其中包含SRID元数据。

函数声明

  1. bytea ST_AsEWKB(geometry g1);
  2. bytea ST_AsEWKB(geometry g1, text NDR_or_XDR);

使用示例

  1. SELECT ST_AsEWKB(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
  2. st_asewkb
  3. --------------------------------
  4. \001\003\000\000 \346\020\000\000\001\000
  5. \000\000\005\000\000\000\000
  6. \000\000\000\000\000\000\000\000
  7. \000\000\000\000\000\000\000\000\000
  8. \000\000\000\000\000\000\000\000\000\000
  9. \000\000\360?\000\000\000\000\000\000\360?
  10. \000\000\000\000\000\000\360?\000\000\000\000\000
  11. \000\360?\000\000\000\000\000\000\000\000\000\000\000
  12. \000\000\000\000\000\000\000\000\000\000\000\000\000
  13. (1 row)

ST_AsEWKT

描述

返回给定几何对象的扩展WKT表示,其中包含SRID元数据。

函数声明

  1. text ST_AsEWKT(geometry g1);
  2. text ST_AsEWKT(geography g1);

使用示例

  1. SELECT ST_AsEWKT('0103000020E61000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000'::geometry);
  2. st_asewkt
  3. --------------------------------
  4. SRID=4326;POLYGON((0 0,0 1,1 1,1 0,0 0))
  5. (1 row)
  6. SELECT ST_AsEWKT('0108000080030000000000000060E30A4100000000785C0241000000000000F03F0000000018E20A4100000000485F024100000000000000400000000018E20A4100000000305C02410000000000000840')
  7. --st_asewkt---
  8. CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)

ST_AsGeoJSON

描述

返回给定几何对象或地理对象的GeoJSON表示。

函数声明

  1. text ST_AsGeoJSON(geometry geom, integer maxdecimaldigits=15, integer options=0);
  2. text ST_AsGeoJSON(geography geog, integer maxdecimaldigits=15, integer options=0);
  3. text ST_AsGeoJSON(integer gj_version, geometry geom, integer maxdecimaldigits=15, integer options=0);
  4. text ST_AsGeoJSON(integer gj_version, geography geog, integer maxdecimaldigits=15, integer options=0);

使用示例

  1. select row_to_json(fc)
  2. from (
  3. select
  4. 'FeatureCollection' as "type",
  5. array_to_json(array_agg(f)) as "features"
  6. from (
  7. select
  8. 'Feature' as "type",
  9. ST_AsGeoJSON(ST_Transform(way, 4326), 6) :: json as "geometry",
  10. (
  11. select json_strip_nulls(row_to_json(t))
  12. from (
  13. select
  14. osm_id,
  15. "natural",
  16. place
  17. ) t
  18. ) as "properties"
  19. from planet_osm_point
  20. where
  21. "natural" is not null
  22. or place is not null
  23. limit 10
  24. ) as f
  25. ) as fc;
  26. st_asgeojson
  27. -----------------------------------------------------------------------------------------------------------
  28. {"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[23.569251,51.541599]},"properties":{"osm_id":3424148658,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.625174,51.511718]},"properties":{"osm_id":4322036818,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.613928,51.5417]},"properties":{"osm_id":242979330,"place":"hamlet"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.586361,51.563272]},"properties":{"osm_id":3424148656,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.605488,51.553886]},"properties":{"osm_id":242979323,"place":"village"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.6067,51.57609]},"properties":{"osm_id":242979327,"place":"village"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.636533,51.575683]},"properties":{"osm_id":5737800420,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.656733,51.518733]},"properties":{"osm_id":5737802397,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.672542,51.504584]},"properties":{"osm_id":242979320,"place":"hamlet"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.574094,51.63389]},"properties":{"osm_id":242979333,"place":"village"}}]}

ST_AsGML

描述

返回给定几何对象或地理对象的GML表示。

函数声明

  1. text ST_AsGML(geometry geom, integer maxdecimaldigits=15, integer options=0);
  2. text ST_AsGML(geography geog, integer maxdecimaldigits=15, integer options=0);
  3. text ST_AsGML(integer version, geometry geom, integer maxdecimaldigits=15, integer options=0, text nprefix=null, text id=null);
  4. text ST_AsGML(integer version, geography geog, integer maxdecimaldigits=15, integer options=0, text nprefix=null, text id=null);

使用示例

  1. SELECT ST_AsGML(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
  2. st_asgml
  3. --------
  4. <gml:Polygon srsName="EPSG:4326"><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>0,0 0,1 1,1 1,0 0,0</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>

ST_AsHEXEWKB

描述

返回给定几何对象的HEXEWKB表示字符串,采用小端编码(NDR)或大端编码(XDR)。

函数声明

  1. text ST_AsHEXEWKB(geometry g1, text NDRorXDR);
  2. text ST_AsHEXEWKB(geometry g1);

使用示例

  1. SELECT ST_AsHEXEWKB(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
  2. which gives same answer as
  3. SELECT ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326)::text;
  4. st_ashexewkb
  5. --------
  6. 0103000020E6100000010000000500
  7. 00000000000000000000000000000000
  8. 00000000000000000000000000000000F03F
  9. 000000000000F03F000000000000F03F000000000000F03
  10. F000000000000000000000000000000000000000000000000

ST_AsKML

描述

返回给定几何对象或地理对象的KML表示。可以指定多个变种,默认采用version=2, maxdecimaldigits=15。

函数声明

  1. text ST_AsKML(geometry geom, integer maxdecimaldigits=15);
  2. text ST_AsKML(geography geog, integer maxdecimaldigits=15);
  3. text ST_AsKML(integer version, geometry geom, integer maxdecimaldigits=15, text nprefix=NULL);
  4. text ST_AsKML(integer version, geography geog, integer maxdecimaldigits=15, text nprefix=NULL);

使用示例

  1. SELECT ST_AsKML(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
  2. st_askml
  3. --------
  4. <Polygon><outerBoundaryIs><LinearRing><coordinates>0,0 0,1 1,1 1,0 0,0</coordinates></LinearRing></outerBoundaryIs></Polygon>
  5. --3d linestring
  6. SELECT ST_AsKML('SRID=4326;LINESTRING(1 2 3, 4 5 6)');
  7. <LineString><coordinates>1,2,3 4,5,6</coordinates></LineString>

ST_AsLatLonText

描述

返回给定坐标点的度、分、秒表示。

函数声明

  1. text ST_AsLatLonText(geometry pt, text format='');

使用示例

  1. SELECT (ST_AsLatLonText('POINT (-3.2342342 -2.32498)'));
  2. st_aslatlontext
  3. ----------------------------
  4. 2°19'29.928"S 3°14'3.243"W

ST_AsSVG

描述

返回给定几何对象或地理对象的SVG表示。

函数声明

  1. text ST_AsSVG(geometry geom, integer rel=0, integer maxdecimaldigits=15);
  2. text ST_AsSVG(geography geog, integer rel=0, integer maxdecimaldigits=15);

使用示例

  1. SELECT ST_AsSVG(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
  2. st_assvg
  3. --------
  4. M 0 0 L 0 -1 1 -1 1 0 Z

ST_AsText

描述

返回给定几何对象或地理对象的WKT表示,不包含SRID元数据。

函数声明

  1. text ST_AsText(geometry g1);
  2. text ST_AsText(geometry g1, integer maxdecimaldigits=15);
  3. text ST_AsText(geography g1);
  4. text ST_AsText(geography g1, integer maxdecimaldigits=15);

使用示例

  1. SELECT ST_AsText('01030000000100000005000000000000000000
  2. 000000000000000000000000000000000000000000000000
  3. F03F000000000000F03F000000000000F03F000000000000F03
  4. F000000000000000000000000000000000000000000000000');
  5. st_astext
  6. --------------------------------
  7. POLYGON((0 0,0 1,1 1,1 0,0 0))
  8. (1 row)

ST_AsTWKB

描述

返回给定几何对象的TWKB表示,即”Tiny Well-Known Binary”。

函数声明

  1. bytea ST_AsTWKB(geometry g1, integer decimaldigits_xy=0, integer decimaldigits_z=0, integer decimaldigits_m=0, boolean include_sizes=false, boolean include_bounding boxes=false);
  2. bytea ST_AsTWKB(geometry[] geometries, bigint[] unique_ids, integer decimaldigits_xy=0, integer decimaldigits_z=0, integer decimaldigits_m=0, boolean include_sizes=false, boolean include_bounding_boxes=false);

使用示例

  1. SELECT ST_AsTWKB('LINESTRING(1 1,5 5)'::geometry);
  2. st_astwkb
  3. --------------------------------------------
  4. \x02000202020808

ST_AsX3D

描述

返回给定几何对象的X3D表示,参见: ISO-IEC-19776-1.2-X3DEncodings-XML

函数声明

  1. text ST_AsX3D(geometry g1, integer maxdecimaldigits=15, integer options=0);

使用示例

  1. SELECT '<?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE X3D PUBLIC "ISO//Web3D//DTD X3D 3.0//EN" "http://www.web3d.org/specifications/x3d-3.0.dtd">
  3. <X3D>
  4. <Scene>
  5. <Transform>
  6. <Shape>
  7. <Appearance>
  8. <Material emissiveColor=''0 0 1''/>
  9. </Appearance> ' ||
  10. ST_AsX3D( ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
  11. ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
  12. ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
  13. ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )')) ||
  14. '</Shape>
  15. </Transform>
  16. </Scene>
  17. </X3D>' As x3ddoc;
  18. x3ddoc
  19. --------
  20. <?xml version="1.0" encoding="UTF-8"?>
  21. <!DOCTYPE X3D PUBLIC "ISO//Web3D//DTD X3D 3.0//EN" "http://www.web3d.org/specifications/x3d-3.0.dtd">
  22. <X3D>
  23. <Scene>
  24. <Transform>
  25. <Shape>
  26. <Appearance>
  27. <Material emissiveColor='0 0 1'/>
  28. </Appearance>
  29. <IndexedFaceSet coordIndex='0 1 2 3 -1 4 5 6 7 -1 8 9 10 11 -1 12 13 14 15 -1 16 17 18 19 -1 20 21 22 23'>
  30. <Coordinate point='0 0 0 0 0 1 0 1 1 0 1 0 0 0 0 0 1 0 1 1 0 1 0 0 0 0 0 1 0 0 1 0 1 0 0 1 1 1 0 1 1 1 1 0 1 1 0 0 0 1 0 0 1 1 1 1 1 1 1 0 0 0 1 1 0 1 1 1 1 0 1 1' />
  31. </IndexedFaceSet>
  32. </Shape>
  33. </Transform>
  34. </Scene>
  35. </X3D>

ST_GeoHash

描述

返回给定几何对象的GeoHash表示。

函数声明

  1. text ST_GeoHash(geometry geom, integer maxchars=full_precision_of_point);

使用示例

  1. SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326));
  2. st_geohash
  3. ----------------------
  4. c0w3hf1s70w3hf1s70w3
  5. SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326),5);
  6. st_geohash
  7. ------------
  8. c0w3h

ST_AsGeobuf

描述

返回给定行的集合的Geobuf表示。

函数声明

  1. bytea ST_AsGeobuf(anyelement set row);
  2. bytea ST_AsGeobuf(anyelement row, text geom_name);

使用示例

  1. SELECT encode(ST_AsGeobuf(q, 'geom'), 'base64')
  2. FROM (SELECT ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))') AS geom) AS q;
  3. st_asgeobuf
  4. ----------------------------------
  5. GAAiEAoOCgwIBBoIAAAAAgIAAAE=