2B.RequestReferencePost.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "RAMS query program\n",
    "\n",
    "Created on Wed Jul 5 11:49:17 2017\n",
    "\n",
    "@author: Tingting Huang\n",
    "\n",
    "thuang1@iastate.edu\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use your own directory and filename.\n",
    "\n",
    "Must have column names: routeId, MM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dirpath = 'C:/Users/thuang1/Desktop/REST API/'\n",
    "filename = 'MM_Found_XD_Coded_Direction_Checked.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Install arcgis package by shell command: \n",
    "pip install arcgis-rest-query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import requests\n",
    "import os\n",
    "from arcgis import ArcGIS\n",
    "\n",
    "# connect to service\n",
    "username = os.getenv('ARCGIS_USERNAME', None)\n",
    "password = os.getenv('ARCGIS_PASSWORD', None)\n",
    "service = ArcGIS(\"https://gis.iowadot.gov/ramsa/rest/services/lrs/MapServer/98/query\",\n",
    "username=username,\n",
    "password=password)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "a. Load functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def valid_post(r, x0):\n",
    "\n",
    "    \n",
    "    \n",
    "    res=[]\n",
    "    if len(r['features'])==0:\n",
    "        pass\n",
    "    else:\n",
    "        for i in xrange(len(r['features'])):\n",
    "            ss=str(r['features'][i]['attributes']['REFERENCE_POST_VALUE'])\n",
    "            if len([s for s in ss if s.isdigit()])==len(ss):\n",
    "                res.append([float(r['features'][i]['attributes']['MEASURE']),\\\n",
    "                            float(r['features'][i]['attributes']['REFERENCE_POST_VALUE'])])\n",
    "    \n",
    "    temp = pd.DataFrame(res, columns=['MM','Post'])\n",
    "    temp = temp.sort_values(by='Post').reset_index(drop=True)\n",
    "   \n",
    "    return temp\n",
    "\n",
    "# updated finding schema, use two closest as x1, x2\n",
    "def get_near_two(temp, x0):\n",
    "    if min(temp.MM)>x0: \n",
    "        one=temp['MM'].idxmin()\n",
    "        mini = temp['MM'].min()\n",
    "        two=temp.loc[temp.MM>mini,'MM'].idxmin()\n",
    "        \n",
    "    elif max(temp.MM)<x0:\n",
    "        one=temp['MM'].idxmax()\n",
    "        maxi = temp['MM'].max()\n",
    "        two=temp.loc[temp.MM<maxi,'MM'].idxmax()\n",
    "        \n",
    "    else: \n",
    "        one=temp.loc[temp.MM<x0,'MM'].idxmax()\n",
    "        two=temp.loc[temp.MM>x0,'MM'].idxmin()\n",
    "        \n",
    "    x1, y1, x2, y2 = temp.loc[one,'MM'],  temp.loc[one,'Post'], temp.loc[two,'MM'], temp.loc[two,'Post']\n",
    "    \n",
    "    return x1, y1, x2, y2\n",
    "\n",
    "\n",
    "def get_mile_post(df):\n",
    "    \n",
    "    routeId = df['routeId']\n",
    "    a = df['a']\n",
    "    b = df['b']\n",
    "    x0 = df['MM']\n",
    "    index = df.name\n",
    "    \n",
    "    try:\n",
    "\n",
    "        r = service.get_json(98, where= \"EFFECTIVE_END_DATE is NULL AND ROUTE_ID = '%s' AND measure >= '%s' AND measure< '%s'\" % (routeId, a, b),\\\n",
    "                                fields=fields)\n",
    "        temp = valid_post(r, x0)\n",
    "\n",
    "        if len(temp)>=2:\n",
    "            x1, y1, x2, y2 = get_near_two(temp, x0)\n",
    "            \n",
    "            b1=(y2-y1)/(x2-x1)\n",
    "            b0=y2-b1*x2\n",
    "            y0=b0+b1*x0\n",
    "\n",
    "        else:\n",
    "            r = service.get_json(98, where= \"EFFECTIVE_END_DATE is NULL AND ROUTE_ID = '%s' AND measure >= '%s' AND measure< '%s'\" % (routeId, a-20, b+20),\\\n",
    "                                fields=fields) \n",
    "            temp = valid_post(r, x0)\n",
    "\n",
    "            if (len(temp)==0) or (len(temp)==1):\n",
    "                y0=None\n",
    "            else:\n",
    "                x1, y1, x2, y2 = get_near_two(temp, x0)\n",
    "                \n",
    "                b1=(y2-y1)/(x2-x1)\n",
    "                b0=y2-b1*x2\n",
    "                y0=b0+b1*x0\n",
    "\n",
    "        return y0\n",
    "\n",
    "    except Exception, e:\n",
    "        print \"Error! At row \"+str(index)+\": \"+str(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "b. Import your data and create boundary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(dirpath+filename)\n",
    "\n",
    "df['a']=np.floor(df.MM)-3\n",
    "df['b']=np.ceil(df.MM)+3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "c. Specify query fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "fields=['REFERENCE_POST_VALUE','MEASURE']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "d. Main function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "now1=datetime.now()\n",
    "\n",
    "\n",
    "df['mile_post']=df.apply(lambda x: get_mile_post(x), axis=1)\n",
    "\n",
    "\n",
    "    \n",
    "now2=datetime.now()\n",
    "\n",
    "print('Runtime: '+str(now2-now1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "e. Export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "df=df.drop(['a','b'],axis=1)\n",
    "df.to_csv(dirpath+'Reference_Post_'+filename, header=True, index=False)\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}