【Postgres】GIS数据转换(转)

一、查询

1.点
数据:“0101000020E61000002596731D61DA5E40D0DECF4F9EED4440”

Geojson 格式查询 : ST_AsGeojson(ST_GeomFromText(st_astext(字段)))

select ST_AsGeojson(ST_GeomFromText(st_astext(d_shape))) as shape from public.dian WHERE id=4
  • 1

得到数据:"{“type”:“Point”,“coordinates”:[123.412177431943,41.8563937916748]}"

WKT 格式查询 : ST_AsText(ST_GeomFromText(st_astext(字段)))

select ST_AsText(ST_GeomFromText(st_astext(d_shape))) as shape from public.dian WHERE id=4
  • 1

得到数据:“POINT(123.412177431943 41.8563937916748)”

== 线、面查询方式同点 ==

2.线

数据:
“0102000020E6100000020000002596732D18D75E40D0DECF0F88E944402596733D61D85E40D0DECFAF60E94440”

Geojson格式同点

“{
“type”:“LineString”,
“coordinates”:[[123.360850680234,41.8244647755615],[123.380935061338,41.8232631459229]]
}”

WKT格式同点

“LINESTRING(123.360850680234 41.8244647755615,123.380935061338 41.8232631459229)”

3.面

数据:
“0103000020E6100000010000000D000000495E52E424D35E4099A133F180DC444097B88EC973D55E4056117633DBDE4440D32CBF5AB4D35E4025A7A7868DE1444013CD440294D25E405CE6704127E144402F5A560579D25E40934B290D19E14440A521141A39D25E405B5046B6D7E04440499CD24236D25E40A4FA2D42AAE04440C9F6076022D25E407FDAA43699E0444012EC84B11CD25E40240ACE996EE04440DC86CCE52AD25E40FE531A030EE044402FF08090C8D25E407A2A50DD1CDE44408B75C267CBD25E40D5196729D0DD4440495E52E424D35E4099A133F180DC4440”

Geojson格式同点

“{”
type":“Polygon”,
“coordinates”:[[
[123.299126701772,41.7226850034815],
[123.335192098022,41.7410644842627],
[123.307882963843,41.7621315306299],
[123.290283744038,41.7590104867238],
[123.288636526421,41.7585770084035],
[123.284735221538,41.75658300813],
[123.28456183021,41.755195877505],
[123.283348090913,41.7546757035207],
[123.283001308257,41.7533752685597],
[123.283868264898,41.7504276159816],
[123.293491483608,41.7352558747707],
[123.293664874937,41.732915091841],
[123.299126701772,41.7226850034815]
]]
}"

WKT格式同点

“POLYGON((
123.299126701772 41.7226850034815,
123.335192098022 41.7410644842627,
123.307882963843 41.7621315306299,
123.290283744038 41.7590104867238,
123.288636526421 41.7585770084035,
123.284735221538 41.75658300813,
123.28456183021 41.755195877505,
123.283348090913 41.7546757035207,
123.283001308257 41.7533752685597,
123.283868264898 41.7504276159816,
123.293491483608 41.7352558747707,
123.293664874937 41.732915091841,
123.299126701772 41.7226850034815
))”

二、插入

1.点 POINT

insert into 表名 values (ST_GeomFromText(‘POINT(123.38574157989258 41.841888405322265)’,4326) )

insert into public.dian (id,d_shape) values (nextval('dian_id_seq'),ST_GeomFromText('POINT(123.38574157989258 41.841888405322265)',4326));
  • 1

2.线 LINESTRING

insert into 表名 values (ST_GeomFromText(‘LINESTRING(
123.38659988677735 41.85098645830078,
123.41784225738282 41.840686775683594,
123.39964615142578 41.8329620137207,
123.39346634185547 41.83673856401367
)’,4326) )

insert into public.line (l_id,l_shape) 
	values 
	(
	 	nextval('line_id_seq'),
	 	ST_GeomFromText('LINESTRING(
			123.38659988677735 41.85098645830078,
			123.41784225738282 41.840686775683594,
			123.39964615142578 41.8329620137207,
			123.39346634185547 41.83673856401367
	)',4326) )
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

3.面 POLYGON

同线,将LINESTRING改为POLYGON

POLYGON((123.37166534698243 41.83759687089844,123.42110382354493 41.83759687089844,123.40136276519532 41.81013105058594,123.37372528350586 41.81304929399414,123.36497055328125 41.8271255269043))
posted @   咸鱼翻身  阅读(919)  评论(0编辑  收藏  举报
编辑推荐:
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
阅读排行:
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· 单线程的Redis速度为什么快?
· 展开说说关于C#中ORM框架的用法!
· Pantheons:用 TypeScript 打造主流大模型对话的一站式集成库
历史上的今天:
2017-09-12 Android 读写位于SD卡上的sqlite数据库文件错误问题
2013-09-12 软件的价值来源(转,感谢原创,谢谢带来的顿悟)
点击右上角即可分享
微信分享提示