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

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

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).