ShareBike Project

发表于 | 分类于

引言：

《共享单车可视化项目》——基于Spring Boot快速构建项目。包含核心的SSM框架即：SpringMVC，Spring，Mybatis，基于Maven项目管理工具。

整个流程大致为：地址栏输入网址，前端控制器（Controller）拦截请求进行处理，调用服务层（Service）方法，服务层再调用数据访问层（Dao/Mapper）与数据库进行交互获取数据，进行返回。

表结构：

字段 类型 长度 为空 说明
company_id string 6 否 企业标识
bicycle_id string 14 否 车辆标识
longitude decimal 10 否 车辆实时坐标-经度
latitude decimal 9 否 车辆实时坐标-纬度
lock_status number 1 否 车锁状态：车锁实时状态 0-开，1-关
update_time timestamp 15 否 更新时间：当前UTC时间戳

补充：数据为单车当天采集的一小时内的位置信息。

sql建表

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS `history_bike_status`;
CREATE TABLE `history_bike_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bicycle_id` varchar(50) NOT NULL,
  `longitude` decimal(11,6) DEFAULT NULL,
  `latitude` decimal(11,6) DEFAULT NULL,
  `company_id` varchar(50) DEFAULT NULL,
  `lock_status` tinyint(1) DEFAULT NULL,
  `upload_time` datetime DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1058621 DEFAULT CHARSET=utf8;

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
server:
  port: 8080

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/sharebike?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456

mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml
  type-aliases-package: com.sstl.sharebikevisualization.model
  config-location: classpath:mybatis/mybatis-config.xml

entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
public class LogLatObject {
    private Double log;

    private Double lat;

    private String bicycleId;

    private String companyId;

    public Double getLog() {
        return log;
    }

    public void setLog(Double log) {
        this.log = log;
    }

    public Double getLat() {
        return lat;
    }

    public void setLat(Double lat) {
        this.lat = lat;
    }

    public String getBicycleId() {
        return bicycleId;
    }

    public void setBicycleId(String bicycleId) {
        this.bicycleId = bicycleId;
    }

    public String getCompanyId() {
        return companyId;
    }

    public void setCompanyId(String companyId) {
        this.companyId = companyId;
    }
}

mapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@Mapper
public interface BikeMapper {
    /**
     * 获取数据库中单车数量
     * @return
     */
    @Select("SELECT Count(DISTINCT bicycle_id) FROM history_bike_status")
    Integer getTotalBikeAmount();

    /**
     * 6.13单车最后位置
     * @return
     */
    @Select("SELECT longitude lng, latitude lat, bicycle_id "  +
            "FROM " +
            "( SELECT * FROM history_bike_status AS hbs WHERE hbs.company_id = #{companyId} AND "+
            "hbs.upload_time BETWEEN '2018-06-13 00:00:00' AND '2018-06-14 00:00:00' " +
            "    ORDER BY hbs.upload_time DESC " +
            " ) temp "+
            "GROUP BY bicycle_id")
    List<LngLatObject> getLastPositionByCompanyId(String companyId);

    List<LngLatObject> getLastPositionByCompanyIdAndTime(String companyId, String searchBeginDate, String searchEndDate);

    List<LngLatObject> getAllPositionByCompanyIdAndTime(String companyId, String searchBeginDate, String searchEndDate);
}

mybatis自定义查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<select id="getLastPositionByCompanyIdAndTime" resultMap="BaseResultMap">
    SELECT
    longitude log,
    latitude lat,
    bicycle_id
    FROM
    (
        SELECT *
        FROM
        history_bike_status AS hbs
        WHERE
        hbs.company_id = #{arg0} AND hbs.upload_time BETWEEN #{arg1}
        AND #{arg2}
        ORDER BY hbs.upload_time DESC
    ) temp
    GROUP BY bicycle_id
</select>
1
2
3
4
5
6
7
8
9
10
11
12
<select id="getAllPositionByCompanyIdAndTime" resultMap="BaseResultMap">
    SELECT
    longitude log,
    latitude lat,
    bicycle_id
    FROM
    history_bike_status AS hbs
    WHERE
    hbs.company_id = #{arg0}
    AND hbs.upload_time BETWEEN #{arg1}
    AND #{arg2}
</select>

service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@Service
public class BikeServiceImpl implements BikeService {

    @Autowired
    private BikeMapper bikeMapper;

    @Override
    public Integer getTotalBikeAmount() {
        return bikeMapper.getTotalBikeAmount();
    }

    @Override
    public List<LngLatObject> getLastPositionByCompanyId(String companyId) {
        return bikeMapper.getLastPositionByCompanyId(companyId);
    }

    @Override
    public List<LngLatObject> getLastPositionByCompanyIdAndTime(String companyId, String searchDate) {
        String searchBeginDate = searchDate+" 00:00:00";
        String searchEndDate = searchDate+" 23:59:59";
        return bikeMapper.getLastPositionByCompanyIdAndTime(companyId,searchBeginDate,searchEndDate);
    }

    @Override
    public List<LngLatObject> getAllPositionByCompanyIdAndTime(String companyId, String searchDate) {
        String searchBeginDate = searchDate+" 00:00:00";
        String searchEndDate = searchDate+" 23:59:59";
        return bikeMapper.getAllPositionByCompanyIdAndTime(companyId,searchBeginDate,searchEndDate);
    }
}

controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
@Controller
public class IndexController {

    @Autowired
    private BikeService bikeService;

    private static String SEARCH_DATE = "2018-06-13";

    @RequestMapping(value = "/getOfoLastPositionData", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<JsonResult> getOfoData(){
        JsonResult r = new JsonResult();
        try {
            List<LngLatObject> lngLatOfoObjects = bikeService.getLastPositionByCompanyIdAndTime("05ofo",SEARCH_DATE);
            System.out.println(SEARCH_DATE);
            r.setResult(lngLatOfoObjects);
            r.setStatus("ok");
        } catch (Exception e) {
            r.setResult(e.getClass().getName() + ":" + e.getMessage());
            r.setStatus("error");
            e.printStackTrace();
        }
        return ResponseEntity.ok(r);
    }

    @RequestMapping(value = "/getMobikeLastPositionData", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<JsonResult> getMobikeData(){
        JsonResult r = new JsonResult();
        try {
            List<LngLatObject> lngLatMobikeObjects = bikeService.getLastPositionByCompanyIdAndTime("07mobike",SEARCH_DATE);
            r.setResult(lngLatMobikeObjects);
            r.setStatus("ok");
        } catch (Exception e) {
            r.setResult(e.getClass().getName() + ":" + e.getMessage());
            r.setStatus("error");
            e.printStackTrace();
        }
        return ResponseEntity.ok(r);
    }

    @RequestMapping(value = "/getXqLastPositionData", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<JsonResult> getXqData(){
        JsonResult r = new JsonResult();
        try {
            List<LngLatObject> lngLatXqObjects = bikeService.getLastPositionByCompanyIdAndTime("01xqcx",SEARCH_DATE);
            r.setResult(lngLatXqObjects);
            r.setStatus("ok");
        } catch (Exception e) {
            r.setResult(e.getClass().getName() + ":" + e.getMessage());
            r.setStatus("error");
            e.printStackTrace();
        }
        return ResponseEntity.ok(r);
    }

    @RequestMapping(value = "/getOfoAllPositionByTime", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<JsonResult> getOfoAllPositionByTime(){
        JsonResult r = new JsonResult();
        try {
            List<LngLatObject> lngLatOfoObjects = bikeService.getAllPositionByCompanyIdAndTime("05ofo",SEARCH_DATE);
            r.setResult(lngLatOfoObjects);
            r.setStatus("ok");
        } catch (Exception e) {
            r.setResult(e.getClass().getName() + ":" + e.getMessage());
            r.setStatus("error");
            e.printStackTrace();
        }
        return ResponseEntity.ok(r);
    }

    @RequestMapping(value = "/getMobikeAllPositionByTime", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<JsonResult> getMobikeAllPositionByTime(){
        JsonResult r = new JsonResult();
        try {
            List<LngLatObject> lngLatOfoObjects = bikeService.getAllPositionByCompanyIdAndTime("07mobike",SEARCH_DATE);
            r.setResult(lngLatOfoObjects);
            r.setStatus("ok");
        } catch (Exception e) {
            r.setResult(e.getClass().getName() + ":" + e.getMessage());
            r.setStatus("error");
            e.printStackTrace();
        }
        return ResponseEntity.ok(r);
    }

    @RequestMapping(value = "/getXqAllPositionByTime", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<JsonResult> getXqAllPositionByTime(){
        JsonResult r = new JsonResult();
        try {
            List<LngLatObject> lngLatOfoObjects = bikeService.getAllPositionByCompanyIdAndTime("01xqcx",SEARCH_DATE);
            r.setResult(lngLatOfoObjects);
            r.setStatus("ok");
        } catch (Exception e) {
            r.setResult(e.getClass().getName() + ":" + e.getMessage());
            r.setStatus("error");
            e.printStackTrace();
        }
        return ResponseEntity.ok(r);
    }
}

页面展示基于html实现，展示，分区域，弹窗等基本展示功能就完结。

下面是一个基于缩放的点聚合优化；

点聚合优化

问题：加载大量点时候卡顿

缘由： 点是否聚合的判断是计算其是否在聚合点的范围内；百度地图开发文档的操作是，是在加点过程中进行DOM操作

document文档 object 对象 model模型，Dom翻译中文：文档对象模型

dom操作就是元素节点操作,指的是改变html的标签结构，它有两种情况：

  1. 移动现有标签的位置；
  2. 将新创建的标签插入到现有的标签中 。

解决： addMarker() 方法内不停的去进行dom操作是最主要的弊端，所以在只要批量加点时，屏蔽DOM操作，计算完成后，再一次遍历DOM显示，速度就极大提升。

参考链接： 知乎 ，博客园，博主

  • 后续的各种可视化功能也没开发出来