# 时空数据库系列（二）-空间数据典型处理

+关注继续查看

5月5号，阿里云发布了最新的时空数据库，感兴趣的同学可以之前的文章《重磅！阿里云时空数据库正式免费公测》，

https://www.aliyun.com/product/hitsdb_spatialpre

# 一、投影变换

## 地理坐标与平面坐标相互转换

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009,
-71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.177684
8522251 42.3902896512902))',4326),2249)) As wgs_geom;
wgs_geom
---------------------------
POLYGON((743238 2967416,743238 2967450, 743265 2967450,743265.625 2967416,743238 2967416));

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)) As wgs_geom;
 wgs_geom
---------------------------
POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009,-71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.1776848522251 42.3902896512902));
(1 row)

# 二、线性投影

SELECT ST_AsText(ST_LineInterpolatePoint(foo.the_line, ST_LineLocatePoint(foo.the_line, ST_GeomFromText('POINT(4 3)')))) FROM (SELECT ST_GeomFromText('LINESTRING(1 2, 4 5, 6 7)') As the_line) As foo;
st_astext
----------------
POINT(3 4)

# 三、空间数据简化

SELECT ST_Simplify('LINESTRING(250 250, 280 290, 300 230, 340 300, 360 260, 440 310, 470 360, 604 286)', 2);

# 四、几何数据合并与裁剪

## 几何数据合并

SELECT ST_AsText(ST_UnaryUnion(ST_Collect(st_geomfromtext('POLYGON((0 1, 0 3, 2 3, 2 1, 0 1))', 4326),
st_geomfromtext('POLYGON((1 0, 1 2, 3 2, 3 0, 1 0))', 4326))));

## 裁剪

SELECT ST_AsText(ST_Difference( ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)')));
st_astext---------
LINESTRING(50 150,50 200)

# 五、空间数据咬合

SELECT ST_AsText(ST_Snap(poly,line, ST_Distance(poly,line)*1.25)) AS polysnapped
FROM (SELECT ST_GeomFromText('MULTIPOLYGON((( 26 125, 26 200, 126 200, 126 125, 26 125 ),( 51 150, 101 150, 76 175, 51 150 )),(( 151 100, 151 200, 176 175, 151 100 )))')
As poly, ST_GeomFromText('LINESTRING (5 107, 54 84, 101 100)') As line
) As foo;
polysnapped
---------------------------------------------------------------------
MULTIPOLYGON(((5 107,26 200,126 200,126 125,101 100,54 84,5 107),(51 150,101 150,76 175,51 150)),((151 100,151 200,176 175,151 100)))

# 六、计算方位角

SELECT degrees(ST_Azimuth(ST_Point(25, 45), ST_Point(75, 100))) AS degA_B,degrees(ST_Azimuth(ST_Point(75, 100), ST_Point(25, 45))) AS degB_A;
   dega_b       |      degb_a
------------------+------------------
42.2736890060937 | 222.273689006094

dega_b

degb_a

|
8天前
|

13 0
|
26天前
|

33 1
|
4月前
|

86 0
|
5月前
|

|
5月前
|

1403 0
|
7月前
|

57 0
|
7月前
|

78 0
|
7月前
|

「数据库深度探索」图型数据库JanusGraph支持知识图谱和图型分析
「数据库深度探索」图型数据库JanusGraph支持知识图谱和图型分析
103 0
|
8月前

35 0
|
8月前
|

52 0