{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# GMNS to AequilibraE example\n", "\n", "## Inputs\n", "1. Nodes as a .csv flat file in GMNS format\n", "2. Links as a .csv flat file in GMNS format\n", "3. Trips as a .csv flat file, with the following columns: orig_node, dest_node, trips\n", "4. Sqlite database used by AequilibraE\n", "\n", "## Steps\n", "1. Read the GMNS nodes\n", " - Place in SQLite database, then translate to AequilibraE nodes\n", " - Generate the dictionary of zones for the omx trip table (uses node_type = centroid)\n", "\n", "2. Read the GMNS links\n", " - Place in SQLite database, then translate to AequilibraE links\n", "\n", "3. Read the trips\n", " - Translate into .omx file\n", "\n", "A separate Jupyter notebook, Route, performs the following steps\n", "\n", "4. Run AequilibraE shortest path and routing\n", "\n", "5. Generate detail and summary outputs\n" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "#!/usr/bin/env python\n", "# coding: utf-8\n", "import os\n", "import numpy as np\n", "import pandas as pd\n", "import sqlite3\n", "#import shutil # needed?\n", "import openmatrix as omx\n", "import math\n", "#run_folder = 'C:/Users/Scott.Smith/GMNS/Lima'\n", "run_folder = 'C:/Users/Scott/Documents/Work/AE/Lima' #Change to match your local environment\n", "#highest_centroid_node_number = 500 #we are now finding this from the nodes dataframe" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Read the nodes, and set up the dictionary of centroids\n", "The dictionary of centroids is used later in setting up the omx trip table" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " node_id name x_coord y_coord z_coord node_type ctrl_type zone_id \\\n", "0 1 NaN -84.106107 40.743322 NaN centroid NaN 1 \n", "1 2 NaN -84.104302 40.743319 NaN centroid NaN 2 \n", "2 3 NaN -84.102894 40.743284 NaN centroid NaN 3 \n", "3 4 NaN -84.102975 40.740703 NaN centroid NaN 4 \n", "4 5 NaN -84.104341 40.742056 NaN centroid NaN 5 \n", "\n", " parent_node_id \n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 NaN \n", "4 NaN \n", "2232\n" ] } ], "source": [ "#Read the nodes\n", "node_csvfile = os.path.join(run_folder, 'GMNS_node.csv')\n", "df_node = pd.read_csv(node_csvfile) #data already has headers\n", "print(df_node.head()) #debugging\n", "df_size = df_node.shape[0]\n", "print(df_size)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "449\n", "493\n" ] } ], "source": [ "# Set up the dictionary of centroids\n", "# Assumption: the node_type = 'centroid' for centroid nodes\n", "# The centroid nodes are the lowest numbered nodes, at the beginning of the list of nodes,\n", "# but node numbers need not be consecutive\n", "tazdictrow = {}\n", "for index in df_node.index:\n", " if df_node['node_type'][index]=='centroid':\n", " #DEBUG print(index, df_node['node_id'][index], df_node['node_type'][index])\n", " tazdictrow[df_node['node_id'][index]]=index\n", "#tazdictrow = {1:0,2:1,3:2,4:3,...,492:447,493:448}\n", "taz_list = list(tazdictrow.keys())\n", "matrix_size = len(tazdictrow) #Matches the number of nodes flagged as centroids\n", "print(matrix_size) #DEBUG\n", "highest_centroid_node_number = max(tazdictrow, key=tazdictrow.get) #for future use\n", "print(highest_centroid_node_number) #DEBUG" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Read the links" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "6095\n" ] } ], "source": [ "# Read the links\n", "link_csvfile = os.path.join(run_folder, 'GMNS_link.csv')\n", "df_link = pd.read_csv(link_csvfile) #data already has headers\n", "#print(df_node.head()) #debugging\n", "#df_size = df_link.shape[0]\n", "print(df_link.shape[0]) #debug" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Put nodes and links into SQLite. Then translate to AequilibraE 0.6.5 format\n", "1. Nodes are pushed into a table named GMNS_node\n", "2. node table used by AequilibraE is truncated, then filled with values from GMNS_node\n", "3. Centroid nodes are assumed to be the lowest numbered nodes, limited by the highest_centroid_node_number\n", " - Number of centroid nodes must equal matrix_size, the size of the trip OMX Matrix\n", "3. Links are pushed into a table named GMNS_link\n", "4. link table used by AequilibraE is truncated, then filled with values from GMNS_link\n", "\n", "### Some notes\n", "1. All the nodes whole id is <= highest_centroid_node_number are set as centroids\n", "2. GMNS capacity is in veh/hr/lane, AequilibraE is in veh/hr; hence, capacity * lanes in the insert statement\n", "3. free_flow_time (minutes) is assumed to be 60 (minutes/hr) * length (miles) / free_speed (miles/hr)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "#Open the Sqlite database, and insert the nodes and links\n", "network_db = os.path.join(run_folder,'1_project','Lima.sqlite')\n", "with sqlite3.connect(network_db) as db_con:\n", " #nodes\n", " df_node.to_sql('GMNS_node',db_con, if_exists='replace',index=False)\n", " db_cur = db_con.cursor()\n", " sql0 = \"delete from nodes;\"\n", " db_cur.execute(sql0)\n", " sql1 = (\"insert into nodes(ogc_fid, node_id, x, y, is_centroid)\" +\n", " \" SELECT node_id, node_id, x_coord,y_coord,0 from \" +\n", " \" GMNS_node\")\n", " db_cur.execute(sql1)\n", " sql2 = (\"update nodes set is_centroid = 1 where ogc_fid <= \" + str(highest_centroid_node_number))\n", " db_cur.execute(sql2)\n", " \n", "with sqlite3.connect(network_db) as db_con:\n", " df_link.to_sql('GMNS_link',db_con, if_exists='replace',index=False)\n", " db_cur = db_con.cursor()\n", " sql0 = \"delete from links;\"\n", " db_cur.execute(sql0)\n", " sql1 = (\"insert into links(ogc_fid, link_id, a_node, b_node, direction, distance, modes,\" +\n", " \" link_type, capacity_ab, speed_ab, free_flow_time) \" +\n", " \" SELECT link_id, link_id, from_node_id, to_node_id, directed, length, allowed_uses,\" +\n", " \" facility_type, capacity*lanes, free_speed, 60*length / free_speed\" +\n", " \" FROM GMNS_link where GMNS_link.capacity > 0\")\n", " db_cur.execute(sql1)\n", " sql2 = (\"update links set capacity_ba = 0, speed_ba = 0, b=0.15, power=4\")\n", " db_cur.execute(sql2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Next step is to update the links with the parameters for the volume-delay function. This step is AequilibraE-specific and makes use of the link_types Sqlite table. This table is taken from v 0.7.1 of AequilibraE, to ease future compatibility. The link_types table expects at least one row with link_type = \"default\" to use for default values. The user may add other rows with the real link_types. \n", "\n", "Its CREATE statement is as follows\n", "\n", "```\n", "CREATE TABLE 'link_types' (link_type VARCHAR UNIQUE NOT NULL PRIMARY KEY,\n", " link_type_id VARCHAR UNIQUE NOT NULL,\n", " description VARCHAR,\n", " lanes NUMERIC,\n", " lane_capacity NUMERIC,\n", " alpha NUMERIC,\n", " beta NUMERIC,\n", " gamma NUMERIC,\n", " delta NUMERIC,\n", " epsilon NUMERIC,\n", " zeta NUMERIC,\n", " iota NUMERIC,\n", " sigma NUMERIC,\n", " phi NUMERIC,\n", " tau NUMERIC)\n", "```\n", "\n", "| link_type | link_type_id | description | lanes | lane_capacity | alpha | beta | other fields not used |\n", "| ----- | ----- | ----- | ----- | ----- |----- |----- |----- |\n", "| default | 99 | Default general link type | 2 | 900 | 0.15 | 4 | |" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "with sqlite3.connect(network_db) as db_con:\n", " db_cur = db_con.cursor()\n", " sql1 = \"update links set b = (select alpha from link_types where link_type = links.link_type)\"\n", " db_cur.execute(sql1)\n", " sql2 = (\"update links set b = (select alpha from link_types where link_type = 'default') where b is NULL\")\n", " db_cur.execute(sql2)\n", " sql3 = \"update links set power = (select beta from link_types where link_type = links.link_type)\"\n", " db_cur.execute(sql3)\n", " sql4 = (\"update links set power = (select beta from link_types where link_type = 'default') where power is NULL\")\n", " db_cur.execute(sql4)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Read the trips, and translate to omx file" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " orig_taz dest_taz total\n", "0 1 57 1\n", "1 1 138 1\n", "2 2 2 1\n", "3 2 287 1\n", "4 3 67 1\n", "13000\n", "32041\n" ] } ], "source": [ "#Read a flat file trip table into pandas dataframe\n", "trip_csvfile = os.path.join(run_folder, 'demand.csv')\n", "df_trip = pd.read_csv(trip_csvfile) #data already has headers\n", "print(df_trip.head()) #debugging\n", "df_size = df_trip.shape[0]\n", "print(df_size)\n", "#print(df.iloc[50]['o_zone_id'])\n", "#stuff for debugging\n", "print(df_trip['total'].sum()) #for debugging: total number of trips\n", "#for k in range(df_size): #at most matrix_size*matrix_size\n", "# i = tazdictrow[df_trip.iloc[k]['orig_taz']]\n", "# j = tazdictrow[df_trip.iloc[k]['dest_taz']]\n", "# if k == 4: print(k,\" i=\",i,\" j=\",j) #debugging" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "#Write the dataframe to an omx file\n", "# This makes use of tazdictrow and matrix_size, that was established earlier. \n", "# The rows are also written to a file that is used only for debugging\n", "outfile = os.path.join(run_folder, '0_tntp_data' ,'demand.omx') \n", "outdebugfile = open(os.path.join(run_folder,'debug_demand.txt'),\"w\")\n", "output_demand = np.zeros((matrix_size,matrix_size))\n", "f_output = omx.open_file(outfile,'w')\n", "\n", "f_output.create_mapping('taz',taz_list)\n", "#write the data\n", "for k in range(df_size): #at most matrix_size*matrix_size\n", " i = tazdictrow[df_trip.iloc[k]['orig_taz']]\n", " j = tazdictrow[df_trip.iloc[k]['dest_taz']]\n", " \n", " output_demand[i][j] = df_trip.iloc[k]['total']\n", " print('Row: ',df_trip.iloc[k]['orig_taz'],i,\" Col: \",df_trip.iloc[k]['dest_taz'],j,\" Output\",output_demand[i][j],file=outdebugfile)\n", " \n", "f_output['matrix'] = output_demand #puts the output_demand array into the omx matrix\n", "f_output.close()\n", "outdebugfile.close()\n", "#You may stop here" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Shape: (449, 449)\n", "Number of tables 1\n", "Table names: ['matrix']\n", "attributes: []\n", "sum of trips 32041.0\n" ] } ], "source": [ "# Not needed except for debugging\n", "#Read the input omx trip table\n", "infile = os.path.join(run_folder, '0_tntp_data' ,'demand.omx') \n", "f_input = omx.open_file(infile)\n", "m1 = f_input['matrix']\n", "input_demand = np.array(m1)\n", "\n", "print('Shape:',f_input.shape())\n", "print('Number of tables',len(f_input))\n", "print('Table names:',f_input.list_matrices())\n", "print('attributes:',f_input.list_all_attributes())\n", "print('sum of trips',np.sum(m1))\n", "f_input.close()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.7" } }, "nbformat": 4, "nbformat_minor": 4 }