RowanM (3) [Avatar] Offline
On page 25-26 it talks about loading in shapefile data, however, the ch01.roads create table statement is missing from the previous pages (and not in the code download).
regina.leo (265) [Avatar] Offline
Re: CH01 create table for roads missing ?

We'll have to add the link to the data to the front page of this forum. I presume you were downloading the code which just has the code and not the data.

If you go to the book website:

The paper clip next to chapter 1 has both the code and the data for chapter 1.

The data download is a separate file altogther linked on the left labeled "Book Data"

Direct link below and that has the data for all the chapters
RowanM (3) [Avatar] Offline
Re: CH01 create table for roads missing ?
Hey Regina,

I'm loving the book I have to add!

Ahh okay, I see the problem now, because I was doing this via command line, problem between keyboard and chair.

It wasn't clear that the shapefile loader actually creates the table, and I must have omitted a parameter, and gotten the wrong table name.

shp2pgsql -s 4269 -g geom_4269 -I roadtrl020.shp ch01.roads > roads.sql

Still digging some more to ensure this is the equivalent of the gui operation.

Message was edited by:
RowanM (3) [Avatar] Offline
Re: CH01 create table for roads missing ?
So.. I'm getting lost on trying to find the equivalent command line options to the gui. I know if you would follow the gui based operations it would work, but in my case I can't do that - have to run via command line.

I would think it would be these two commands, the first one to generate the table, with the geometry column, then the second one to append the data for the geography column. However the append function only seems to create inserts which can't be run a second time over the roads table, as follows:

Step 1

shp2pgsql -s 4269 -g geom_4269 -I -S roadtrl020.shp ch01.roads > roads.sql

>>>>> Table
CREATE TABLE "ch01"."roads" (gid serial PRIMARY KEY,
"fnode_" numeric(11,0),
"tnode_" numeric(11,0),
"lpoly_" numeric(11,0),
"rpoly_" numeric(11,0),
"length" float8,
"roadtrl020" numeric(11,0),
"feature" varchar(80),
"name" varchar(120),
"state_fips" varchar(2),
"state" varchar(2));
SELECT AddGeometryColumn('ch01','roads','geom_4269','4269','MULTILINESTRING',2);
>>>>> Row Data
INSERT INTO "ch01"."roads" ("fnode_","tnode_","lpoly_","rpoly_","length","roadtrl020","feature","name","state_fips","state",geom_4269) VALUES ('1','2','0','0','0.041','1','Other Highway',NULL,'02','AK','0105000020AD100000010000000102000020AD1000000300000000000080D9CB64C0000000205C5350400000002011CB64C0000000C0295250400000008043CB64C0000000809C515040');

Step 2

shp2pgsql -G -a roadtrl020.shp ch01.roads > roads2.sql

INSERT INTO "ch01"."roads" ("fnode_","tnode_","lpoly_","rpoly_","length","roadtrl020","feature","name","state_fips","state",geog) VALUES ('1','2','0','0','0.041','1','Other Highway',NULL,'02','AK','01050000000100000001020000000300000000000080D9CB64C0000000205C5350400000002011CB64C0000000C0295250400000008043CB64C0000000809C515040');
INSERT INTO "ch01"."roads" ("fnode_","tnode_","lpoly_","rpoly_","length","roadtrl020","feature","name","state_fips","state",geog) VALUES ('2','3','0','0','0.023','2','Other Highway','State Route 131','02','AK','0105000000010000000102000000030000000000008043CB64C0000000809C5150400000004050CB64C0000000E07851504000000060C8CB64C0000000E094505040');
regina.leo (265) [Avatar] Offline
Re: CH01 create table for roads missing ?

Neither the gui or the command line will add a geometry/geography column to an existing table.

What you need to do for the second -- is create the geography field in SQL, and then run your append.

So what you are missing is:

Between your step 1 and step 2

step 2 pre:
ALTER TABLE ch01.roads ADD COLUMN geog geography(MULTILINESTRING,4326);

If you only need the geography column
since 4269 and 4326 are so close you can just get away with:

shp2pgsql -G roadtrl020.shp ch01.roads > roads2.sql