Conflating HERE and WSDOT layers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Establishing Multisource Data-Integration Framework for Transportation Data Analytics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ### This notebook demonstrates the map conflation process described in the following paper"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "@article{cui2020establishing,\n",
    "  title={Establishing Multisource Data-Integration Framework for Transportation Data Analytics},\n",
    "  author={Cui, Zhiyong and Henrickson, Kristian and Biancardo, Salvatore Antonio and Pu, Ziyuan and Wang, Yinhai},\n",
    "  journal={Journal of Transportation Engineering, Part A: Systems},\n",
    "  volume={146},\n",
    "  number={5},\n",
    "  pages={04020024},\n",
    "  year={2020},\n",
    "  publisher={American Society of Civil Engineers}\n",
    "}\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data used in this code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, two geographical layers are used the Washington 24K map layer and the HERE layer in 2016 quarter 2. These two layers are stored in PostgreSQL as two tables \n",
    "* **state_lines**: Washington 24K map layer used for loop detector data analytics\n",
    "* **wash_lines_2016q2**: HERE Layer for HERE data\n",
    "The two tables are backup as **.csv** files and **.sql** files. The files are in the './data' folder."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build your own database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can easily use **PgAdmin4** (version >= 4.23) to import the **.csv** files to your own PostgreSQL database, taking the **wash_lines_2016q2** table as an example:\n",
    "* 1.Create a database for yourself to run this code.\n",
    "* 2.Create a **wash_lines_2016q2** table in your database by executing the following code in the *Query Tool* of **PgAdmin4**:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "~~~sql\n",
    "-- Table: public.wash_lines_2016q2\n",
    "-- DROP TABLE public.wash_lines_2016q2;\n",
    "CREATE TABLE public.wash_lines_2016q2\n",
    "(\n",
    "    geom geometry(MultiLineString,4326),\n",
    "    link_id bigint,\n",
    "    st_name character varying(240) COLLATE pg_catalog.\"default\",\n",
    "    feat_id bigint,\n",
    "    dir_travel character varying(1) COLLATE pg_catalog.\"default\",\n",
    "    frontage character varying(1) COLLATE pg_catalog.\"default\",\n",
    "    ramp character varying(1) COLLATE pg_catalog.\"default\",\n",
    "    contracc character varying(1) COLLATE pg_catalog.\"default\",\n",
    "    route_type character varying(1) COLLATE pg_catalog.\"default\",\n",
    "    iso_code character varying(3) COLLATE pg_catalog.\"default\"\n",
    ")\n",
    "WITH (\n",
    "    OIDS = FALSE\n",
    ")\n",
    "TABLESPACE pg_default;\n",
    "\n",
    "ALTER TABLE public.wash_lines_2016q2\n",
    "    OWNER to postgres;\n",
    "\n",
    "GRANT ALL ON TABLE public.wash_lines_2016q2 TO postgres;\n",
    "\n",
    "GRANT SELECT ON TABLE public.wash_lines_2016q2 TO PUBLIC;\n",
    "-- Index: sidx_wash_lines_2016q2_geom\n",
    "\n",
    "-- DROP INDEX public.sidx_wash_lines_2016q2_geom;\n",
    "\n",
    "CREATE INDEX sidx_wash_lines_2016q2_geom\n",
    "    ON public.wash_lines_2016q2 USING gist\n",
    "    (geom)\n",
    "    TABLESPACE pg_default;\n",
    "~~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* (Create a **state_lines** table in your database by executing the following code in the *Query Tool* of **PgAdmin4**)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "-- Table: public.state_lines\n",
    "\n",
    "-- DROP TABLE public.state_lines;\n",
    "\n",
    "CREATE TABLE public.state_lines\n",
    "(\n",
    "    ogc_fid integer,\n",
    "    objectid numeric(9,0),\n",
    "    direction character varying(2) COLLATE pg_catalog.\"default\",\n",
    "    barm numeric(19,11),\n",
    "    earm numeric(19,11),\n",
    "    region character varying(2) COLLATE pg_catalog.\"default\",\n",
    "    display character varying(8) COLLATE pg_catalog.\"default\",\n",
    "    rt_typea character varying(2) COLLATE pg_catalog.\"default\",\n",
    "    rt_typeb numeric(4,0),\n",
    "    lrs_date character varying(8) COLLATE pg_catalog.\"default\",\n",
    "    routeid character varying(12) COLLATE pg_catalog.\"default\",\n",
    "    stateroute character varying(3) COLLATE pg_catalog.\"default\",\n",
    "    relroutety character varying(2) COLLATE pg_catalog.\"default\",\n",
    "    relroutequ character varying(7) COLLATE pg_catalog.\"default\",\n",
    "    oneway character varying(2) COLLATE pg_catalog.\"default\",\n",
    "    from_elev numeric(9,0),\n",
    "    to_elev numeric(9,0),\n",
    "    shape_leng numeric(19,11),\n",
    "    wkb_geometry geometry(LineString,900914),\n",
    "    source integer,\n",
    "    target integer,\n",
    "    reverse_cost double precision,\n",
    "    travel_cost double precision,\n",
    "    restriction character varying(3) COLLATE pg_catalog.\"default\",\n",
    "    express character(1) COLLATE pg_catalog.\"default\",\n",
    "    en_date date,\n",
    "    st_date date,\n",
    "    e_barm numeric(19,11),\n",
    "    e_earm numeric(19,11),\n",
    "    e_direction character(1) COLLATE pg_catalog.\"default\",\n",
    "    geom geometry(Geometry,4326)\n",
    ")\n",
    "WITH (\n",
    "    OIDS = FALSE\n",
    ")\n",
    "TABLESPACE pg_default;\n",
    "\n",
    "ALTER TABLE public.state_lines\n",
    "    OWNER to postgres;\n",
    "\n",
    "GRANT ALL ON TABLE public.state_lines TO postgres;\n",
    "\n",
    "GRANT SELECT ON TABLE public.state_lines TO PUBLIC;\n",
    "-- Index: state_lines_geom_gist\n",
    "\n",
    "-- DROP INDEX public.state_lines_geom_gist;\n",
    "\n",
    "CREATE INDEX state_lines_geom_gist\n",
    "    ON public.state_lines USING gist\n",
    "    (geom)\n",
    "    TABLESPACE pg_default;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 3.Create the **postgis** extension for your database (Just right click the **Entensions** menu in your database and **Create**) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/extension.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 4.Use the **Import** function by right clicking the **wash_lines_2016q2** table to import the **wash_lines_2016q2.csv** file. (Please remeber to turn the **header** on and choose ',' as Delimiter). Do the same thing for the **state_lines** table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/import.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Possible Issues:\n",
    "* If you encounter error: 'GetProj4StringSPI: Cannot find SRID \"(900914)\" in spatial_ref_sys' or 'GetProj4StringSPI: Cannot find SRID \"(9110)\" in spatial_ref_sys', run the following code to add SRID=900914 and SRID=9110 into **spatial_ref_sys** table:\n",
    "\n",
    "```sql\n",
    "INSERT INTO public.spatial_ref_sys(srid, srtext, proj4text)\n",
    "VALUES(900914, \n",
    "'PROJCS[\"\"NAD_1983_HARN_StatePlane_Washington_South_FIPS_4602_Feet\"\",GEOGCS[\"\"GCS_North_American_1983_HARN\"\",DATUM[\"\"NAD83_High_Accuracy_Reference_Network\"\",SPHEROID[\"\"GRS_1980\"\",6378137.0,298.257222101]],PRIMEM[\"\"Greenwich\"\",0.0],UNIT[\"\"Degree\"\",0.0174532925199433],AUTHORITY[\"\"EPSG\"\",\"\"4269\"\"]],PROJECTION[\"\"Lambert_Conformal_Conic_2SP\"\"],PARAMETER[\"\"False_Easting\"\",1640416.666666667],PARAMETER[\"\"False_Northing\"\",0.0],PARAMETER[\"\"Central_Meridian\"\",-120.5],PARAMETER[\"\"Standard_Parallel_1\"\",45.83333333333334],PARAMETER[\"\"Standard_Parallel_2\"\",47.33333333333334],PARAMETER[\"\"Latitude_Of_Origin\"\",45.33333333333334],UNIT[\"\"Foot_US\"\",0.3048006096012192]]'\n",
    ",\n",
    "'+proj=lcc +lat_1=45.83333333333334 +lat_2=47.33333333333334 +lat_0=45.33333333333334 +lon_0=-120.5 +x_0=500000.0000000001 +y_0=0 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs '\n",
    ")\n",
    "```\n",
    "\n",
    "```sql\n",
    "INSERT INTO public.spatial_ref_sys(srid, srtext, proj4text)\n",
    "VALUES(9110, \n",
    "'PROJCS[\"\"NAD_1983_HARN_StatePlane_Washington_North_FIPS_4601_Feet\"\",GEOGCS[\"\"GCS_North_American_1983_HARN\"\",DATUM[\"\"NAD83_High_Accuracy_Regional_Network\"\",SPHEROID[\"\"GRS_1980\"\",6378137.0,298.257222101]],PRIMEM[\"\"Greenwich\"\",0.0],UNIT[\"\"Degree\"\",0.0174532925199433]],PROJECTION[\"\"Lambert_Conformal_Conic_2SP\"\"],PARAMETER[\"\"False_Easting\"\",1640416.666666667],PARAMETER[\"\"False_Northing\"\",0.0],PARAMETER[\"\"Central_Meridian\"\",-120.8333333333333],PARAMETER[\"\"Standard_Parallel_1\"\",47.5],PARAMETER[\"\"Standard_Parallel_2\"\",49.73333333333333],PARAMETER[\"\"Latitude_Of_Origin\"\",47.0],UNIT[\"\"Foot_US\"\",0.3048006096012192]]'\n",
    ",\n",
    "'+proj=lcc +lat_1=47.5 +lat_2=49.73333333333333 +lat_0=47 +lon_0=-120.8333333333333 +x_0=500000.0000000001 +y_0=0 +ellps=GRS80 +to_meter=0.3048006096012192 +no_defs'\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Code for Demonstrating the Map Conflation Algorithm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "IP = 'your IP address (default: localhost)'\n",
    "PORT = 'your port (default: 5432)'\n",
    "DBNAME = 'your datbase name'\n",
    "USER = 'your username'\n",
    "PASSWORD = 'your password'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query for all state_lines segment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = psycopg2.connect(host=IP, port=PORT, dbname=DBNAME, user=USER, password=PASSWORD)\n",
    "cur = con.cursor()\n",
    "\n",
    "ids = []\n",
    "cur.execute(\"SELECT DISTINCT objectid FROM state_lines WHERE express is null and relroutequ is null ORDER BY objectid\")\n",
    "for rw in cur.fetchall():\n",
    "    ids.append(rw[0])\n",
    "    \n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Create conflation table\n"
     ]
    }
   ],
   "source": [
    "con = psycopg2.connect(host=IP, port=PORT, dbname=DBNAME, user=USER, password=PASSWORD)\n",
    "cur = con.cursor()\n",
    "\n",
    "conflation_table_name = 'conflation'\n",
    "create_conflation_table_query = '''\n",
    "CREATE TABLE IF NOT EXISTS public.{}\n",
    "(\n",
    "    point_id integer NOT NULL,\n",
    "    here_id integer NOT NULL,\n",
    "    geom geometry(Geometry,4326),\n",
    "    angle_score double precision,\n",
    "    distance_score double precision\n",
    ")\n",
    "'''.format(conflation_table_name)\n",
    "cur.execute(create_conflation_table_query)\n",
    "con.commit()\n",
    "con.close()\n",
    "print('Create conflation table')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12251"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conflating function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def ConflateSegments(ids, direction_subquery, distance, angle = 0.25, conflation_table = 'conflation', RECONFLATE=False):\n",
    "    con = psycopg2.connect(host=IP, port=PORT, dbname=DBNAME, user=USER, password=PASSWORD)\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    if RECONFLATE:\n",
    "        for i in ids:\n",
    "            sql = '''DELETE FROM {} WHERE point_id = '''.format(conflation_table) + str(i)\n",
    "            print (sql)\n",
    "            cur.execute(sql)\n",
    "            print(cur.rowcount)\n",
    "            con.commit()\n",
    "    \n",
    "    c = 0\n",
    "    for i in ids:\n",
    "        sql = '''\n",
    "    WITH base_points AS (\n",
    "                        SELECT \n",
    "                            objectid AS base_id,\n",
    "                            ST_DumpPoints( ST_Segmentize( ST_Transform(wkb_geometry,9110), 30 ) ) AS dp,\n",
    "                            geom AS base_geom,\n",
    "                            rt_typea AS base_type,\n",
    "                            direction AS base_dir\n",
    "                        FROM state_lines\n",
    "                        WHERE objectid = {}\n",
    "        )\n",
    "        , base_pairs AS (\n",
    "                        SELECT \n",
    "                            base_id,\n",
    "                            ST_Transform((dp).geom,4326 ) AS pt_geom,\n",
    "                            LEAD(ST_Transform((dp).geom,4326 )) OVER (PARTITION BY base_id ORDER BY (dp).path[1]) As lead_geom,\n",
    "                            ST_Centroid(ST_MakeLine(ST_Transform((dp).geom,4326 ), LEAD(ST_Transform((dp).geom,4326 )) \n",
    "                                OVER (PARTITION BY base_id ORDER BY (dp).path[1]))) AS base_line_centroid,\n",
    "                            base_type,\n",
    "                            base_dir,\n",
    "                            (dp).path[1] AS base_path\n",
    "                        FROM base_points\n",
    "        )\n",
    "        , here_near_points AS (\n",
    "                        SELECT \n",
    "                            here.link_id AS here_id,\n",
    "                            here.geom AS here_geom,\n",
    "                            ST_DumpPoints( ST_Segmentize( ST_Transform(here.geom, 9110), 30 ) ) AS here_dp,\n",
    "                            here.ramp AS here_type,\n",
    "                            here.dir_travel AS here_dir\n",
    "                        FROM state_lines AS sl JOIN wash_lines_2016q2 here\n",
    "                            -- ON ST_Distance(sl.geom, here.geom) < 0.0001\n",
    "                            ON ST_DWithin(ST_Transform(sl.wkb_geometry, 4326), here.geom, {})\n",
    "                        WHERE sl.objectid = {}\n",
    "                        AND here.ramp = 'N'\n",
    "                        {}\n",
    "        )\n",
    "        , here_pairs AS (\n",
    "                        SELECT \n",
    "                            here_id,\n",
    "                            ST_Transform((here_dp).geom,4326 ) AS here_pt_geom,\n",
    "                            LEAD(ST_Transform((here_dp).geom,4326 )) OVER (PARTITION BY here_id ORDER BY (here_dp).path[2]) As here_lead_geom,\n",
    "                            ST_Centroid(ST_MakeLine(ST_Transform((here_dp).geom,4326 ), LEAD(ST_Transform((here_dp).geom,4326 )) \n",
    "                                OVER (PARTITION BY here_id ORDER BY (here_dp).path[2]))) AS here_line_centroid,\n",
    "                            here_type,\n",
    "                            here_dir,\n",
    "                            (here_dp).path[2] AS here_path\n",
    "                        FROM here_near_points\n",
    "        )\n",
    "        , points AS (\n",
    "                        SELECT \t\n",
    "                            base_id,\n",
    "                            ST_MakeLine(pt_geom, lead_geom) AS base_line,\n",
    "                            ST_Azimuth(pt_geom, lead_geom ) AS base_az,\n",
    "                            ST_Distance(pt_geom, lead_geom) AS base_length,\n",
    "                            base_line_centroid,\n",
    "                            base_type,\n",
    "                            base_dir,\n",
    "                            base_path,\n",
    "                            here_id,\n",
    "                            ST_MakeLine(here_pt_geom, here_lead_geom) AS here_line,\n",
    "                            --ST_Azimuth(here_pt_geom, here_lead_geom ) AS here_az,\n",
    "                            CASE        \t\t\t\n",
    "                                WHEN here_dir = 'T' THEN \n",
    "                                    ST_Azimuth(here_lead_geom, here_pt_geom) \n",
    "                                ELSE\n",
    "                                    ST_Azimuth(here_pt_geom, here_lead_geom) \n",
    "                            END  AS here_az,\n",
    "                            ST_Distance(here_pt_geom, here_lead_geom) AS here_length,\n",
    "                            here_type,\n",
    "                            here_dir,\n",
    "                            here_path,\n",
    "                            ST_Distance(base_line_centroid, here_line_centroid ) AS distance\n",
    "                    FROM base_pairs JOIN here_pairs ON \n",
    "                        --ST_Distance(wkt_geom,(here_geom).geom ) < 0.001)\n",
    "                        ST_DWithin(base_line_centroid, here_line_centroid, {})\n",
    "                    ORDER BY base_id, base_path, distance, here_id, here_path\n",
    "        )\n",
    "        , points_here AS (\n",
    "                    SELECT  base_id,\n",
    "                        base_path,\n",
    "                        base_line,\n",
    "                        base_az,\n",
    "                        base_dir,\n",
    "                        here_id,\n",
    "                        here_path,\n",
    "                        here_az,\n",
    "                        here_dir,\n",
    "                        distance,\n",
    "                        ACOS(COS( base_az ) * COS(here_az ) + SIN( base_az ) * SIN(here_az ))  AS angle\n",
    "                    FROM points\n",
    "                    WHERE ABS( SIN( base_az - here_az ) ) < {}\n",
    "        )\n",
    "        ,final AS (\n",
    "                    SELECT \n",
    "                        base_id,\n",
    "                        base_path,\n",
    "                        base_line,\n",
    "                        base_az,\n",
    "                        base_dir,\n",
    "                        here_id,\n",
    "                        here_path,\n",
    "                        here_az,\n",
    "                        here_dir,\n",
    "                        distance,\n",
    "                        angle,\n",
    "                        ROW_NUMBER() OVER ( PARTITION BY base_id, base_path ORDER BY distance ) AS rw\n",
    "                    FROM points_here \n",
    "        )\n",
    "        INSERT INTO {}\n",
    "        SELECT  \n",
    "                base_id,\n",
    "                here_id,\n",
    "                ST_MakeLine( base_line ORDER BY base_path ) AS geom,\n",
    "                MAX(angle) AS angle,\n",
    "                MAX(distance) AS distance\n",
    "        FROM final \n",
    "        WHERE rw = 1\n",
    "        GROUP BY \n",
    "                base_id,\n",
    "                here_id \n",
    "        ON CONFLICT DO NOTHING;\n",
    "        '''.format(i, distance, i, direction_subquery, distance, angle, conflation_table)\n",
    "        cur.execute(sql)\n",
    "        print( '{} rows inserted on id {}'.format(cur.rowcount,i))\n",
    "        con.commit()\n",
    "        c += 1\n",
    "    con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conflate all Segments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ConflateSegments(ids, direction_subquery='', distance=0.0001, angle=0.25, conflation_table = 'conflation', RECONFLATE=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## For specific segments, if the conflation results are not good enough, we can reconflate by manually adjusting parameters as follows or using iteratively adjusting as the paper mentioned."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SR-520"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DELETE FROM conflation WHERE point_id = 17815\n",
      "3\n",
      "3 rows inserted on id 17815\n"
     ]
    }
   ],
   "source": [
    "ids = [17815]\n",
    "direction_subquery = ''' AND dir_travel = 'F' '''\n",
    "distance = 0.001\n",
    "\n",
    "ConflateSegments(ids, direction_subquery, distance, conflation_table = 'conflation', RECONFLATE=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# I-90"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DELETE FROM conflation WHERE point_id = 2439\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 15111\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 15112\n",
      "0\n",
      "8 rows inserted on id 2439\n",
      "5 rows inserted on id 15111\n",
      "2 rows inserted on id 15112\n"
     ]
    }
   ],
   "source": [
    "ids=[2439, 15111, 15112]\n",
    "direction_subquery = ''' AND here.dir_travel != 'B' '''\n",
    "# dir_travel = 'F'\n",
    "distance = 0.001\n",
    "\n",
    "ConflateSegments(ids, direction_subquery, distance, conflation_table = 'conflation', RECONFLATE=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# I405"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DELETE FROM conflation WHERE point_id = 2157\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 2156\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 15327\n",
      "0\n",
      "5 rows inserted on id 2157\n",
      "8 rows inserted on id 2156\n",
      "5 rows inserted on id 15327\n"
     ]
    }
   ],
   "source": [
    "ids=[2157, 2156, 15327]\n",
    "direction_query = ''' AND here.dir_travel != 'B' '''\n",
    "# dir_travel = 'F'\n",
    "distance = 0.001\n",
    "\n",
    "ConflateSegments(ids, direction_subquery, distance, conflation_table = 'conflation', RECONFLATE=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# I-5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DELETE FROM conflation WHERE point_id = 13524\n",
      "0\n",
      "5 rows inserted on id 13524\n"
     ]
    }
   ],
   "source": [
    "ids=[13524]\n",
    "direction_query = '''  '''\n",
    "distance = 0.001\n",
    "\n",
    "ConflateSegments(ids, direction_subquery, distance, conflation_table = 'conflation', RECONFLATE=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## North Bound"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DELETE FROM conflation WHERE point_id = 13524\n",
      "5\n",
      "DELETE FROM conflation WHERE point_id = 3126\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 15483\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 3127\n",
      "0\n",
      "5 rows inserted on id 13524\n",
      "6 rows inserted on id 3126\n",
      "6 rows inserted on id 15483\n",
      "5 rows inserted on id 3127\n"
     ]
    }
   ],
   "source": [
    "ids=[13524, 3126, 15483, 3127]\n",
    "direction_query = ''' AND here.dir_travel = 'F' '''\n",
    "distance = 0.001\n",
    "angle = 0.35\n",
    "\n",
    "ConflateSegments(ids, direction_subquery, distance, angle, conflation_table = 'conflation', RECONFLATE=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## South Bound"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DELETE FROM conflation WHERE point_id = 9307\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 15484\n",
      "0\n",
      "DELETE FROM conflation WHERE point_id = 9274\n",
      "0\n",
      "6 rows inserted on id 9307\n",
      "5 rows inserted on id 15484\n",
      "5 rows inserted on id 9274\n"
     ]
    }
   ],
   "source": [
    "ids=[9307, 15484, 9274]\n",
    "direction_query = ''' AND here.dir_travel = 'T' '''\n",
    "distance = 0.001\n",
    "angle = 0.35\n",
    "\n",
    "ConflateSegments(ids, direction_subquery, distance, angle, conflation_table = 'conflation', RECONFLATE=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ---------------------------- Finished ----------------------------------"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}