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.
3. By joining the geometries in your network to the results of your routing you can import this into QGIS and see your paths.
Tips: Arduino code is not supported by new version, so you have to alter the code.
The resistor have to be around 300K, because human body's resistance is about 300K , if it is too low,
the reading would be very low(almost zero), if it too high, the reading woud be 1023.
The basic principle is that the analogRead is actually reading the voltage of the resistor, not the body,
if body resistance is much much bigger than the resistor, even if the body resistance changed , the voltage
of the resistor will not change much(I am sure we have learned this in senior high school)
Data Model Prototype 