Advancing Chicago’s Walkable Network

GOAL THIS SEMESTER:

Analyze the walkability and popularity of areas in the Chicago Network and make changes to existing topologies and infrastructure to make less walkable areas become more walkable and advance walkability in the existing walkable neighborhoods.

WHERE WE LEFT OFF LAST SEMESTER:

Walkable Networks

HOW WE CAN USE WHAT WE’VE DONE FOR THIS SEMESTER:

We can use the data from last semester to determine both what areas are very walkable & popular and which areas are not popular and do not invite walking. This data can include sidewalk size, crime data, business license data, and even some API data to help find popularity. We can also integrate more layers into our system as we see fit.

Once we have determined the level of walkability for areas of the city. We can determine what changes need to occur in this area to promote walkability and social interaction, by determining what businesses may be missing, if more sidewalk space is needed, if more green space is needed, etc.

After determining these changes we can import it into our network and see if and how it can advance existing walkability in that area.

WHAT WE HAVE LOOKED AT SO FAR:

Business LicensesOtherLicenseRoutes

 

UML Diagram

UML Diagram

Advertisements

Making Routes and Visualizing in QGIS Using SQL

PgRouting analyzes graphs to find the shortest path between two nodes through edges. In a street network, you can think of nodes as intersections and edges as the streets themselves. I will be using the Chicago street network, which can be found here. I have also uploaded it to the server, named chicagostreets.

1. In order to develop data capable of using PgRouting a source and target geometry must be set up at the start and end of each road. These will serve as the start and end destination points. To perform this follow the “Creating a routable road network” section of this tutorial (blog also has many helpful QGIS tutorials). Another thing to get familiar with in SQL is the WITH Clause. This allows you to reference other SQLs within one query.

2. Once your network is made you can start routing. Follow this tutorial to get multiple options for routing. pgr_dijkstra and pgr_kdijkstraPath are two that work well for visualizing one path or one startpoint to many paths.

pgr_kdijkstraPath3. By joining the geometries in your network to the results of your routing you can import this into QGIS and see your paths.

To reference my SQL query from the tutorial in class, click link.

SQL and PgRouting Continued

In order to get pg routing to work, first a transportation network must be formed with a source and target column created to specify which start and end points are used. The SQL below creates a network  in a buffer of a 1 mile radius and examines the Damen CTA Stop. To alter the name of the table created, change the buffer distance, or to change the CTA Stop, change the red values in the SQL below.

CREATE TABLE damennetwork AS
WITH ctastop AS
(SELECT * FROM cta_railstations
WHERE gid = 32),
transpo AS
(SELECT
row_number() OVER (ORDER BY transportation.gid)::integer AS gid,
transportation.gid AS id, 
transportation.street_nam AS name,
transportation.length AS cost,
transportation.geom,
st_startpoint(ST_LineMerge(transportation.geom)) as source,
st_endpoint(ST_LineMerge(transportation.geom)) as target
FROM ctastop
LEFT JOIN transportation
ON st_within(transportation.geom, 
st_setsrid(st_buffer(ctastop.geom, 5280), 3435))),
nodes AS
(SELECT DISTINCT transpo.source AS gid 
FROM transpo
UNION
SELECT DISTINCT transpo.target AS gid 
FROM transpo)
SELECT transpo.gid, transpo.id, transpo.name, transpo.cost, transpo.geom, source.id as source, target.id as target
FROM transpo
JOIN 
(SELECT row_number() OVER (ORDER BY nodes.gid)::integer AS id, 
nodes.gid AS geom
FROM nodes
GROUP BY nodes.gid)
AS source ON transpo.source = source.geom
JOIN 
(SELECT row_number() OVER (ORDER BY nodes.gid)::integer AS id, 
nodes.gid AS geom
FROM nodes
GROUP BY nodes.gid) 
AS target ON transpo.target = target.geom

To be able to find a route with this SQL use the below SQL. Input the name of your table used in the previous SQL where there are the “damennetwork” in red. To change to start and end points, change to “502” and “341” respectfully.

SELECT seq,id1 as node, id2 as edge, route.cost, damennetwork.name as streetname, damennetwork.geom 
FROM pgr_dijkstra('
 SELECT gid AS id,
 source::integer,
 target::integer,
 cost::double precision AS cost
 FROM damennetwork', 502, 341, false, false ) 
AS route
LEFT JOIN damennetwork
ON route.id2 = damennetwork.gid;

What we have been working on is using the CTA stop as a start point and all the venues as the end points. The first thing I did was take all the venues in the same buffer as the network and corresponded them to their closest edge (the target). Different buffers and train stops can be chosen by the the red numbers.

WITH ctastop AS
(SELECT * FROM cta_railstations
WHERE gid = 32),
p AS
(SELECT
venues.gid AS gid, 
venues."legal name" AS company,
venues."doing busi" AS name,
venues.address AS address,
venues.latitude AS lat,
venues.longitude AS lon,
venues.geom AS geom
FROM 
ctastop
LEFT JOIN venues
ON st_within(venues.geom, 
st_buffer(ctastop.geom, 5280)))
SELECT rcost.*, p_to_l.targetID, p_to_l.geom FROM
(SELECT p.gid as restaurantID,
min(st_distance(p.geom,l.geom)) as distance
FROM p, damennetwork AS l
GROUP BY restaurantid) 
AS rcost
LEFT JOIN 
(SELECT p.gid as restaurantID, l.target as targetID, l.geom,
st_distance(p.geom,l.geom) as distance
FROM p, damennetwork AS l) 
AS p_to_l
ON rcost.distance = p_to_l.distance
AND rcost.restaurantID = p_to_l.restaurantID

The same must be done to find the start point (source) which is the CTA stop. Change to red value to select a different trainstop.

SELECT * FROM (WITH ctastop AS
(SELECT * FROM cta_railstations
WHERE gid = 32)
SELECT row_number() OVER (ORDER BY sourceid.distance)::integer AS gid,
sourceid.* FROM (
SELECT p.gid as restaurantID, l.source as sourceID, l.geom,
st_distance(p.geom,l.geom) as distance
FROM ctastop AS p, damennetwork AS l) as sourceid) as source
WHERE source.gid = 1

This gave me a result of a 292 start point, which I will be plugging into the next SQL (in red). The other numbers are the results from the venue target table formed two SQLs ago.

CREATE TABLE routetestwm AS
SELECT seq,id1 as path, id2 as node, id3 as edge, route.cost, damennetwork.name, damennetwork.geom
FROM pgr_kdijkstraPath('
 SELECT gid AS id,
 source::integer,
 target::integer,
 cost::double precision AS cost
 FROM damennetwork', 292, array[336,457,227,179,179,291,351,515,424,244,427,250,349,457,138,291,291,263,230,117,115,208,325,594,284,301,368,116,91,471,543,398,251,251,251,230,230,365,138,463,404,301,356,212,342,203,203,342,478,286,292,519,41,242,242,242,242,242,242,356,57,57,289,310,286,452,277,251,212,427,251,182,242,471,445,251,251,93,291,286,305,262,46,262,46,262,288,288,371,117,398,105,105,286], 
 false, false ) 
AS route
LEFT JOIN damennetwork
ON route.id3 = damennetwork.gid;

This table can be brought into qgis as the network of paths from a CTA stop to any venue.

All Routes

SQL and PG routing

In order to begin pgrouting you must first set up a a table containing all of your nodes and all of your edges.

My process started with taking all of the roads within a mile buffer of the train stop, finding the nodes at the intersections, then combining the data to create a usable network for routing. A simple tutorial on this can be found under the Create a Routable Road Network section on this blog post.

My interpretation is combined into one step below. Where it says damennetwork is where I name my table. This table will be referenced in the pgRouting SQL later. Anywhere that the SQL says 32 is where I am referencing the gid of the damen train stop. This can be changed to any CTA train stop in Chicago.

CREATE TABLE damennetwork AS
(SELECT transpo.gid, transpo.id, transpo.name, transpo.cost, transpo.geom, source.id as source, target.id as target
FROM
(SELECT
row_number() OVER (ORDER BY transportation.gid)::integer AS gid,
transportation.gid AS id,
transportation.street_nam AS name,
transportation.length AS cost,
transportation.geom,
transportation.geom AS source,
transportation.geom AS target
FROM
(SELECT * FROM cta_railstations
WHERE gid = 32) AS damen
LEFT JOIN transportation
ON st_within(transportation.geom,
st_setsrid(st_buffer(damen.geom, 5280), 3435)))
AS transpo
JOIN
(SELECT row_number() OVER (ORDER BY nodes.gid)::integer AS id,
nodes.gid AS geom
FROM (
SELECT DISTINCT transpo.source AS gid FROM
(SELECT
row_number() OVER (ORDER BY transportation.gid)::integer AS gid,
transportation.gid AS id,
transportation.street_nam AS name,
transportation.length AS cost,
transportation.geom,
transportation.geom AS source,
transportation.geom AS target
FROM
(SELECT * FROM cta_railstations
WHERE gid = 32) AS damen
LEFT JOIN transportation
ON st_within(transportation.geom,
st_setsrid(st_buffer(damen.geom, 5280), 3435))) AS transpo
UNION
SELECT DISTINCT transpo.target AS gid FROM
(SELECT
row_number() OVER (ORDER BY transportation.gid)::integer AS gid,
transportation.gid AS id,
transportation.street_nam AS name,
transportation.length AS cost,
transportation.geom,
transportation.geom AS source,
transportation.geom AS target
FROM
(SELECT * FROM cta_railstations
WHERE gid = 32) AS damen
LEFT JOIN transportation
ON st_within(transportation.geom,
st_setsrid(st_buffer(damen.geom, 5280), 3435))) AS transpo
) AS nodes
GROUP BY nodes.gid)
AS source ON transpo.source = source.geom
JOIN
(SELECT row_number() OVER (ORDER BY nodes.gid)::integer AS id,
nodes.gid AS geom
FROM (
SELECT DISTINCT transpo.source AS gid FROM
(SELECT
row_number() OVER (ORDER BY transportation.gid)::integer AS gid,
transportation.gid AS id,
transportation.street_nam AS name,
transportation.length AS cost,
transportation.geom,
transportation.geom AS source,
transportation.geom AS target
FROM
(SELECT * FROM cta_railstations
WHERE gid = 32) AS damen
LEFT JOIN transportation
ON st_within(transportation.geom,
st_setsrid(st_buffer(damen.geom, 5280), 3435))) AS transpo
UNION
SELECT DISTINCT transpo.target AS gid FROM
(SELECT
row_number() OVER (ORDER BY transportation.gid)::integer AS gid,
transportation.gid AS id,
transportation.street_nam AS name,
transportation.length AS cost,
transportation.geom,
transportation.geom AS source,
transportation.geom AS target
FROM
(SELECT * FROM cta_railstations
WHERE gid = 32) AS damen
LEFT JOIN transportation
ON st_within(transportation.geom,
st_setsrid(st_buffer(damen.geom, 5280), 3435))) AS transpo
) AS nodes
GROUP BY nodes.gid)
AS target ON transpo.target = target.geom);

When plugged into QGIS, this is the result:

damennetwork

 

 

…. TO BE CONTINUED

Group 1: Where are we?

Ideas

The launch point of our conversation regarding the prototype which is focused on tracking people and things, hovered around the idea of “dynamic transportation networks”.  An interesting and ambitious idea to say the least, but perhaps the idea is more solution than design tool.  To open up the proposition and get at some of the fundamentals that might drive the components of such a solution toward design tools, lets abstract back from bus’s + people.

At the very least one would have to know about the following things:

  • where groups of people were at and where they wanted to go
  • the state of the network (roads) on which the buses operated
  • where buses are within the network and what the state of those buses are

To generate dynamic routes for the buses would then involve an algorithm which could find and evaluate paths within the network.  This is not unlike systems currently in use by emergency management services to dispatch people and resources for things such as building fires.

To continue the abstraction we could say that the three things we need to be able to do are

  • identify properties of agents which occupy the network in continuous and discontinuous ways.  What or who is at or near a particular node or edge within the network and what properties are exposed to them.
  • manage the nodes and edges of the network, what are their properties? how do we logically traverse the edges of the network?
  • formulate singular paths or sub networks from within the network.  Consider the way in which a single path through a collection of network nodes, is itself a network, if only a simple one

In this way, the underlying mechanisms that enable us to find say the fastest, or shortest route for a bus might be the same mechanisms that we would use to form ad-hoc networks through nearly any system which can be considered as a network.  Perhaps the prototyped design tool might allow it’s user to generate or identify particular sub networks which a person or thing is connected to based on where they are at in space?  Of course one of the more interesting conditions of this type of network thinking, still has to do with our “position” or “location” in a network, but perhaps those networks might be logical networks but not physical networks.  For instance, where are you located with respect to the social or professional network that is the Architecture community of Chicago, or of the world.  Through forming, collecting and analyzing these sub networks over time perhaps we can begin to understand not only where we are or what we are connected to, but how the super networks might be better organized.  What kinds of feedback loops can be  established here?

Ultimately this question of “Where Is Something”, physically, logically, semantically, is at the heart of what will see in the emerging paradigm of contextual computing.

Technology

Graphs

The graph, a term for network from the area of mathematics referred to as graph theory, describes a collection of nodes and the edges which connect them.  Graph theory is one of the most critical and fundamental aspects to computation and data.  Much of the underlying data structures for modern software rely on graph representations of system components.   Learning how to logically or computationally move or traverse through graphs is essential to searching, sorting, analyzing, and generating: algorithm’s such as Depth-first searchBreadth-first searchDijkstra’s algorithmNearest neighbour algorithm.  More specifically algorithm’s such as A* pathfinding algorithms, which underpin much of the artificial intelligence world from video games to data mining, enable goal or objective based navigation of complex networks.

Networks in Databases
To get started operating on data networks it is imperative that we properly store our networked data.  While we will likely not be utilizing explicitly graph oriented databases, it is worth mentioning that databases organized around the premise of graph’s exist.  In the studio and seminar we will be focusing on the utilization of the postgres/postgis relational database.  Fortunately the prevalence of networks in geospatial thinking has lead to the development of some critical tools which aid in our analysis of networks using postgis data structures.

pgRouting extends the PostGIS / PostgreSQL geospatial database to provide geospatial routing functionality.

Advantages of the database routing approach are:

  • Data and attributes can be modified by many clients, like Quantum GIS and uDigthrough JDBC, ODBC, or directly using Pl/pgSQL. The clients can either be PCs or mobile devices.
  • Data changes can be reflected instantaneously through the routing engine. There is no need for precalculation.
  • The “cost” parameter can be dynamically calculated through SQL and its value can come from multiple fields or tables.

In addition a series of tools for importing data such as street networks have been developed for pgRouting enabling aquisition from sources such as Open Street MapOSM2PGSQL, and OSM2PGROUTING
Tutorials: beginners guide, workshop (extensive)

Startup technologies (first prototype)
Without presuming too much about the prototype, it’s safe to say that the system would take some kind of input (perhaps from the physical world i.e. sensor or maybe through an interactive interface), analysis or computation would be performed and some type of network oriented output produced.

Because of the immediacy to topics of transportation and logistics which spear headed the project, I would suggest that you simply begin with the transportation network data which we have access to through the data portal and OSM.  A first pass tech prototype would therefore include:

  • the process of importing this data into pgRouting friendly structures
  • demonstrable querying and route formation using pgRouting SQL queries
  • the representation of generated routes within qgis or google earth.
  • Additional considerations might include the integration of CTA api data into our database.  This would involve a simple app which could parse the XML data from the CTA’s system into table structures in our PostGres db for inclusion with base network data.

From this point, we can validate functionality and begin to think more laterally about the criteria used to create and/or interact with the data.  We start with shape based networks (road center lines), developing an understanding of how pgRouting is working, then begin to explore other networks and forming networks on the fly rather than as shape file imports.

Research Terms

  • Graph Theory
  • Topology
  • Koenigsberg Bridge Problem
  • Dijkstra’s Algorithm
  • A* Pathfiinding algorithms
  • CTA Transportation API
  • XML to PGSQL
  • pgRouting
  • OSM2PGROUTING, OSM2PGSQL
  • Internet of Things
  • iBeacon, NFC, BlueToothLE
  • Mesh networking