gpt4 book ai didi

sql - 将 Postgis 几何格式转换为 WKT

转载 作者:行者123 更新时间:2023-11-29 12:30:29 25 4
gpt4 key购买 nike

我有一个 Postgres 表,它以特定格式在其中一列中存储多边形几何图形,类似于这样-

0103000020E61000000100000004000000B8627F336B1554405DD602FFA2733A40B8627FA7601554403851F8EBC7723A40B8627FC38F15544036D539E90B733A40B8627F336B1554405DD602FFA2733A40

我知道如何使用 ST_AsText 将这个单一值转换为 WKT,这将给我 POLYGON((Lat Long))。但我想将整个列转换为 WKT 格式。

如何实现?

谢谢!

最佳答案

你试过吗?

SELECT ST_AsText(your_geom_column) FROM your_table

在下面的示例中,我将向您展示几种序列化几何图形的方法。这是两个点编码为 4326 (WGS84) 的示例数据:

CREATE TEMPORARY TABLE tmp 
(geom GEOMETRY);
INSERT INTO tmp VALUES
('SRID=4326;POINT(1 2)'),
('SRID=4326;POINT(2 4)');

几何图形为 WKB (众所周知的二进制文件,默认):

SELECT geom FROM tmp;

geom
----------------------------------------------------
0101000020E6100000000000000000F03F0000000000000040
0101000020E610000000000000000000400000000000001040

几何图形为 WKT (知名文本)和 EWKT (具有显式空间引用系统的 WKT):

SELECT ST_AsText(geom), ST_AsEWKT(geom) FROM tmp;

st_astext | st_asewkt
------------+----------------------
POINT(1 2) | SRID=4326;POINT(1 2)
POINT(2 4) | SRID=4326;POINT(2 4)

如果你喜欢 GeoJSON

SELECT ST_AsGeoJSON(geom) FROM tmp;

st_asgeojson
--------------------------------------
{"type":"Point","coordinates":[1,2]}
{"type":"Point","coordinates":[2,4]}

甚至 GML

SELECT ST_AsGML(geom) FROM tmp;

st_asgml
-----------------------------------------------------------------------------------
<gml:Point srsName="EPSG:4326"><gml:coordinates>1,2</gml:coordinates></gml:Point>
<gml:Point srsName="EPSG:4326"><gml:coordinates>2,4</gml:coordinates></gml:Point>

Google 地球的爱好者们也玩得很开心!几何形状为 KML

SELECT ST_AsKML(geom) FROM tmp;

st_askml
-----------------------------------------------
<Point><coordinates>1,2</coordinates></Point>
<Point><coordinates>2,4</coordinates></Point>

图书馆员可能还想看看 ST_AsMARC21 (3.3新增)

SELECT ST_AsMARC21(geom) FROM tmp;
st_asmarc21
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<record xmlns="http://www.loc.gov/MARC21/slim"><datafield tag="034" ind1="1" ind2=" "><subfield code="a">a</subfield><subfield code="d">E0010000</subfield><subfield code="e">E0010000</subfield><subfield code="f">N0020000</subfield><subfield code="g">N0020000</subfield></dataf
ield></record>
<record xmlns="http://www.loc.gov/MARC21/slim"><datafield tag="034" ind1="1" ind2=" "><subfield code="a">a</subfield><subfield code="d">E0020000</subfield><subfield code="e">E0020000</subfield><subfield code="f">N0040000</subfield><subfield code="g">N0040000</subfield></dataf
ield></record>
(2 rows)

名单还在继续!在 PostGIS 文档中有 other fancy ways to serialise geometry .

演示: db<>fiddle

关于sql - 将 Postgis 几何格式转换为 WKT,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49905207/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com