Deal of the Day

Home » Main » Manning Forums » 2009 » PostGIS in Action

Thread: Data to go with code listings 8.13 and 8.14

Welcome, Guest Help
Login Login / Register
Guest Settings Guest Settings
Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 5 - Pages: 1 - Last Post: Jul 23, 2010 12:36 AM by: regina.leo
dputler

Posts: 11
Registered: 6/11/10
Data to go with code listings 8.13 and 8.14
Posted: Jul 1, 2010 8:05 PM
  Click to reply to this thread Reply

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

Posts: 211
Registered: 5/6/09
Re: Data to go with code listings 8.13 and 8.14
Posted: Jul 1, 2010 8:47 PM   in response to: dputler in response to: dputler
  Click to reply to this thread Reply

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

Posts: 11
Registered: 6/11/10
Re: Data to go with code listings 8.13 and 8.14
Posted: Jul 12, 2010 11:50 AM   in response to: regina.leo in response to: regina.leo
  Click to reply to this thread Reply

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

Posts: 211
Registered: 5/6/09
Re: Data to go with code listings 8.13 and 8.14
Posted: Jul 12, 2010 10:41 PM   in response to: dputler in response to: dputler
  Click to reply to this thread Reply

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

Posts: 11
Registered: 6/11/10
Re: Data to go with code listings 8.13 and 8.14
Posted: Jul 12, 2010 11:55 PM   in response to: regina.leo in response to: regina.leo
  Click to reply to this thread Reply

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.178)'));

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

Posts: 211
Registered: 5/6/09
Re: Data to go with code listings 8.13 and 8.14
Posted: Jul 23, 2010 12:36 AM   in response to: dputler in response to: dputler
  Click to reply to this thread Reply

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

Pages: 1
Back to Thread List Back to Thread List
Legend
Gold: 300 + pts
Silver: 100 - 299 pts
Bronze: 25 - 99 pts
Manning Author
Manning Staff