开发者学堂课程【PostgreSQL 实战进阶：时空场景开发实践（二）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/112/detail/1905
时空场景开发实践（二）
内容介绍
一． 认识 Ganos
二． 如何使用 Ganos
三． 进阶实战
四．使用接口汇总
三． 进阶实战
1、实战课题
l 如何通过 Ganos 快速分析城市结构、社会属性与新冠病毒传播的之间的关系
l 如何在 Ganos 中通过轨迹数据追踪患者行程，并挖掘风险点
2、实战技能
l 利用 Ganos 进行空间统计分析
l 实现矢量、栅格一体化查询
l 实现轨迹追踪
l 实现跨区域时空查询
3、实战目的
l 熟练使用 Ganos
l 学会多源数据融合处理
l 实现时空场景快速呈现，减少开发成本
4、数据准备
矢量
l 行政街区数据
l 患者案例数据
栅格
l Ndvi 监测.tif
l 建筑密度.tif
l 建筑高度值.tif
5、数据入库
矢量数据入库
l ogr2ogr -nln hk tpu-nlt MULTIPOLYGON-geomfield geom -f
PostgreSQL
PG:“dbname='ganos train db’host='pgm-***.rdsaliyuncs.comport=1921'user=ganos trainpassword=ganos@2021'"”./data/hk tpu84.shp //这里填写的是云上购买的RDSPG的访问参数。
l ogr2ogr-nln hk cases…“./data/sick_cases.shp
栅格数据入库
l 首先，Tif 文件上传至 OSS。
l 其次，执行导入的 SQL 语句 insert into hk ndvi rast values(1st
importfrom(rbt',OSS://accessKey:accessSecret@oss-cn-beiling-internal.alivuncs.com/bucket/data/ndvi spot 84.tif
OSS 是云上非常廉价、也是使用率较高的工具。把 t if 上传至 OSS 之后，再通过insert 语句就可以把 OSS 上的数据录栅格数据录入到 pg 数据库中。因为影像文件比较小，所以采用 import from 这个接口，这样就可以将影像文件的所有像素值全部写入到数据库。
6、统计分析
l 统计香港患者案例最多的几个街区
select btpucount(a.id)from hk cases a,hk tpu b where st contains(bgeomageom)group by b.tpu order by count(a.id) desc；//主要通过 st contains 查询接口快速得到案例排名前五的街区编号。
案例数 top5的街区编号为121、131、212、113、144
7、矢栅一体化查询
l 用 Ganos 进行矢量+栅格一体化查询，提高开发效率
查询街区编号为121区域的 ndvi 监测总值、平均值
查询某某街区的建筑密度、建筑高度
通常有五个步骤:
可以看到通过 ST_Values 接口传入一个栅格对象，再传入一个几何对象。指定栅格对象的查询波段，就可以将所有在几何对象范围内的像素值统计出来，同时计算它的平均值，这样可以极大地提高开发效率。
8、分析城市结构、社会属性与新冠病毒传播的之间的关系
l 计算所有街区的 ndvi 平均值与案例数之间的关系
select m.tpum.sum as ndvi sum,mavg as ndvi avgncases count from(select c.tpu as tpu, sum(c.value) as sumava(c.value)as avafrom(SELECTbtpu as tpu(sTValues(a.rastb.aeom0)).* from hk ndvi rast a,hk tpu b)c group by c.tpu)m(select b.tpu as tpucount(aid) as cases count from hk cases a,hk tpu b where stcontains(bgeomageom)group by b.tpun where m.tpu=n.tpu order by m.avg;
在做这种统计质量和栅格一体化查询统计的时候，Ganos是非常方便的。下图是通过一条 SQL 语句查询街区编号对应的ndvi总值、ndvi平均值以及所在街区的案例数量，截取了前面几十条以及后面几十条：
ndvi 是植被指数，这里可以理解为绿地最少的不一定是有人去的地方，绿地最大的也不一定是人聚集的地方。很多时候都是在绿地处于中间水平的时候是比较聚集的，所以患者传播的概率也会增加很多。这是通过 SQL 语句来演示矢量和栅格数据之间能够分析出背后的关系的一个例子。
l 计算所有街区的建筑高度值与案例数之间的关系
select m.tpum.sum as building density sum,mavg as building density avgncases count from(select c.tpu as tpusum(c.value)as sumavg(c.value)as avg from(SELECT b tpu as tpu
(ST Values(arastbgeom0)).*from hk building rast a,hk tpu 23 b where a.id=1)c qroup by
c.tpu)m(select btpu as tpucount(aid) as cases count from sick cases 2326 a, hk tpu 23 b where st contains(bgeomageom)group by btpu)n where mtpu=n.tpu order by mavg；
建筑高度与患者案例之间的关系。建筑密度比较比较低的案例分布都是通常是个位数，建筑密度比较高案例数明显增大，最大值也是分布在建筑密度较高的区域。反映出建筑越高，案例数量呈现有聚集性。
9、用点表构造轨迹表
l 根据案例编号查询行程
通过轨迹来追踪患者的案例的行程，挖掘一些潜在的一些风险点。如上图所示，可以看到案例编号为105的案例，他在不同的时刻去过很多场所。他的监测时间记录的比较笼统，只具体到了某一天，但没有具体时间。
l 将行程构造成轨迹表
create table hk case traj(id serialtrajtrajectory); Insert into hk case traj(traj)
Select ST makeTrajectory’STPOINT’::leaftypex:float8ly:float84326ttimestampll
ARRAY['building name’'monitor value’]::text，NULL::int4[，m::float8b:text[1) FROM(
select array agg(st x(geom) order byid)as x
array agg(st y(geom) order by id) as y array agg(tm order by id) as t
array agg(monitor order by id) as m array agg(building order by id) as b from hk cases where cases=105)a;
可以通过将监测点抽取为行程点形成轨迹，以上是行程轨迹表。通过这个 Mac jacky 的一些聚合把编号为105的案例聚合成一条轨迹，写入到轨迹表里。如果想把所有的患者案例全部聚合成轨迹，可以把查询语句直接改成 groupby 就可以实现。
10、轨迹追踪
l 轨迹空间显示
create table hk_case_traj_geom as select id,st_trajectoryspatial(traj) as geom from hk_case traj;
l 轨迹追踪
select traj from hk case traj where id=230;
目前轨迹要显示还需要先转成空间对象。这里用技术软件就可以把空间对象直观的显示，查询到这条轨迹编号为230，到轨迹表里面查编号为230的轨迹对象，可以很快得到他到过的场所这一重要信息以及它的主要的走向。
11、时空查询
l 轨迹跨区域时空查询，挖掘潜在的风险点
select traj from hk tpu a, hk case traj b where st intersects(b.traj,'2020-03-1900:00:00:timestamp2020-03-21
00:00:00':timestampa.geo
m)andatpu=310
拿到这个轨迹之后，可以进行轨迹跨区域的时空查询。上图右侧的红色的区域，如果用这种案例直接查询，在案例表里面是只有一个案例的。但是通过轨迹的跨区域的查询，包括时间和空间上的相交，可以挖掘出来其中有八条轨迹，说明这八个案例在指定的时间内有可能穿过这个地方，所以它的风险指数是要增加的。这里主要演示的是通过跨时空查询来挖掘潜在的风险点。以上就是主要实战的内容，最后汇总一下用到的接口。
四． 使用接口汇总
矢量是空间关系的判断；栅格主要是一些入库、矢栅一体化查询以及拼接镶嵌；轨迹主要是轨迹构造以及轨迹的空间对象、轨迹的时空查询。接口的使用使用方法可以参考官方手册。
l Ganos Geometry
ST_Intersects、ST_Contains
l Ganos Raster
ST_importFrom、ST_createRast、ST_Values、ST_mosaicFrom、ST_mosaicTo
l Ganos Trajectory
ST_makeTrajectory、ST_trajectorySpatial、ST_Intersects
l Ganos 官方帮助手册
https://help.alivun.com/document detail/95580.html?spm=a2c4q.11174283.6.1275.4bde5b83aBseb
