移动端的数据可视化移动化可视化网络化

转载

mob64ca14085c24 2023-11-28 10:18:55

文章标签 移动端的数据可视化数据 json 时间段 文章分类 数据可视化人工智能

如果我们有一些按照时间顺序发生的无线网络测量数据，每个测量数据有一个对应的坐标点，那么我们如何利用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`

从数据可以看到，这些测量数据发生在不同的时间点，分布在不同的地点，我们可以利用Power BI来进行可视化的分析，例如在地图上显示不同区域的网络测量值，分析网络测量值的分布，分析在每天不同时间段的测量值的分布等等。

这里我用到了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分钟来进行聚合，并且把RSRP，RSRQ的数值进行分组，把结果写入到一个新的表中，在PG里面输入以下的SQL语句：

create table nir_view as 
    select 
        extract(minute from time_bucket('15 minutes', ts)) as minute, 
        date(ts) as date, 
        cast(extract(hour from ts) as int) as hour, 
        rsrp, 
        rsrq, 
        operator, 
        gridid, 
        (CASE 
            WHEN rsrp<=-44 and rsrp>-65 THEN '(-65,-44]'
            WHEN rsrp<=-65 and rsrp>-75 THEN '(-75,-65]'
            WHEN rsrp<=-75 and rsrp>-85 THEN '(-85,-75]'
            WHEN rsrp<=-85 and rsrp>-95 THEN '(-95,-85]'
            WHEN rsrp<=-95 and rsrp>-105 THEN '(-105,-95]'
            ELSE '(-140,-105]' END
        ) as rsrp_range,
        (CASE 
            WHEN rsrp<=-44 and rsrp>-65 THEN '6'
            WHEN rsrp<=-65 and rsrp>-75 THEN '5'
            WHEN rsrp<=-75 and rsrp>-85 THEN '4'
            WHEN rsrp<=-85 and rsrp>-95 THEN '3'
            WHEN rsrp<=-95 and rsrp>-105 THEN '2'
            ELSE '1' END
        ) as rsrp_level,
        (CASE 
            WHEN rsrq<=-18 and rsrq>-20 THEN '(-20,-18]'
            WHEN rsrq<=-16 and rsrq>-18 THEN '(-18,-16]'
            WHEN rsrq<=-14 and rsrq>-16 THEN '(-16,-14]'
            WHEN rsrq<=-12 and rsrq>-14 THEN '(-14,-12]'
            WHEN rsrq<=-10 and rsrq>-12 THEN '(-12,-10]'
            WHEN rsrq<=-8 and rsrq>-10 THEN '(-10,-8]'
            WHEN rsrq<=-6 and rsrq>-8 THEN '(-8,-6]'
            WHEN rsrq<=-4 and rsrq>-6 THEN '(-6,-4]'
            ELSE '(-4,-2]' END
        ) as rsrq_range,
        (CASE 
            WHEN rsrq<=-18 and rsrq>-20 THEN '1'
            WHEN rsrq<=-16 and rsrq>-18 THEN '2'
            WHEN rsrq<=-14 and rsrq>-16 THEN '3'
            WHEN rsrq<=-12 and rsrq>-14 THEN '4'
            WHEN rsrq<=-10 and rsrq>-12 THEN '5'
            WHEN rsrq<=-8 and rsrq>-10 THEN '6'
            WHEN rsrq<=-6 and rsrq>-8 THEN '7'
            WHEN rsrq<=-4 and rsrq>-6 THEN '8'
            ELSE '9' END
        ) as rsrq_level
    from nir;