Grafana 使用高德地图 #7

hipudding · 2024-06-14T14:27:40Z

OSM地图在国内体验不好，最近研究了一下，Grafana中的OSM地图可以更换为高德地图，大概的思路是：

把grafana地图插件的地图图层更换为高德瓦片。
在数据库中加入存储过程，将wgs84转gcj02坐标。

先展示下成果吧：

使用方法

使用修改的grafana
将docker-compose中的teslamate/grafana 修改为 hipudding/grafana
重启

docker compose restart

PS：没有配置action自动同步社区版本，如果有新版本需求请提issue

The text was updated successfully, but these errors were encountered:

hipudding · 2024-06-14T14:35:31Z

原理

步骤：
以visited页面为例：

添加存储过程存储过程：

CREATE OR REPLACE FUNCTION transformLat(x DOUBLE PRECISION, y DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    ret DOUBLE PRECISION;
BEGIN
    ret := -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * sqrt(abs(x));
    ret := ret + (20.0 * sin(6.0 * x * pi()) + 20.0 * sin(2.0 * x * pi())) * 2.0 / 3.0;
    ret := ret + (20.0 * sin(y * pi()) + 40.0 * sin(y / 3.0 * pi())) * 2.0 / 3.0;
    ret := ret + (160.0 * sin(y / 12.0 * pi()) + 320 * sin(y * pi() / 30.0)) * 2.0 / 3.0;
    RETURN ret;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION transformLon(x DOUBLE PRECISION, y DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    ret DOUBLE PRECISION;
BEGIN
    ret := 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * sqrt(abs(x));
    ret := ret + (20.0 * sin(6.0 * x * pi()) + 20.0 * sin(2.0 * x * pi())) * 2.0 / 3.0;
    ret := ret + (20.0 * sin(x * pi()) + 40.0 * sin(x / 3.0 * pi())) * 2.0 / 3.0;
    ret := ret + (150.0 * sin(x / 12.0 * pi()) + 300.0 * sin(x / 30.0 * pi())) * 2.0 / 3.0;
    RETURN ret;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delta(lat DOUBLE PRECISION, lon DOUBLE PRECISION)
RETURNS TABLE (dLat DOUBLE PRECISION, dLon DOUBLE PRECISION) AS $$
DECLARE
    a CONSTANT DOUBLE PRECISION := 6378245.0;
    ee CONSTANT DOUBLE PRECISION := 0.00669342162296594323;
    radLat DOUBLE PRECISION;
    magic DOUBLE PRECISION;
    sqrtMagic DOUBLE PRECISION;
BEGIN
    radLat := lat / 180.0 * pi();
    magic := sin(radLat);
    magic := 1 - ee * magic * magic;
    sqrtMagic := sqrt(magic);
    
    dLat := transformLat(lon - 105.0, lat - 35.0);
    dLon := transformLon(lon - 105.0, lat - 35.0);
    
    dLat := (dLat * 180.0) / ((a * (1 - ee)) / (magic * sqrtMagic) * pi());
    dLon := (dLon * 180.0) / (a / sqrtMagic * cos(radLat) * pi());
    
    RETURN QUERY SELECT dLat, dLon;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION wgs84_to_gcj02(wgsLat DOUBLE PRECISION, wgsLon DOUBLE PRECISION)
RETURNS TABLE (gcjLat DOUBLE PRECISION, gcjLon DOUBLE PRECISION) AS $function$
DECLARE
    dLat DOUBLE PRECISION;
    dLon DOUBLE PRECISION;
BEGIN
    IF wgsLat < 0 OR wgsLat > 60.0 OR wgsLon < 72.004 OR wgsLon > 137.8347 THEN
        RETURN QUERY SELECT wgsLat, wgsLon;
    ELSE
        SELECT delta.dLat, delta.dLon INTO dLat, dLon FROM delta(wgsLat, wgsLon);
        SELECT wgsLat + dLat, wgsLon + dLon INTO gcjLat, gcjLon;
        RETURN QUERY SELECT gcjLat, gcjLon;
    END IF;
END;
$function$ LANGUAGE plpgsql;

页面的SQL修改：

WITH positions AS (
    SELECT
        date_trunc('minute', date) AS time,
        latitude AS ori_latitude,
        longitude AS ori_longitude
    FROM
        positions
    WHERE
        car_id = $car_id AND $__timeFilter(date)
)
SELECT
    time,
    avg(gcjLat) as latitude,
    avg(gcjLon) as longitude
FROM (
    SELECT
        time,
        (wgs84_to_gcj02(ori_latitude, ori_longitude)).gcjLat AS gcjLat,
        (wgs84_to_gcj02(ori_latitude, ori_longitude)).gcjLon AS gcjLon
    FROM
        positions
) AS transformed_positions
GROUP BY time 
ORDER BY time;

修改地图配置：

最后贴上visited.json配置文件的修改吧：

--- visited.json        2024-05-20 12:10:07.000000000 +0000
+++ visited.json.new    2024-06-14 14:30:16.076507632 +0000
@@ -129,10 +129,10 @@
       "options": {
         "basemap": {
           "config": {
-            "server": "streets"
+            "url": "http://wprd0{1-4}.is.autonavi.com/appmaptile?x={x}&y={y}&z={z}&lang=zh_cn&size=1&scl=1&style=7"
           },
           "name": "Layer 0",
-          "type": "esri-xyz"
+          "type": "xyz"
         },
         "controls": {
           "mouseWheelZoom": true,
@@ -212,7 +212,7 @@
           "format": "table",
           "hide": false,
           "rawQuery": true,
-          "rawSql": "SELECT\n  date_trunc('minute', date) as time,\n  avg(latitude) as latitude,\n  avg(longitude) as longitude\nFROM\n  positions\nWHERE\n  car_id = $car_id AND $__timeFilter(date)\nGROUP BY 1\nORDER BY 1",
+          "rawSql": "WITH positions AS (\n    SELECT\n        date_trunc('minute', date) AS time,\n        latitude AS ori_latitude,\n        longitude AS ori_longitude\n    FROM\n        positions\n    WHERE\n        car_id = $car_id AND $__timeFilter(date)\n)\nSELECT\n    time,\n    avg(gcjLat) as latitude,\n    avg(gcjLon) as longitude\nFROM (\n    SELECT\n        time,\n        (wgs84_to_gcj02(ori_latitude, ori_longitude)).gcjLat AS gcjLat,\n        (wgs84_to_gcj02(ori_latitude, ori_longitude)).gcjLon AS gcjLon\n    FROM\n        positions\n) AS transformed_positions\nGROUP BY time \nORDER BY time;",
           "refId": "Positions",
           "sql": {
             "columns": [

hipudding · 2024-06-15T14:54:25Z

所有与地图有关的SQL修改如下：

charging stats

WITH converted_positions AS (
  SELECT
    charge.id AS charge_id,
    COALESCE(
      geofence.name,
      CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)
    ) AS loc_nm,
    (wgs84_to_gcj02(position.latitude, position.longitude)).gcjLat AS gcj_latitude,
    (wgs84_to_gcj02(position.latitude, position.longitude)).gcjLon AS gcj_longitude,
    charge.charge_energy_added
  FROM
    charging_processes charge
  LEFT JOIN addresses address ON charge.address_id = address.id
  LEFT JOIN positions position ON charge.position_id = position.id
  LEFT JOIN geofences geofence ON charge.geofence_id = geofence.id
  WHERE
    $__timeFilter(charge.start_date)
    AND charge.car_id = $car_id
),
charge_data AS (
  SELECT
    loc_nm,
    AVG(gcj_latitude) AS latitude,
    AVG(gcj_longitude) AS longitude,
    SUM(charge_energy_added) AS chg_total,
    COUNT(*) AS charges
  FROM
    converted_positions
  GROUP BY
    loc_nm
)
SELECT
  loc_nm,
  latitude,
  longitude,
  chg_total,
  chg_total * 1.0 / (SELECT SUM(chg_total) FROM charge_data) * 100 AS pct,
  charges
FROM
  charge_data;

trip

WITH converted_positions AS (
  SELECT
    $__timeGroup(date, '5s') AS time,
    (wgs84_to_gcj02(latitude, longitude)).gcjLat AS gcj_latitude,
    (wgs84_to_gcj02(latitude, longitude)).gcjLon AS gcj_longitude
  FROM
    positions
  WHERE
    car_id = $car_id AND
    $__timeFilter(date)
)
SELECT
  time,
  avg(gcj_latitude) AS latitude,
  avg(gcj_longitude) AS longitude
FROM
  converted_positions
GROUP BY
  time
ORDER BY
  time ASC;

visited

WITH converted_positions AS (
  SELECT
    date_trunc('minute', date) as time,
    (wgs84_to_gcj02(latitude, longitude)).gcjLat AS latitude,
    (wgs84_to_gcj02(latitude, longitude)).gcjLon AS longitude
  FROM
    positions
  WHERE
    car_id = $car_id AND $__timeFilter(date)
)
SELECT
  time,
  avg(latitude) as latitude,
  avg(longitude) as longitude
FROM
  converted_positions
GROUP BY
  time
ORDER BY
  time;

charge details

SELECT
	$__time(date),
	unnest(ARRAY[(wgs84_to_gcj02(p.latitude, p.longitude)).gcjLat, (wgs84_to_gcj02(p.latitude, p.longitude)).gcjLat]) AS latitude,
	unnest(ARRAY[(wgs84_to_gcj02(p.latitude, p.longitude)).gcjLon, (wgs84_to_gcj02(p.latitude, p.longitude)).gcjLon]) AS longitude
FROM
	charging_processes c
	JOIN positions p ON c.position_id = p.id
WHERE
	$__timeFilter(date)
	AND c.car_id = $car_id;

drive details

SELECT
  $__time(date),
  (wgs84_to_gcj02(latitude, longitude)).gcjLat AS latitude,
  (wgs84_to_gcj02(latitude, longitude)).gcjLon AS longitude
FROM positions
WHERE 
  car_id = $car_id AND 
  $__timeFilter(date)
ORDER BY 
  date ASC

odinms · 2024-06-19T04:25:33Z

幸苦了。收益学习。

youguangbin · 2024-07-16T07:03:47Z

太强了，可以解决在车机不能显示地图的问题

DongZhaoXiong · 2024-07-17T07:56:23Z

大佬，请问只要将原本teslamate中的grafana替换就可以吗，还需要部署tesla-fix_addrs 这个镜像吗

hipudding · 2024-07-17T08:19:41Z

大佬，请问只要将原本teslamate中的grafana替换就可以吗，还需要部署tesla-fix_addrs 这个镜像吗

这是两个功能。替换grafana是为了显示为高德地图。 fix_addrs是为了用高德地图解析地址存到数据库。这个根据需要选择吧。

DongZhaoXiong · 2024-07-17T09:06:29Z

大佬，请问只要将原本teslamate中的grafana替换就可以吗，还需要部署tesla-fix_addrs 这个镜像吗

这是两个功能。替换grafana是为了显示为高德地图。 fix_addrs是为了用高德地图解析地址存到数据库。这个根据需要选择吧。
了解，所以对于地址解析的问题，要么使用http_proxy,要么直接使用高德地图解决网络block的问题，对吧

try-to-fly · 2024-07-17T09:07:54Z

你好，替换为hipudding/grafana:lastest 后，trip界面的地图有报错，如图所示

hipudding · 2024-07-17T09:08:38Z

了解，所以对于地址解析的问题，要么使用http_proxy,要么直接使用高德地图解决网络block的问题，对吧

解析是两个功能，还是用osm从gps解析到地址，这个不方便修改，不想影响本身的逻辑。高德地图是把osm的地址再润色一下，让我们看起来更舒服。

DongZhaoXiong · 2024-07-17T09:10:03Z

了解，所以对于地址解析的问题，要么使用http_proxy,要么直接使用高德地图解决网络block的问题，对吧

解析是两个功能，还是用osm从gps解析到地址，这个不方便修改，不想影响本身的逻辑。高德地图是把osm的地址再润色一下，让我们看起来更舒服。

所以proxy还是要添加的，对吗，也就是地址的数据源没有换掉

DongZhaoXiong · 2024-07-17T09:11:05Z

你好，替换为hipudding/grafana:lastest 后，trip界面的地图有报错，如图所示

没有创建对应的function，你需要连接postgres 执行作者的命令

hipudding · 2024-07-17T09:13:38Z

你好，替换为hipudding/grafana:lastest 后，trip界面的地图有报错，如图所示

存储过程没生效，重启下grafana容器再试试。
或者把这个存储过程安装下： https://github.com/hipudding/teslamate/blob/v1.30.1.amap/grafana/proc.sql

hipudding · 2024-07-17T09:14:24Z

你好，替换为hipudding/grafana:lastest 后，trip界面的地图有报错，如图所示

没有创建对应的function，你需要连接postgres 执行作者的命令

容器启动会自动安装的，现在不需要手动执行了。

zhangqi-net · 2024-08-14T05:31:20Z

我个人使用感觉更新一下visited.json和CurrentState.json就可以了，下面列出CurrentState.json 的关键diff：

734,736c773
<           "config": {
<             "url": "http://wprd0{1-4}.is.autonavi.com/appmaptile?x={x}&y={y}&z={z}&lang=zh_cn&size=1&scl=1&style=7"
<           },
---
>           "config": {},
738c775
<           "type": "xyz"
---
>           "type": "default"

829c866
<           "rawSql": "SELECT\n  $__time(date),\n  (wgs84_to_gcj02(latitude, longitude)).gcjLat AS latitude,\n  (wgs84_to_gcj02(latitude, longitude)).gcjLon AS longitude\nFROM positions\nWHERE \n  car_id = $car_id AND \n  $__timeFilter(date)\nORDER BY \n  date ASC",
---
>           "rawSql": "SELECT\n  $__time(date),\n  latitude,\n  longitude\nFROM positions\nWHERE \n  car_id = $car_id AND \n  $__timeFilter(date)\nORDER BY \n  date ASC",

hipudding · 2024-08-14T06:02:29Z

直接使用 rhipudding/gafana的镜像，目前和社区最新release版本是匹配的

phphi · 2024-09-02T01:41:23Z

牛逼，已经部署成功，不过还有2个小问题。
1.有bug，在做了ssl后的https地址的teslamate，控制台报错，页面中出现了http地址，需要改成https的高德地图URL。
The page at '' was loaded over HTTPS, but requested an insecure element ''. This request was automatically upgraded to HTTPS, For more information see

2.你用的瓦片源好像是旧版的，视觉体验不太好，可以把地址修复成webrd。见下边替换方法
http://wprd0{1-4}.is.autonavi.com/appmaptile?x={x}&y={y}&z={z}&lang=zh_cn&size=1&scl=1&style=7
替换成
https://webrd0{1-4}.is.autonavi.com/appmaptile?lang=zh_cn&size=1&scale=1&style=7&x={x}&y={y}&z={z}

hipudding · 2024-09-03T01:38:03Z

@phphi 多谢，我更换下

phphi · 2024-09-03T01:54:17Z

@phphi 多谢，我更换下

style=8可能更合适，8为道路详图，7是简图

hipudding · 2024-09-03T01:56:09Z

@phphi 多谢，我更换下

style=8可能更合适，8为道路详图，7是简图

ok

hipudding · 2024-09-03T02:03:29Z

@phphi 已更新。 docker compose pull && docker compose up -d即可

h4rk8s · 2024-09-05T07:23:44Z

牛逼！

This was referenced Jun 14, 2024

Please add Baidu Map teslamate-org/teslamate#3915

Closed

Custom map source teslamate-org/teslamate#3900

Closed

hipudding closed this as completed Jan 26, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Grafana 使用高德地图 #7

Grafana 使用高德地图 #7

hipudding commented Jun 14, 2024 •

edited

Loading

hipudding commented Jun 14, 2024 •

edited

Loading

hipudding commented Jun 15, 2024 •

edited

Loading

odinms commented Jun 19, 2024

youguangbin commented Jul 16, 2024

DongZhaoXiong commented Jul 17, 2024

hipudding commented Jul 17, 2024

DongZhaoXiong commented Jul 17, 2024

try-to-fly commented Jul 17, 2024

hipudding commented Jul 17, 2024

DongZhaoXiong commented Jul 17, 2024

DongZhaoXiong commented Jul 17, 2024

hipudding commented Jul 17, 2024

hipudding commented Jul 17, 2024

zhangqi-net commented Aug 14, 2024

hipudding commented Aug 14, 2024

phphi commented Sep 2, 2024

hipudding commented Sep 3, 2024

phphi commented Sep 3, 2024

hipudding commented Sep 3, 2024

hipudding commented Sep 3, 2024

h4rk8s commented Sep 5, 2024

Grafana 使用高德地图 #7

Grafana 使用高德地图 #7

Comments

hipudding commented Jun 14, 2024 • edited Loading

hipudding commented Jun 14, 2024 • edited Loading

原理

hipudding commented Jun 15, 2024 • edited Loading

charging stats

trip

visited

charge details

drive details

odinms commented Jun 19, 2024

youguangbin commented Jul 16, 2024

DongZhaoXiong commented Jul 17, 2024

hipudding commented Jul 17, 2024

DongZhaoXiong commented Jul 17, 2024

try-to-fly commented Jul 17, 2024

hipudding commented Jul 17, 2024

DongZhaoXiong commented Jul 17, 2024

DongZhaoXiong commented Jul 17, 2024

hipudding commented Jul 17, 2024

hipudding commented Jul 17, 2024

zhangqi-net commented Aug 14, 2024

hipudding commented Aug 14, 2024

phphi commented Sep 2, 2024

hipudding commented Sep 3, 2024

phphi commented Sep 3, 2024

hipudding commented Sep 3, 2024

hipudding commented Sep 3, 2024

h4rk8s commented Sep 5, 2024

hipudding commented Jun 14, 2024 •

edited

Loading

hipudding commented Jun 14, 2024 •

edited

Loading

hipudding commented Jun 15, 2024 •

edited

Loading