我有一个 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);插入 tmp 值 ('SRID=4326;POINT (1 2)'),('SRID=4326;点 (2 4)');几何图形为 WKB(默认):
SELECT geom FROM tmp;几何--------------------------------------------------——0101000020E6100000000000000000F03F00000000000000400101000020E610000000000000000000400000000000001040几何为 WKT 和 EWKT(带有空间参考系统的 WKT):
SELECT ST_AsText(geom),ST_AsEWKT(geom) FROM tmp;st_astext |st_asewkt------------+----------------------点(1 2) |SRID=4326;点(1 2)点(2 4) |SRID=4326;点(2 4)如果您喜欢 GeoJSON ..
SELECT ST_AsGeoJSON(geom) FROM tmp;st_asgeojson-------------------------------{类型":点",坐标":[1,2]}{类型":点",坐标":[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>... 谷歌地球爱好者也有他们的乐趣!几何图形为 KML
SELECT ST_AsKML(geom) FROM tmp;st_askml-----------------------------------------------<点><坐标>1,2</坐标></点><点><坐标>2,4</坐标></点>名单还在继续……!在 PostGIS 文档 中,您会发现序列化几何图形的其他奇特方法.
演示:db<>fiddle
I have a Postgres table which stores polygon geometry in its specific format in one of the column, something like this-
0103000020E61000000100000004000000B8627F336B1554405DD602FFA2733A40B8627FA7601554403851F8EBC7723A40B8627FC38F15544036D539E90B733A40B8627F336B1554405DD602FFA2733A40I know how to convert this single value to WKT using ST_AsText which will give me POLYGON((Lat Long)). But I want to convert whole column into WKT format.
How to achieve this?
Thanks!
解决方案Have you tried this?
SELECT ST_AsText(your_geom_column) FROM your_tableIn the following examples I'll show you a few ways to serialise your geometries - data sample with two points encoded as 4326 (WGS84):
CREATE TEMPORARY TABLE tmp (geom GEOMETRY); INSERT INTO tmp VALUES ('SRID=4326;POINT (1 2)'), ('SRID=4326;POINT (2 4)');Geometries as WKB (default):
SELECT geom FROM tmp; geom ---------------------------------------------------- 0101000020E6100000000000000000F03F0000000000000040 0101000020E610000000000000000000400000000000001040Geometries as WKT and EWKT (a WKT with the Spatial Reference System):
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)In case you fancy GeoJSON ..
SELECT ST_AsGeoJSON(geom) FROM tmp; st_asgeojson -------------------------------------- {"type":"Point","coordinates":[1,2]} {"type":"Point","coordinates":[2,4]}.. or even 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>.. the Google Earth enthusiasts also have their fun! Geometries as KML
SELECT ST_AsKML(geom) FROM tmp; st_askml ----------------------------------------------- <Point><coordinates>1,2</coordinates></Point> <Point><coordinates>2,4</coordinates></Point>And the list goes on..! In the PostGIS documentation you will find other fancy ways to serialise your geometries.
Demo: db<>fiddle
更多推荐
将 Postgres 几何格式转换为 WKT
发布评论