# 数据库大赛

**Repository Path**: zhu-junyang_admin/database-competition

## Basic Information

- **Project Name**: 数据库大赛
- **Description**: 数据库大赛作品（前端）
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 5
- **Forks**: 2
- **Created**: 2020-10-27
- **Last Updated**: 2023-05-04

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 数据库大赛需求文档
>主题：车辆轨迹检索系统
>成员：朱珺阳、崔文博、刘泽宇
>指导老师：李莉

`v1.0`
## 一、需求分析
### 1、作品简介
自2020年1月份新冠疫情爆发以来，防疫工作已经成为了常态化的工作。“健康宝”、“通信大数据通行卡”等工具的背后就是运用人工智能、大数据技术对疫情监测分析，实施精准防控、科学防治提供数据支持。在当前疫情防控的关键时期，内防扩散，外防输出的任务仍然严峻。其中，对大量疫情数据信息，如何进行高效筛选、分析，也是疫情防控面临的挑战...因此，运用大数据进行疫情数据分析、当发现了确诊人员、疑似人员以及无症状人员时，首要工作就是发现其密切接触者，也就是根据这些人员所活动的区域发现与接触过的人员，因此本赛题主要关心时空数据检索任务。

### 2、应用场景
功能类似于‘附近的人’。可以实现查询指定地点**50米到2公里**附近某一车辆某一天的行动轨迹。疫情期间可以进行大数据车辆检索，对某一地点的车辆轨迹进行查询，同类型产品可以对比北京健康宝。也可以为警方提供车辆信息检索。
![markdown](http://zhu-junyang_admin.gitee.io/picture/2.png)

### 3、实现功能
* 指定时间段内精确查询附近50米到2公里的车辆
* 通过查询出来的车辆ID绘制轨迹
* 地点搜索

### 4、技术栈
前端：`Vue.js`+`Museui`+`高德地图API`

后端：
* 数据库：`mysql`,`hbase`,`redis`,`elasticsearch`
* 语言：`golang`,`python`
* 框架：`happybase`(python操作hbase)、`gorm` (go操作mysql)、`g-redis | g-elastic`(go操作redis、es)、jedis（java操作redis）
* 容器：docker

算法：Geohash算法，Google S2算法（正在研究）

## 二、数据库设计
主要字段如下：
* Geohash --> **geohash字符串**
* status --> **车辆状态（载客/未载客）**
* direction --> **车辆方向**
* lng --> **经度（WGS84坐标系）**
* lat --> **纬度（WGS84坐标系）**
* speed --> **车辆速度**
* time --> **经过时间**
* carno --> **车辆唯一标示码**
![markdown](http://zhu-junyang_admin.gitee.io/picture/4.png)
>mysql=> 一个季度为一个库。根据日期分表，查询具体日期先查表。主键：Geohash+random（10）组成的字符串，经纬度为WGS84转GCJ02后的经纬度，其余与原字段保持一致。mysql并不适合大数据量的查询，只能以主键作为索引（唯一性）来保证大数据量的查询效率。

>redis=> 采用redis中zset的数据结构和自带的地理位置geo，将车辆表示码字段作为key值，将经纬度和该车辆记录时间time存到value中，以此来更方便的查询车辆的轨迹。如果需要查询车辆轨迹只需要使用查询车辆key的value值即可，并返回到前端页面。由于redis适合大量数据的查询，所以该查询车辆轨迹的速度非常快。如果需要查询一段时间内的所有车辆，则将每个time作为key，value值保存经纬度和车辆标识码，即可利用事务来查询一段时间内的车辆都有哪些，所处位置在哪里。![输入图片说明](https://images.gitee.com/uploads/images/2020/1219/105707_4db7ed16_8154081.png "屏幕截图.png")

>hbase=>
hbase是一个nosql，以键值对存放的数据库，所以这个数据库最重要的就是设计一个能方便，快速的rowkey查询。
rowkey设计思路：
(1)统一存储
reverse(geohash) + time + car_id
revese(geohash)用于解决hbase热点问题，这样的rowkey可以选择位置时间以及司机id来进行查询。
(2)根据项目的分表想法(项目中使用的该想法)
分成两个表存储，建立存储索引数据的索引表和存储司机基本数据的司机id表
索引表rowkey=reverse(geohash) + time， 司机id写入索引表data: idi (i=1,2,3...)，同一时间同一地点可能有多个司机，这些司机id按顺序存入data列族中。司机id表rowkey = 分区编码 + 司机id， 基本信息如经纬度，车速等存入司机id表中的data: lng, lat等。
这样的想法来源于项目先查目标范围，再查司机的轨迹。数据库查询先依靠geohash和time来确定范围内的司机id，再用司机id去司机id表中得到基本信息。

>elasticsearch=>与mysql类似，可以进行geohash的模糊查询。暂未实际应用到项目中。

## 三、系统设计
#### 1、实现思路
```mermaid
graph TD
A[选择地点] --> B(选择时间段)
    B --> C{进行查询}
    C --> |查询到结果| D[列出所有车辆信息]
    C --> |未查询到结果| E[重新选择]
    E --> A
    D --> F(选择司机ID)
    F --> G(查询该司机一天内所有经纬度坐标)
    G --> H(绘制轨迹)
```

#### 2、技术路线

```mermaid
graph TD
A[前端获取用户选择的地点坐标以及时间段] --> B(发送到后端)
    B --> C{依据条件进行查询}
    C --> |查询到结果| D[返回数据]
    C --> |未查询到结果| E[返回错误信息并记录日志]
```

## 四、系统实施
>Geohash是一种检索时空大数据的算法，精度最高为12位。依据不同的精度区分不同范围。例如精度为8时，可以查询范围为50米的矩形或圆形。因此我们可以通过模糊查询geohash字符串的公共前缀，进行数据检索。

>高德地图是GCJ02坐标系，同时geohash的精度也并不高导致不同经纬度会有重复geohash值，因此需要对原始数据进行处理。首先转换原始坐标系为高德坐标系，其次geohash字符串需要连接random（10）字符串保证唯一性。最后将生成的csv存入数据库

>对于mysql，利用模糊查询like查找geohash公共前缀以此获取数据，对于mysql的索引来说，只有在like%查询时索引生效，正好符合需求。查询时间较快。司机id查询轨迹用了order by + where语句。

>redis
>对于redis而言，redis有自带的地理位置函数，通过zset有序集合将经纬度和车辆表示码或者时间存储为key-value形式。利用事务和geopos、georadius可实现许多功能如查询范围内的所有车辆。首先如果要查询车辆轨迹，用zrange返回车辆所有的时间，再用geopos返回车辆所在位置即可。![轨迹](https://images.gitee.com/uploads/images/2020/1219/110723_3bb1f15e_8154081.png "屏幕截图.png"),![轨迹](https://images.gitee.com/uploads/images/2020/1219/110659_c12bf95a_8154081.png "屏幕截图.png")

>如果要查找时间范围内的车辆，例如用户想要查询凌晨4点到凌晨7点的车辆有哪些。首先用keys查找符合的key，之后用zrange和geopos返回车辆的经纬度即可.![查找时间](https://images.gitee.com/uploads/images/2020/1219/112634_7b4f0ffa_8154081.png "屏幕截图.png"),![查找位置](https://images.gitee.com/uploads/images/2020/1219/112750_700ca3d9_8154081.png "屏幕截图.png")

>hbase
对于hbase，按照上面的rowkey设计进行存储，使用scan扫描+ROWFILTER过滤器进行rowkey查询查到范围内的司机id，再用司机id查询基本轨迹信息。

>对于大数据量的查询需要前后端协同，主流的解决方案是分页查询。

>前端利用高德API实现地点选择、轨迹绘制等功能。通过Vue.js响应式框架快速渲染后端数据并呈现在页面上，并用Muse-ui做界面美化。
![markdown](http://zhu-junyang_admin.gitee.io/picture/1.png)

## 五、系统测试与评价
>从开发角度来说。mysql主键索引可以做大数据量的简单查询，时间在1s以内，而order by + where 语句则消耗时间过长，原因是没有走主键索引，解决方案是where自查询走索引或强制走主键索引。

>从使用体验角度来说。从地点查询到显示轨迹的时间几乎可以忽略不计。轨迹绘制精确，较为直观。

## 六、未来展望
>Google S2算法
该算法对比geohash的优点：
30个级层跨度较小，中间变化相对平缓。
对于多边形的覆盖可以划分的更加细致。下图是一个例子。
  ![](http://zhu-junyang_admin.gitee.io/picture/5.png)
  上图是用相同 Level 的 Cell 覆盖了整个圣保罗城市。
  这是 Geohash 做不到的。
  关于Google S2参考文章：https://halfrost.com/go_spatial_search/

  >多边形复杂区域查询

## 七、开发过程问题
1. 学校服务器不能开放端口，不能访问外网，环境相对来说不好配置，导致服务器上的数据无法返回个人的前端界面。
2. 尝试本地或公有云配置hbase环境，linux内部直接配置环境非常复杂。通过docker容器解决。
3. sql语句没有做分页限制，一次查询数据量过大导致内存溢出。
4. 通过navicat导入csv到mysql会产生较大的日志占用系统空间。
5. geohash精度较差，每一位与每一位之间相差距离较远。
6. geohash有重复值，不能直接作为主键，因此必须加上random（xx）
7. 高德坐标系与原始坐标系不同，需进行转换。
8. 高德提供的API函数均为异步函数，对于前端来说需要等待异步（async await）结果，否则无法同步获取数据，导致数据无法渲染。
9. 使用python代码往服务器上的hbase导入数据过慢，需要改进。
10. hbase使用get查询一条语句速度很块，但scan扫描全表查询范围数据过慢，需要优化。