dputler (11) [Avatar] Offline
#1
The code contained in Listings 8.13 and 8.14 relates to tables called point_table and line_table, but I can't find these tables referenced earlier in the book and can't find them in the data zip archive. However, the data is displayed in Figure 8.1. Was not including these tables intentional or an oversight?
regina.leo (265) [Avatar] Offline
#2
Re: Data to go with code listings 8.13 and 8.14
Actually there is no data for that one. I guess we should make up some data so people can try it out. We had tested it on some project data which we can't give out.

The example was hypothetical based on Paul's example here

http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html
dputler (11) [Avatar] Offline
#3
Re: Data to go with code listings 8.13 and 8.14
I cooked some data for this (and for use in trying to get a better understanding of how to work with pgRouting), which I'd be happy to PD so you can use it as you wish.
regina.leo (265) [Avatar] Offline
#4
Re: Data to go with code listings 8.13 and 8.14
Thanks. What kind of example did you come up with. Still in the middle of revamping chapter 3, so that is all on our mind at the moment.
dputler (11) [Avatar] Offline
#5
Re: Data to go with code listings 8.13 and 8.14
A simple grid road network, with a "spur". In addition, four points called "sites". The idea is a standard downtown grid road network, and the locations aren't on the centerlines (which would be the case if you were working with parcel In some sense, it is probably easier to browse the SQL, so here it is:

CREATE SCHEMA chk;



SET search_path = chk, pg_catalog, public;



-- Create a small road network



CREATE TABLE roads (

gid serial PRIMARY KEY,

rd_name varchar(50),

rd_type varchar(10),

rd_dir varchar(2),

l_from_num integer,

l_to_num integer,

r_from_num integer,

r_to_num integer,

trvl_time real

);



SELECT AddGeometryColumn('chk','roads','the_geom',-1,'LINESTRING',2);



INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('First', 'Ave', 'N', 101, 191, 102, 190, 0.2, ST_GeomFromText('LINESTRING(0 2, 0 3)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('First', 'Ave', 'N', 201, 291, 202, 290, 0.2, ST_GeomFromText('LINESTRING(0 3, 0 4)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('First', 'Ave', 'N', 301, 391, 302, 390, 0.2, ST_GeomFromText('LINESTRING(0 4, 0 5)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Second', 'Ave', 'N', 101, 191, 102, 190, 0.2, ST_GeomFromText('LINESTRING(1 2, 1 3)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Second', 'Ave', 'N', 201, 291, 202, 290, 0.2, ST_GeomFromText('LINESTRING(1 3, 1 4)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Second', 'Ave', 'N', 301, 391, 302, 390, 0.2, ST_GeomFromText('LINESTRING(1 4, 1 5)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Third', 'Ave', 'N', 101, 191, 102, 190, 0.15, ST_GeomFromText('LINESTRING(2 2, 2 3)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Third', 'Ave', 'N', 201, 291, 202, 290, 0.15, ST_GeomFromText('LINESTRING(2 3, 2 4)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Third', 'Ave', 'N', 301, 391, 302, 390, 0.15, ST_GeomFromText('LINESTRING(2 4, 2 5)'));

INSERT INTO roads (rd_name, rd_type, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Main', 'St', 101, 191, 102, 190, 0.2, ST_GeomFromText('LINESTRING(0 4, 1 4)'));

INSERT INTO roads (rd_name, rd_type, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Main', 'St', 201, 291, 202, 290, 0.2, ST_GeomFromText('LINESTRING(1 4, 2 4)'));

INSERT INTO roads (rd_name, rd_type, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Oak', 'St', 101, 191, 102, 190, 0.2, ST_GeomFromText('LINESTRING(0 3, 1 3)'));

INSERT INTO roads (rd_name, rd_type, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Oak', 'St', 201, 291, 202, 290, 0.2, ST_GeomFromText('LINESTRING(1 3, 2 3)'));

INSERT INTO roads (rd_name, rd_type, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Walnut', 'St', 101, 191, 102, 190, 0.2, ST_GeomFromText('LINESTRING(0 2, 1 2)'));

INSERT INTO roads (rd_name, rd_type, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Walnut', 'St', 201, 291, 202, 290, 0.2, ST_GeomFromText('LINESTRING(1 2, 2 2)'));

INSERT INTO roads (rd_name, rd_type, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Maple', 'St', 401, 491, 402, 490, 0.2, ST_GeomFromText('LINESTRING(3 1, 4 1)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Elm', 'Dr', 'S', 100, 198, 101, 199, 0.2, ST_GeomFromText('LINESTRING(2 2, 3 1)'));

INSERT INTO roads (rd_name, rd_type, rd_dir, l_from_num, l_to_num, r_from_num, r_to_num, trvl_time, the_geom)

VALUES ('Elm', 'Dr', 'S', 202, 290, 201, 291, 0.2, ST_GeomFromText('LINESTRING(3 1, 3 0)'));



-- Create a set of points along the road network



CREATE TABLE sites (

gid serial PRIMARY KEY,

house_num integer,

rd_name varchar(50),

rd_type varchar(10),

rd_dir varchar(2)

);



SELECT AddGeometryColumn('chk','sites','the_geom',-1,'POINT',2);



INSERT INTO sites (house_num, rd_name, rd_type, rd_dir, the_geom)

VALUES (197, 'Elm', 'Dr', 'S', ST_GeomFromText('POINT(2.81 1.032)'));

INSERT INTO sites (house_num, rd_name, rd_type, rd_dir, the_geom)

VALUES (204, 'First', 'Ave', 'N', ST_GeomFromText('POINT(0.066 3.17smilie'));

INSERT INTO sites (house_num, rd_name, rd_type, rd_dir, the_geom)

VALUES (387, 'Third', 'Ave', 'N', ST_GeomFromText('POINT(1.891 4.951)'));

INSERT INTO sites (house_num, rd_name, rd_type, rd_dir, the_geom)

VALUES (301, 'Second', 'Ave', 'N', ST_GeomFromText('POINT(0.856 4.071)'));
regina.leo (265) [Avatar] Offline
#6
Re: Data to go with code listings 8.13 and 8.14
Dan,

This looks good. we'll revise the 8.13 and 8.14 to use this data set.

You mind if we include this in the code download for chapter 8. We'll give you credit for it.

Thanks,
Leo and Regina