欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

用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

 

相关标签: 数据分析