{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "888d089c-a9c8-4d2d-af74-dff1a8ccfefd",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import time\n",
    "from typing import List\n",
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "class GetElevation:\n",
    "\n",
    "    @classmethod\n",
    "    def __SendQuery(cls, latLngString: str) -> json:\n",
    "        query = ('https://api.opentopodata.org/v1/mapzen?locations={}&interpolation=bilinear'.format(latLngString))\n",
    "        res = requests.get(query).json()\n",
    "        if res[\"status\"] != \"OK\":\n",
    "            raise Exception(res[\"error\"])\n",
    "        return res\n",
    "\n",
    "    def GetSingleElevation(self, latitude: float, longitude: float) -> float:\n",
    "        \"\"\"\n",
    "        获取单个高程，输入经纬度格式为数值类型，返回值为高程float类型\n",
    "        :param latitude: 纬度\n",
    "        :param longitude: 经度\n",
    "        :return: 高程\n",
    "        \"\"\"\n",
    "        if latitude < -90 or latitude > 90:\n",
    "            raise Exception(\"纬度的范围应在-90-90之间！请检查数据源！\")\n",
    "        latLngString = str(latitude) + \",\" + str(longitude)\n",
    "        res = self.__SendQuery(latLngString)\n",
    "        elevation = res[\"results\"][0][\"elevation\"]\n",
    "        return elevation\n",
    "\n",
    "    def GetMultiElevation(self, latitude: List[float], longitude: List[float]) -> List[float]:\n",
    "        \"\"\"\n",
    "        获取数组类型的高程，输入经纬度格式为经度数组和纬度数组，返回值为高程数组\n",
    "        :param latitude:纬度数组\n",
    "        :param longitude:经度数组\n",
    "        :return:高程数组\n",
    "        \"\"\"\n",
    "        if len(latitude) != len(longitude):\n",
    "            raise Exception(\"纬度数组和经度数组长度不一致！请检查数据源！\")\n",
    "        for lat in latitude:\n",
    "            if lat < -90 or lat > 90:\n",
    "                raise Exception(\"纬度的范围应在-90-90之间！请检查数据源！\")\n",
    "        elevationList = []\n",
    "        hundredNums = len(latitude) // 100\n",
    "        # 查询整百的高程\n",
    "        for i in range(hundredNums):\n",
    "            latLngString = \"\"\n",
    "            for idx in range(100 * i, 100 * (i + 1)):\n",
    "                latLngString += (str(latitude[idx]) + \",\" + str(longitude[idx]) + \"|\")\n",
    "            res = self.__SendQuery(latLngString)\n",
    "            for idx in range(100):\n",
    "                elevationList.append(res[\"results\"][idx][\"elevation\"])\n",
    "            time.sleep(1)\n",
    "        # 查询剩余的不到100的高程\n",
    "        latLngString = \"\"\n",
    "        for i in range(hundredNums * 100, len(latitude)):\n",
    "            latLngString += (str(latitude[i]) + \",\" + str(longitude[i]) + \"|\")\n",
    "        res = self.__SendQuery(latLngString)\n",
    "        for i in range(len(latitude) - hundredNums * 100):\n",
    "            elevationList.append(res[\"results\"][i][\"elevation\"])\n",
    "        return elevationList\n",
    "\n",
    "    def ExportToXlsx(self, latLongDf: pd.DataFrame, elevationList: List[float], outputPath: str) -> None:\n",
    "        \"\"\"\n",
    "    如果用户可以传入一个DataFrame数据，可以将返回得到的高程拼接并输出\n",
    "    :param latLongDf: DataFrame数据\n",
    "    :param elevationList: 高程数组\n",
    "    :param outputPath: 输出路径\n",
    "    :return: 无返回值\n",
    "    \"\"\"\n",
    "        latLongDf[\"elevation\"] = elevationList\n",
    "        latLongDf.to_excel(outputPath, index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2a226b08-0c92-483e-b590-29a39dce6298",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "                  plant   longitude   latitude\n0  万方发电厂（焦作爱依斯万方电力有限公司)  113.381649  35.255622\n1            三河发电有限责任公司  116.860260  39.953617\n2          上海上电漕泾发电有限公司  121.407593  30.765242\n3          上海吴泾发电有限责任公司  121.471140  31.065113\n4        上海吴泾第二发电有限责任公司  121.471340  31.062532",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>plant</th>\n      <th>longitude</th>\n      <th>latitude</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>万方发电厂（焦作爱依斯万方电力有限公司)</td>\n      <td>113.381649</td>\n      <td>35.255622</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>三河发电有限责任公司</td>\n      <td>116.860260</td>\n      <td>39.953617</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>上海上电漕泾发电有限公司</td>\n      <td>121.407593</td>\n      <td>30.765242</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>上海吴泾发电有限责任公司</td>\n      <td>121.471140</td>\n      <td>31.065113</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>上海吴泾第二发电有限责任公司</td>\n      <td>121.471340</td>\n      <td>31.062532</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_excel('./lat_lon.xlsx')\n",
    "data.columns = ['plant', 'longitude', 'latitude']\n",
    "data = data.groupby('plant').mean().reset_index()\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [],
   "source": [
    "ele = GetElevation()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c1671bcd-bd33-40dd-82b5-a487801045c0",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on method GetMultiElevation in module __main__:\n",
      "\n",
      "GetMultiElevation(latitude: List[float], longitude: List[float]) -> List[float] method of __main__.GetElevation instance\n",
      "    获取数组类型的高程，输入经纬度格式为经度数组和纬度数组，返回值为高程数组\n",
      "    :param latitude:纬度数组\n",
      "    :param longitude:经度数组\n",
      "    :return:高程数组\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(ele.GetMultiElevation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "91afb581-1994-47c4-85ca-d3ea3e13e95d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[88.0, 27.0, 4.0, 3.0, 4.0, 2.0, 8.0, -2.0, 5.0, 7.0, 3.0, 58.0, 55.0, 775.0, 594.0, -3.0, 1273.0, 145.0, 145.0, 548.0, 21.0, 28.0, 48.0, 1117.0, 14.0, 1172.0, 387.0, 124.0, 1391.0, 1708.0, 1278.0, 440.0, 135.0, 494.0, 1143.0, 1869.0, 1656.0, 1873.0, 124.0, 361.0, 30.0, 971.0, -3.0, 80.0, 475.0, 1622.0, 292.0, 1317.0, 1385.0, 247.0, 1312.0, 1106.0, 1225.0, 1006.0, 1237.0, 1151.0, 555.0, 1024.0, 694.0, 1226.0, 1050.0, 1162.0, 285.0, 1007.0, 1207.0, 1023.0, 178.0, 861.0, 1171.0, 1069.0, 1015.0, 90.0, 1.0, 83.0, 1037.0, 1037.0, 1114.0, 1069.0, 1107.0, 1076.0, 1054.0, 1165.0, 1255.0, 14.0, 52.0, 171.0, 351.0, 49.0, -5.0, 7.0, -2.0, 152.0, 2.0, -2.0, 5.0, 51.0, 51.0, 606.0, 270.0, 1031.0, 1017.0, 595.0, 65.0, 279.0, 1089.0, 218.0, 57.0, 777.0, 9.0, 84.0, 520.0, 622.0, 574.0, 724.0, 582.0, 1010.0, 505.0, 1010.0, 1.0, 1.0, 59.0, 72.0, 10.0, 65.0, 64.0, 75.0, 57.0, 80.0, 118.0, 151.0, 250.0, 0.0, 1459.0, 1036.0, 1884.0, 286.0, 27.0, 2.0, 13.0, 6.0, 65.0, 3.0, 1331.0, 675.0, 240.0, 2007.0, 1590.0, 1532.0, 1545.0, 36.0, 6.0, 7.0, 199.0, 38.0, 76.0, 0.0, 73.0, 4.0, 1.0, 22.0, -2.0, 40.0, 2.0, 148.0, -1.0, 7.0, 22.0, 1149.0, 154.0, 27.0, 24.0, 44.0, 1130.0, 1277.0, 34.0, 154.0, 718.0, 1504.0, 1034.0, 19.0, 5.0, 172.0, 160.0, 27.0, 26.0, 26.0, 206.0, 19.0, 507.0, 75.0, 177.0, 102.0, 6.0, 30.0, 93.0, 218.0, -34.0, 67.0, 131.0, 1544.0, 708.0, 199.0, 119.0, 99.0, 14.0, 6.0, 11.0, 5.0, 9.0, 8.0, 130.0, 133.0, 180.0, 26.0, 1048.0, 73.0, 326.0, 192.0, 175.0, 185.0, 151.0, 151.0, 18.0, 308.0, 404.0, 360.0, 296.0, 208.0, 45.0, 46.0, 136.0, 573.0, 144.0, 147.0, -4.0, 86.0, 68.0, 167.0, 1479.0, 123.0, 35.0, 55.0, 1.0, 112.0, 8.0, 16.0, 20.0, 6.0, 19.0, 47.0, 528.0, 17.0, 627.0, -3.0, -1.0, 1459.0, 81.0, 91.0, 1122.0, 7.0, 1087.0, 1174.0, 81.0, 1349.0, 788.0, 451.0, 361.0, 65.0, 147.0, 25.0, 545.0, 1466.0, 264.0, 223.0, 56.0, 1327.0, 45.0, 45.0, 1052.0, 5.0, 1052.0, 180.0, 35.0, 1087.0, 1.0, 32.0, 1405.0, 699.0, 699.0, 1266.0, 35.0, 18.0, 116.0, 221.0, 186.0, 184.0, 7.0, 132.0, 1165.0, 1118.0, 1252.0, 29.0, 0.0, 7.0, 1184.0, 93.0, 91.0, 32.0, 849.0, 58.0, 7.0, 1.0, -6.0, -5.0, 39.0, 16.0, 12.0, 13.0, 9.0, 0.0, 132.0, 52.0, 7.0, 25.0, 32.0, 83.0, 1787.0, 0.0, 69.0, 24.0, 1.0, 3.0, -2.0, 1096.0, 207.0, 375.0, -8.0, 33.0, 94.0, 45.0, 83.0, 253.0, 24.0, 119.0, 118.0, 563.0, 468.0, 233.0, 26.0, 687.0, 1067.0, 46.0, 35.0, 30.0, 83.0, 562.0, 718.0, 426.0, 212.0, 853.0, 452.0, 294.0, 148.0, 123.0, 117.0, 25.0, 30.0, 17.0, 85.0, 7.0, 49.0, 89.0, 972.0, 56.0, 352.0, 185.0, 1113.0, 261.0, 1877.0, 222.0, 692.0, 1112.0, 395.0, 412.0, 395.0, 394.0, 8.0, 183.0, 6.0, 34.0, 19.0, 454.0, 1.0, 6.0, 8.0, 38.0, 4.0, 3.0, 6.0, 4.0, 4.0, 786.0, 1247.0, 1157.0, 1318.0, 1339.0, 1151.0, 1329.0, 46.0, 30.0, 9.0, 30.0, 9.0, 16.0, 1987.0, 151.0, 35.0, 8.0, 1353.0, 480.0, 539.0, 1010.0, 819.0, 1061.0, 371.0, 803.0, 717.0, 916.0, 918.0, 696.0, 998.0, 878.0, 28.0, 95.0, 88.0, 16.0, 93.0, 0.0, 177.0, 1.0, 9.0, 192.0, 5.0, -2.0, 5.0, -1.0, -1.0, 10.0, 2.0, 5.0, 79.0, 98.0, 338.0, 41.0, 46.0, 3.0, 35.0, 42.0, 2.0, 78.0, 54.0, 798.0, 63.0, 1288.0, 1066.0, -53.0, 705.0, 757.0, 903.0, 547.0, 1267.0, 1267.0, 355.0, 401.0, 916.0, 233.0, 6.0, 1151.0, 937.0, 940.0, 23.0, 1.0, 9.0, 9.0, 11.0, 5.0, 4.0, 7.0, -3.0, 5.0, 22.0, 4.0, 27.0, 23.0, 29.0, 10.0, 51.0, 3.0, 8.0, 63.0, 122.0, 67.0, 32.0, 28.0, 714.0, 4.0, 7.0, 604.0, 115.0, 31.0, 938.0, 21.0, 121.0, 314.0, 55.0, 70.0, 186.0, 61.0, 76.0, 154.0, 117.0, 395.0, 255.0, 40.0, 37.0, 5.0, -7.0, 3.0, 45.0, 2.0, 4.0, 6.0, 8.0, 73.0, -3.0, 4.0, 4.0, 11.0, 637.0, 30.0, 28.0, 31.0, 21.0, 23.0, 8.0, 34.0, 32.0, 1007.0, 954.0, 31.0, 31.0, 39.0, 10.0, 137.0, 31.0, 56.0, 110.0, 1.0, 592.0, 401.0, 48.0, 140.0, 171.0, 1414.0, 1455.0, 1707.0, 1707.0, 564.0, 992.0, 342.0, 35.0, 61.0, 9.0, 57.0, 41.0, 1442.0, 699.0, 1064.0, 191.0, 251.0, -11.0, 181.0, 215.0, 302.0, 1.0, 2.0, 1376.0, 2.0, 9.0, 798.0, 335.0, 21.0, 11.0, 1049.0, 1002.0, 21.0, 33.0, 75.0, 152.0, 38.0, 1266.0, 360.0, 1029.0, 1477.0, 901.0, 899.0, 1284.0, 862.0, 1205.0, 905.0, 489.0, 1205.0, 160.0, 98.0, 74.0, -2.0, 44.0, 103.0, 121.0, 443.0, 57.0, 93.0, 179.0, 175.0, 178.0, 828.0, 75.0, 115.0, 1487.0, 393.0, 165.0, 347.0, 197.0, 264.0, 27.0, 905.0, 3.0, 2.0, 33.0, 54.0, 26.0, 196.0, 142.0, 30.0, 690.0, 81.0, 955.0, 49.0, 1210.0, 500.0, 654.0, 1180.0, 1155.0, 1057.0, 407.0, 1225.0, 1169.0, 45.0, 2266.0, 2674.0, 1201.0, 1495.0, 394.0, 9.0, 182.0, 145.0, 53.0, 147.0, 1265.0]\n"
     ]
    }
   ],
   "source": [
    "multiEle = ele.GetMultiElevation(data[\"latitude\"], data[\"longitude\"])\n",
    "print(multiEle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "555559ea-7e35-4062-a21e-e5275b8da9cd",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "669"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(multiEle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "74fced56-47e8-43cc-b412-af40fcb3eedd",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "data['altitude'] = multiEle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "61704866-2b97-4bd0-ac0c-f2708ef52094",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "                    plant   longitude   latitude  altitude\n0    万方发电厂（焦作爱依斯万方电力有限公司)  113.381649  35.255622      88.0\n1              三河发电有限责任公司  116.860260  39.953617      27.0\n2            上海上电漕泾发电有限公司  121.407593  30.765242       4.0\n3            上海吴泾发电有限责任公司  121.471140  31.065113       3.0\n4          上海吴泾第二发电有限责任公司  121.471340  31.062532       4.0\n..                    ...         ...        ...       ...\n664          鹤壁丰鹤发电有限责任公司  114.192184  35.850766     182.0\n665          鹤壁同力发电有限责任公司  114.191246  35.860822     145.0\n666         黄冈大别山发电有限责任公司  114.915181  31.144568      53.0\n667       黑龙江华电齐齐哈尔热电有限公司  124.063322  47.387983     147.0\n668            黔桂发电有限责任公司  106.630029  26.607537    1265.0\n\n[669 rows x 4 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>plant</th>\n      <th>longitude</th>\n      <th>latitude</th>\n      <th>altitude</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>万方发电厂（焦作爱依斯万方电力有限公司)</td>\n      <td>113.381649</td>\n      <td>35.255622</td>\n      <td>88.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>三河发电有限责任公司</td>\n      <td>116.860260</td>\n      <td>39.953617</td>\n      <td>27.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>上海上电漕泾发电有限公司</td>\n      <td>121.407593</td>\n      <td>30.765242</td>\n      <td>4.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>上海吴泾发电有限责任公司</td>\n      <td>121.471140</td>\n      <td>31.065113</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>上海吴泾第二发电有限责任公司</td>\n      <td>121.471340</td>\n      <td>31.062532</td>\n      <td>4.0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>664</th>\n      <td>鹤壁丰鹤发电有限责任公司</td>\n      <td>114.192184</td>\n      <td>35.850766</td>\n      <td>182.0</td>\n    </tr>\n    <tr>\n      <th>665</th>\n      <td>鹤壁同力发电有限责任公司</td>\n      <td>114.191246</td>\n      <td>35.860822</td>\n      <td>145.0</td>\n    </tr>\n    <tr>\n      <th>666</th>\n      <td>黄冈大别山发电有限责任公司</td>\n      <td>114.915181</td>\n      <td>31.144568</td>\n      <td>53.0</td>\n    </tr>\n    <tr>\n      <th>667</th>\n      <td>黑龙江华电齐齐哈尔热电有限公司</td>\n      <td>124.063322</td>\n      <td>47.387983</td>\n      <td>147.0</td>\n    </tr>\n    <tr>\n      <th>668</th>\n      <td>黔桂发电有限责任公司</td>\n      <td>106.630029</td>\n      <td>26.607537</td>\n      <td>1265.0</td>\n    </tr>\n  </tbody>\n</table>\n<p>669 rows × 4 columns</p>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [],
   "source": [
    "data.to_csv('./电厂机组地理信息.csv', encoding='utf-8-sig', index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}