Multiple Routing in Python

Python API Installation

The native Kinetica Python API is accessible through the following means:

• PyPI
• Git

Data File

The example script references the nyc_neighborhood.csv data file, mentioned in the Prerequisites, in the current local directory, by default. This directory can specified as a parameter when running the example script.

Constants

Several constants are defined at the beginning of the script:

• SCHEMA -- the name of the schema in which the tables supporting the graph creation and match operations will be created

  Important

  The schema is created during the table setup portion of the script because the schema must exist prior to creating the tables that will later support the graph creation and match operations.

• TABLE_NYC_N -- the name of the table into which the NYC Neighborhood dataset is loaded. This dataset is joined to the TABLE_TAXI table to create the JOIN_TAXI dataset.

• TABLE_TAXI -- the name of the table into which the NYC Taxi dataset is loaded. This dataset is joined to the TABLE_NYC_N table to create the JOIN_TAXI dataset.

• TABLE_TAXI_EW -- the name of the projection derived from the JOIN_TAXI dataset that serves as the base dataset for the GRAPH_T graph.

• JOIN_TAXI -- the name of the join view that represents the dataset of all the trips found in the TABLE_TAXI dataset that overlap with the neighborhood boundaries found in the TABLE_NYC_N dataset

• GRAPH_T -- the NYC taxi graph

• TABLE_GRAPH_T_MRSOLVED -- the solved NYC taxi graph using the MULTIPLE_ROUTING solver type

1
2
3
4
5
6
7
8
9

SCHEMA = "graph_s_nyctaxi_multi_route"
TABLE_NYC_N = SCHEMA + ".nyc_neighborhood"
TABLE_TAXI = "demo.nyctaxi"
TABLE_TAXI_EW = SCHEMA + ".nyctaxi_edges_weights_wkt"

JOIN_TAXI = SCHEMA + ".taxi_tables_joined"

GRAPH_T = SCHEMA + ".nyctaxi_graph_wkt"
TABLE_GRAPH_T_MRSOLVED = GRAPH_T + "_multiple_routing_solved"

Graph Creation

One graph is used for this example: nyctaxi_graph_wkt, a graph utilizing WKT based on a modified version of the standard NYC Taxi dataset (mentioned in Prerequisites).

To filter out data that could skew graph nyctaxi_graph_wkt, the NYC Neighborhood dataset must be inserted into Kinetica and joined to the NYC Taxi dataset using STXY_CONTAINS to remove any trip points in the NYC Taxi dataset that are not contained within the geospatial boundaries of the NYC Neighborhood dataset:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

join_taxi_tables_response = kinetica.create_join_table(
    join_table_name = JOIN_TAXI,
    table_names = [TABLE_TAXI + " as t", TABLE_NYC_N + " as n"],
    column_names = [
        "CONCAT(CHAR32(pickup_longitude), CHAR32(pickup_latitude)) as pickup_name",
        "t.pickup_longitude", 
        "t.pickup_latitude",
        "HASH(t.pickup_longitude + t.pickup_latitude) as pickup_id",
        "CONCAT(CHAR32(dropoff_longitude), CHAR32(dropoff_latitude)) as dropoff_name",
        "t.dropoff_longitude", 
        "t.dropoff_latitude",
        "HASH(t.dropoff_longitude + t.dropoff_latitude) as dropoff_id",
        "t.total_amount"
    ],
    expressions = [
        "(STXY_CONTAINS(n.geom, t.pickup_longitude, t.pickup_latitude)) AND"
        "(STXY_CONTAINS(n.geom, t.dropoff_longitude, t.dropoff_latitude)) "
    ]
)["status_info"]["status"]

Before nyctaxi_graph_wkt can be created, the edges must be derived from the taxi_tables_joined dataset's XY pickup and dropoff pairs to create the nyctaxi_edges_weights_wkt dataset; note that REMOVE_NULLABLE is used to remove a nullable column property, which would prevent the graph from being created:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

edges_wkt_response = kinetica.create_projection(
    table_name = JOIN_TAXI,
    projection_name = TABLE_TAXI_EW,
    column_names = [
        "REMOVE_NULLABLE(ST_MAKELINE("
            "ST_MAKEPOINT(pickup_longitude, pickup_latitude),"
            "ST_MAKEPOINT(dropoff_longitude, dropoff_latitude)"
        ")) AS tripwkt",
        "total_amount"
    ],
    options = {}
)["status_info"]["status"]

Now, nyctaxi_graph_wkt is created with the following characteristics:

• It is not directed because the trips aren't in a meaningful order
• It has no explicitly defined nodes because the example relies on the implicit nodes attached to the defined edges
• The edges are identified by WKTLINE, using WKT LINESTRINGs from the tripwkt column of the nyctaxi_edges_weights_wkt table; and the weights are identified by WEIGHT_VALUESPECIFIED, using the dollar amounts found in the total_amount column.
• It has no explicitly defined weights, as those were defined as part of the edge configuration above
• It has no inherent restrictions for any of the nodes or edges in the graph
• It will be replaced with this instance of the graph if a graph of the same name exists (recreate).
• If nodes are within 0.01 degrees of each other, they will be merged together (merge_tolerance)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

create_t_graph_response = kinetica.create_graph(
    graph_name = GRAPH_T,
    directed_graph = False,
    nodes = [],
    edges = [
        TABLE_TAXI_EW + ".tripwkt AS WKTLINE",
        TABLE_TAXI_EW + ".total_amount AS WEIGHT_VALUESPECIFIED"
    ],
    weights = [],
    restrictions = [],
    options = {
        "recreate": "true",
        "merge_tolerance": "0.01"
    }
)

Multiple Routing

Before the nyctaxi_graph_wkt graph is solved, the source node and destination nodes are defined.

1
2
3
4
5

source_node = "POINT(-73.98438262939453 40.76493835449219)"  # node 2
destination_nodes = [
    "POINT(-73.97122955322266 40.74700927734375)",  # node 39
    "POINT(-73.97740173339844 40.77263641357422)"  # node 85
]

Next, the graph is solved with the solve results being exported to the response:

1
2
3
4
5
6
7

kinetica.solve_graph(
    graph_name = GRAPH_T,
    solver_type = "MULTIPLE_ROUTING",
    source_nodes = [source_node], 
    destination_nodes = destination_nodes,
    solution_table = TABLE_GRAPH_T_MRSOLVED
)

The cost for the source node to visit the destination nodes is represented as total amount in dollars:

1
2
3
4
5

+---------------+
|   Cost (in $) |
|---------------|
|        230.63 |
+---------------+