用Power BI制作时序地理报表

程序员文章站 2024-02-24 13:32:04

...

如果我们有一些按照时间顺序发生的测量数据，每个测量数据有一个对应的坐标点，那么我们如何利用Power BI来进行分析呢。这里记录一下我的作法。

我的原始数据如下：

Datetime	Latitude	Longitude	CellID	Operator	NetworkType	RSSI	RxLevel	RSRQ	RSRP
`2020-11-08 12:12:10`	`23.120296`	`113.34134`	`100910615`	`CU`	`LTE`	`25`	`-97`	`-16`	`-97`

从数据可以看到，这些测量数据发生在不同的时间点，分布在不同的地点，如果我们要进行分析，例如比较在某个区域内某个时间段（例如15分钟内）的指标情况，那么就需要对数据进行聚合处理。这里我们可以用Google的S2地理数据库来进行地理区域的聚合，用时序数据库来进行时间段的聚合。

地理区域的聚合

我打算按照网格来进行聚合，例如把地图切分为200*200米的方格，把每个测量点归属到这个方格里面。

首先我们需要把地图切分为网格，在我之前的博客有介绍，可以利用Turf这个开源的JavaScript来完成。这里给出代码：

<!DOCTYPE html>
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
	<style type="text/css">
		body, html,#allmap {width: 100%;height: 100%;overflow: hidden;margin:0;font-family:"微软雅黑";}
	</style>
    <script src='https://unpkg.com/@turf/turf/turf.min.js'></script>
	<title>turf</title>
</head>
<body>
    <script type="text/javascript">
        var bbox = [113.2078, 23.0797, 113.3766, 23.1591];
        var cellSide = 0.2;
        var options = {units: 'kilometers'};
        var squareGrid = turf.squareGrid(bbox, cellSide, options);
        console.log(squareGrid.features.length);
        console.log(JSON.stringify(squareGrid));
    </script>
</body>

把这个HTML文件放到Web服务器上，然后访问这个文件，在控制台上可以看到网格数据的输出，把这些输出数据拷贝到一个文本文件中，例如guangzhou.geojson

有了网格数据之后，我们处理这个文件，给每个网格赋予一个ID，并稍微修改一下，然后上传到Mapbox网站生成tileset，代码如下：

with open('guangzhou_grids_200m.geojson', 'r') as f:
    grids_content = json.loads(f.read())

i = 0
for feature in grids_content['features']:
    i += 1
    feature['geometry']['type'] = 'MultiPolygon'
    feature['geometry']['coordinates'] = [feature['geometry']['coordinates']]
    feature['properties']['gridid'] = 'grid_'+str(i)

with open('guangzhou_grids_mapbox_200m.geojson', 'w') as f:
    f.write(json.dumps(grids_content))

之后我们用S2来为每个网格生成一个S2Loop的对象，代码如下：

with open('guangzhou_grids_mapbox_200m.geojson', 'r') as f:
    grids_content = json.loads(f.read())

grids_loop = {}
for feature in grids_content['features']:
    coordinates = feature['geometry']['coordinates'][0][0]
    s2points = []
    for point in coordinates[:-1]:
        s2points.append(s2.S2LatLng.FromDegrees(point[1], point[0]).Normalized().ToPoint())
    s2loop = s2.S2Loop(s2points)
    s2loop.Normalize()
    grids_loop[feature['properties']['gridid']] = s2loop

在PG数据库安装TimeScaleDB插件，具体可见官网介绍https://docs.timescale.com/, 在PG上创建一个数据库和数据表，如下：


create database nir;
\c nir
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE TABLE "nir" (ts TIMESTAMPTZ, lat decimal(9,6), lng decimal(9,6), cellid text, operator text, networktype text, rssi int, rxlevel int, rsrq int, rsrp int, gridid text);

下一步就是把原始测量数据的坐标读出来，用S2来判断是否落在某个网格中，把网格ID也关联的测量数据中，然后把数据写入PG。代码如下：

with open('nir_processed.csv', 'r') as f:
    nir = f.readlines()

newfields = []
for record in tqdm(nir[1:], total=len(nir[1:])):
    fields = record.strip().split(',')
    s2point = s2.S2LatLng.FromDegrees(float(fields[1]), float(fields[2])).ToPoint()
    for gridid in grids_loop:
        if grids_loop[gridid].Contains(s2point):
            fields.append(gridid)
            newfields.append(fields)
            break

conn = psycopg2.connect("dbname=nir user=abc password=123456")
cursor=conn.cursor()

for record in newfields:
    record[1] = float(record[1])
    record[2] = float(record[2])
    record[6] = int(record[6])
    record[7] = int(record[7])
    record[8] = int(record[8])
    record[9] = int(record[9])
    testsql = "insert into nir values('%s', '%f', '%f', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s')" \
        %(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8], record[9], record[10])
    cursor.execute(testsql)

conn.commit()
conn.close()

时间段的聚合

数据保存在时序数据库之后，我们就可以按照时间段来聚合了，这里按照每15分钟来进行聚合，并把结果写入到一个新的表中，在PG里面输入以下的SQL语句：

create table nir_15min as 
    select time_bucket('15 minutes', ts) as fifteen_min, 
        round(avg(rsrp),0) as avg_rsrp, 
        max(rsrp) as max_rsrp, 
        min(rsrp) as min_rsrp, 
        round(avg(rsrq),0) as avg_rsrq, 
        max(rsrq) as max_rsrq, 
        min(rsrq) as min_rsrq, 
        round(avg(rssi),0) as avg_rssi, 
        max(rssi) as max_rssi, 
        min(rssi) as min_rssi, 
        operator, 
        gridid 
    from nir 
        group by fifteen_min, operator, gridid order by fifteen_min;

把新表的数据输出为一个CSV文件

COPY (select * from nir_15min) to '/home/abc/pg_data/nir_15min.csv' with csv header;

Power BI报表制作

这里需要用到Mapbox控件，具体过程和我上一个博客介绍的类似，这里就不再重复了。

最后的效果如下：

P3_report

用Power BI制作时序地理报表

地理区域的聚合

时间段的聚合

Power BI报表制作

用Power BI制作时序地理报表

利用Power BI制作分级地图报表

利用Power BI制作分级地图报表