Grafana 使用高德地图

hipudding commented 2 weeks ago

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

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

先展示下成果吧：

使用方法

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

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

hipudding commented 2 weeks ago

原理

步骤： 以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;


2. 页面的SQL修改：
```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 commented 2 weeks ago

所有与地图有关的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 commented 2 weeks ago

幸苦了。收益学习。

hipudding / teslamate_fix_addrs