The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

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.
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?
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)'));
The calculation of the percentage error due to the use of the World Mercator projection in Listing 8.8 is likely not what you intended. Right now the code has one calculating the "error" of the National Atlas Equal Area projection using the World Mercator projection as the base.
Okay, I've altered the sql file to read in the bridges multilinestrings with minimal headaches, but my attempt to do with the city multipolygons ran into problems (it would hang in the middle of the inserts for no apparent reason, perhaps a timeout issue). I ultimately gave up and downloaded the original city multipolygon shapefile set from DataSF.org and read it into PostgreSQL using shp2pgsql, which seems to work much better. Below is the redone sql to read in the bridge data:

CREATE SCHEMA sf;

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = sf, pg_catalog, public;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE bridges (
gid serial primary key,
objectid integer,
id integer,
bridge_nam character varying(50)
);

SELECT AddGeometryColumn('sf', 'bridges', 'the_geom', 2227, 'MULTILINESTRING', 2);

SELECT pg_catalog.setval('bridges_gid_seq', 5, false);

INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (2, 2, 0, 'Third Street Bridge', '0105000020B3080000010000000102000000020000004F35403C08F356419108B23D8F0540414FA4C11606F35641C7446F64E9054041');
INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (3, 3, 0, 'Golden Gate Bridge', '0105000020B30800000100000001020000000200000077B3CCAB0BDA56415C8D119042344041F76F24AFB6D9564193A2BA082F3E4041');
INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (4, 4, 0, 'San Francisco Bay Bridge - East', '0105000020B3080000010000000102000000080000003137A589D3F95641B4A4C8E129324041ED02EBE186FA5641A62064FCA5334041FE94F091E4FA5641D47B4F88363440419442E6D72CFB5641D7B98E308C3440416E982E358DFB5641382BC723D7344041FD4255A1C6FF56411C58B3F1143840419EE4D4F171005741C0596F3F653840410AB4016DB20357415164D26696394041');
INSERT INTO bridges (gid, objectid, id, bridge_nam, the_geom) VALUES (1, 1, 0, 'San Francisco Bay Bridge - West', '0105000020B3080000010000000102000000030000006D50C1CA47F356411724A2475C23404167736230CEF356418289645B83244041F2AA94DD65F9564156B0B23D38314041');

CREATE INDEX idx_bridges_the_geom ON bridges USING gist (the_geom);
One thing you may want to do is make sure the public schema is always on the search path in the sql scripts that create a data table. I had a minor problem with this for the world cities data. Many folks are likely to have created the DB within the public schema, so they will have problems with the geometry column of a new table if public schema isn't in their search path.
I'm working through Chapter 8 at the moment, but ran into problems with the San Francisco Bridges, and SF Bay Area city data sql files. I'm not getting errors when using i ~/path/to/sf.bridges.sql (or sf.cities.sql) from the psql command line, but I'm not getting a geometry column registered. Trying to figure this out, I moved on to the Chapter 10 data within the postgis_in_action_data.zip, and sourced the ch10_data.sql file. The US Interstates read in correctly, but the Spanish nuclear power plant locations don't. The problem with this code comes at line 10383 of the file. The SELECT AddGeometryColumn call is missing an argument (specifically 'the_geom'). Looking through the two files for Chapter 8, I noticed that there are no calls to AddGeometryColumn, which could be the issue with them.
I'm using PostgreSQL 8.4.4 on 64bit Ubuntu 10.04. I'm not using the postgres account for this, but one under by own login ID, I'm not sure if that creates a problem in terms of getting the geometry column registered within the DB (which belongs to my ID) under the original sql code (since it explicitly gives ownership to postgres). In terms of the premature ending of the insert of the city records, that is really perplexing. It seems like it is having parse errors. If I manually feed it one record at a time (doing a cut and paste from the script file in a text editor, and pasting it into a psql session command prompt) things work. When I feed it script, the read error occurs.
A PS to my last post:

The sql file for the SF bridge data in the data archive labels the bridge name field as 'bridge_nam', while the example queries in Chapter 8 use 'bridge' as the field name.
Hi,

I just downloaded the zip file (I had already downloaded it earlier today), and confirmed that it does not contain the fastfoods.csv file. I think you may need to re-roll the archive.
I want to echo Rick01's question. Having this would be very useful. It isn't contained in the postgis_in_action_data.zip file current available from postgis.us companion site.