{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import pandas as pd\n",
    "from LIB.BACKEND import DBManager, Log\n",
    "from pandasql import sqldf "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = (datetime.datetime.now()+datetime.timedelta(days=-7)).strftime('%Y-%m-%d %H:%M:%S')\n",
    "end_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbManager = DBManager.DBManager()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#idlist=['TJMCL120502305010','TJMCL120502305022','TJMCL120502305038','TJMCL120502305026']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "idlist=['PJXCLL128N22C5001','PJXCLL128N22C5002','PJXCLL128N22C5003','PJXCLL128N22C5004']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bms=pd.DataFrame()\n",
    "df_gps=pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for sn in idlist:\n",
    "    df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms','gps'])\n",
    "    df_data_bms=df_data['bms'] \n",
    "    df_data_bms[\"sn\"]=sn\n",
    "    df_data_gps=df_data['gps']\n",
    "    df_data_gps[\"sn\"]=sn\n",
    "    df_bms=pd.concat([df_bms,df_data_bms],axis=0,ignore_index=True)\n",
    "    df_gps=pd.concat([df_gps,df_data_gps],axis=0,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "##bms选取字段\n",
    "df_bms_1=df_bms[[\"时间戳\",\"SOC[%]\",\"sn\"]]\n",
    "df_bms_1.columns=[\"data_time\",\"soc\",\"sn\"]\n",
    "df_bms_2=df_bms_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11389, 3)"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bms_2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "##gps选取字段\n",
    "df_gps_1=df_gps[[\"时间戳\",\"纬度\",\"经度\",\"sn\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gps_1.columns=[\"data_time\",\"lat\",\"lon\",\"sn\"]\n",
    "df_gps_2=df_gps_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "##soc 数据 关联gps 数据\n",
    "sqlc6=\"select a.*,  b.lat  lat ,b.lon  lon from  df_bms_2 a \\\n",
    "left join  df_gps_2 b  on a.sn=b.sn and  abs((JULIANDAY(a.data_time)-JULIANDAY(b.data_time))*24*60*60)<5 \""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandasql import sqldf "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "pysqldf = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "soc_gps= pysqldf(sqlc6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "##核实数据\n",
    "soc_gps.to_csv(\"soc_gps/soc_gps.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11389, 5)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soc_gps.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "##筛选数据,排除空值和等于零的值\n",
    "sqlc7=\"select *  from  soc_gps  where  lat>0 and lon>0 and  soc>0 \""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "soc_gps_add= pysqldf(sqlc7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "##核实数据\n",
    "soc_gps_add.to_csv(\"soc_gps/soc_gps_add.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "##预处理字段\n",
    "data=soc_gps_add[[\"lat\",\"lon\",\"soc\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "##坐标转化，GPS 转火星\n",
    "import math\n",
    "pi = 3.1415926535897932384626\n",
    "a = 6378245.0\n",
    "ee = 0.00669342162296594323\n",
    "\n",
    "def wgs_gcj(lat, lon,soc):\n",
    "    dLat = transform_lat(lon - 105.0, lat - 35.0)\n",
    "    dLon = transform_lon(lon - 105.0, lat - 35.0)\n",
    "    radLat = lat / 180.0 * pi\n",
    "    magic = math.sin(radLat)\n",
    "    magic = 1 - ee * magic * magic\n",
    "    sqrtMagic = math.sqrt(magic)\n",
    "    dLat = (dLat * 180.0) / ((a * (1 - ee)) / (magic * sqrtMagic) * pi)\n",
    "    dLon = (dLon * 180.0) / (a / sqrtMagic * math.cos(radLat) * pi)\n",
    "    mgLat = lat + dLat\n",
    "    mgLon = lon + dLon\n",
    "    return [mgLat,mgLon,soc]\n",
    "\n",
    "\n",
    "def transform_lat(x, y):\n",
    "\n",
    "    ret = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * math.sqrt(abs(x))\n",
    "    ret += (20.0 * math.sin(6.0 * x * pi) + 20.0 * math.sin(2.0 * x * pi)) * 2.0 / 3.0\n",
    "    ret += (20.0 * math.sin(y * pi) + 40.0 * math.sin(y / 3.0 * pi)) * 2.0 / 3.0\n",
    "    ret += (160.0 * math.sin(y / 12.0 * pi) + 320 * math.sin(y * pi / 30.0)) * 2.0 / 3.0\n",
    "    return ret\n",
    "\n",
    "def transform_lon(x, y):\n",
    "\n",
    "    ret = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * math.sqrt(abs(x))\n",
    "    ret += (20.0 * math.sin(6.0 * x * pi) + 20.0 * math.sin(2.0 * x * pi)) * 2.0 / 3.0\n",
    "    ret += (20.0 * math.sin(x * pi) + 40.0 * math.sin(x / 3.0 * pi)) * 2.0 / 3.0\n",
    "    ret += (150.0 * math.sin(x / 12.0 * pi) + 300.0 * math.sin(x / 30.0 * pi)) * 2.0 / 3.0\n",
    "    return ret\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "core=[29.934539,107.79961,0]\n",
    "core=wgs_gcj(core[0], core[1],core[2]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[29.93186073070887, 107.80401736670477, 0]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "core"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "locations = data.values.tolist()\n",
    "\n",
    "zz=[ wgs_gcj(i[0], i[1],i[2])  for i  in  locations ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "import folium\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from folium.plugins import HeatMap\n",
    "\n",
    "m = folium.Map(core[:2], zoom_start=16,\n",
    "               tiles='http://webrd02.is.autonavi.com/appmaptile?lang=zh_cn&size=1&scale=1&style=7&x={x}&y={y}&z={z}',\n",
    "               attr='default') \n",
    "\n",
    "\n",
    "HeatMap(zz).add_to(m) \n",
    "\n",
    "\n",
    "m.save(\"soc_gps/trace1227.html\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[29.93186073070887, 107.80401736670477]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "core[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "drive_statics_l_add=pd.read_csv(\"qiyuandata/drive_statics_l_add.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "##充电路径绘制\n",
    "##寻找充电的时间段\n",
    "sql8=\"select data_time_b,data_time_e, sn,drive_block  from drive_statics_l_add where end_change=1\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "drive_charge= pysqldf(sql8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drive_charge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql9=\"select a.data_time,a.sn,a.lat,a.lon,b.drive_block  from soc_gps_add a left join drive_charge b on a.sn=b.sn \\\n",
    "    and a.data_time  < b.data_time_e  and   a.data_time>b.data_time_b\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "drive_charge_gps=pysqldf(sql9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "##核实数据\n",
    "drive_charge_gps.to_csv(\"qiyuandata/drive_charge_gps.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "##驾驶段\n",
    "drive_group=drive_charge_gps[\"drive_block\"].values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "drive_group=set(drive_group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "drive_group=[int(i) for i in drive_group if i >0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[73, 70, 71]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drive_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "\n",
    "pi = 3.1415926535897932384626\n",
    "a = 6378245.0\n",
    "ee = 0.00669342162296594323\n",
    "\n",
    "\n",
    "def wgs_gcj(lat, lon):\n",
    "\n",
    "    dLat = transform_lat(lon - 105.0, lat - 35.0)\n",
    "    dLon = transform_lon(lon - 105.0, lat - 35.0)\n",
    "    radLat = lat / 180.0 * pi\n",
    "    magic = math.sin(radLat)\n",
    "    magic = 1 - ee * magic * magic\n",
    "    sqrtMagic = math.sqrt(magic)\n",
    "    dLat = (dLat * 180.0) / ((a * (1 - ee)) / (magic * sqrtMagic) * pi)\n",
    "    dLon = (dLon * 180.0) / (a / sqrtMagic * math.cos(radLat) * pi)\n",
    "    mgLat = lat + dLat\n",
    "    mgLon = lon + dLon\n",
    "    return [mgLat,mgLon]\n",
    "\n",
    "\n",
    "def transform_lat(x, y):\n",
    "\n",
    "    ret = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * math.sqrt(abs(x))\n",
    "    ret += (20.0 * math.sin(6.0 * x * pi) + 20.0 * math.sin(2.0 * x * pi)) * 2.0 / 3.0\n",
    "    ret += (20.0 * math.sin(y * pi) + 40.0 * math.sin(y / 3.0 * pi)) * 2.0 / 3.0\n",
    "    ret += (160.0 * math.sin(y / 12.0 * pi) + 320 * math.sin(y * pi / 30.0)) * 2.0 / 3.0\n",
    "    return ret\n",
    "\n",
    "\n",
    "def transform_lon(x, y):\n",
    "\n",
    "    ret = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * math.sqrt(abs(x))\n",
    "    ret += (20.0 * math.sin(6.0 * x * pi) + 20.0 * math.sin(2.0 * x * pi)) * 2.0 / 3.0\n",
    "    ret += (20.0 * math.sin(x * pi) + 40.0 * math.sin(x / 3.0 * pi)) * 2.0 / 3.0\n",
    "    ret += (150.0 * math.sin(x / 12.0 * pi) + 300.0 * math.sin(x / 30.0 * pi)) * 2.0 / 3.0\n",
    "    return ret\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = folium.Map([29.934539,107.79961], zoom_start=16,\n",
    "               tiles='http://webrd02.is.autonavi.com/appmaptile?lang=zh_cn&size=1&scale=1&style=7&x={x}&y={y}&z={z}',\n",
    "               attr='default') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(drive_group)):\n",
    "    q=drive_group[i]\n",
    "    qq=[\"red\",\"blue\",\"green\",\"black\",\"orange\",\"pink\",\"yellow\"][i]\n",
    "    locz=drive_charge_gps[[\"lat\",\"lon\"]][drive_charge_gps[\"drive_block\"]==q].values.tolist()\n",
    "    zz=[ wgs_gcj(i[0], i[1])  for i  in  locz ]\n",
    "    folium.PolyLine(  # polyline方法为将坐标用实线形式连接起来\n",
    "        zz,  # 将坐标点连接起来\n",
    "        weight=4,  # 线的大小为4\n",
    "        color=qq,  # 线的颜色为红色\n",
    "        opacity=0.8,  # 线的透明度\n",
    "    ).add_to(m)  # 将这条线添加到刚才的区域m内"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "m.save(\"soc_gps/trace_mul.html\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py38",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.15"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "4d6c15edccb966d2bb52e4527dc0611a26206d084fadc4df5e610d719652857f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}