shp2pgsql commandline sometimes creating geometry index without -I option

[robe]

Paul is going to kill us for holding up the release. hmm the dbf loader seems to have broken in 1.5. We came across it when just testing our new tiger geocoder loading algorithm. Sorry for calling stop the presses so late.

shp2pgsql -p -W "latin1" tl_2009_11001_featnames.dbf featnames | psql

get this error: CREATE TABLE ERROR: column "the_geom" does not exist

This is loading a tiger file with no corresponding shape again from

​http://www2.census.gov/geo/tiger/TIGER2009/11_DISTRICT_OF_COLUMBIA/11001_District_of_Columbia/

[pramsey]

Shouldn't you have to use the -n flag to get a dbf-only load? works for me:

~/Code/postgis-svn/loader/shp2pgsql -n -p -W "latin1" dbftest.dbf 
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "" (gid serial PRIMARY KEY,
"id" numeric(11,0),
"name" varchar(4));
COMMIT;

[pramsey]

You'll note the table name is missing in my example above, that is fixed in r5194.

[pramsey]

I realized my test wasn't exactly like yours because my dbf file was next to shp and shx files, so I removed them and dropped the -n flag, but the import still was valid.

$ ~/Code/postgis-svn/loader/shp2pgsql -p -W "latin1" dbftest.dbf
dbftest.dbf: shape (.shp) or index files (.shx) can not be opened, will just import attribute data.
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "dbftest" (gid serial PRIMARY KEY,
"id" numeric(11,0),
"name" varchar(4));
COMMIT;

[robe]

Alright let me redouble check then. Maybe there ws something wrong with my version.

[robe]

Paul still compiling. I may have given you the wrong statement -- it may have been my -a option that was failing. Because I first prepare and then batch load with -a. Please try

~/Code/postgis-svn/loader/shp2pgsql -a -W "latin1" dbftest.dbf

and

~/Code/postgis-svn/loader/shp2pgsql -n -a -W "latin1" dbftest.dbf

I'll try once I get my build up.

[pramsey]

Those both seem to output valid runs:

paul-ramseys-macbook-2:~ pramsey$  ~/Code/postgis-svn/loader/shp2pgsql -a -W "latin1" dbftest.dbf
dbftest.dbf: shape (.shp) or index files (.shx) can not be opened, will just import attribute data.
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "dbftest" ("id","name") VALUES ('1','Paul');
COMMIT;
paul-ramseys-macbook-2:~ pramsey$  ~/Code/postgis-svn/loader/shp2pgsql -n -a -W "latin1" dbftest.dbf
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
INSERT INTO "dbftest" ("id","name") VALUES ('1','Paul');
COMMIT;

[robe]

Hold on checking to see what the hell our script is doing. We are using the new binaries and its throwing the same error, but when I try your examples it works as you said.

switching my script to use the 1.4 binaries works fine. So something is worng, but it could be jsut a bug our script was exploiting in 1.4.

I'm tracing it now.

[robe]

Okay this is really mind boggling. This is my exact script that is failing.

C:\GISData\temp>%PGBIN%\shp2pgsql -a -s 4269 -g the_geom -W "latin1" tl_2009_110 01_addr.dbf tiger_data.DC_addr > addr.sql

And its failing on creating the geometry index? But I don't have a -I and yet its adding this to the end

CREATE INDEX "dc_addr_the_geom_gist" ON "tiger_data"."dc_addr" using gist ("the_geom" gist_geometry_ops);

here is that coming from? is there some magical environment variable I am setting somewhere?

[pramsey]

I have a guess, unitiialized variable, I bet... that dirty windows memory is causing it be true for you and false for me... I'll have a hunt.

[robe]

Well funny thing is if I try your examples it works fine and doesn't try to create an index, but that sequence of variables seems to throw it off.

Admittedly the -s and -g are superfluous because I'm too damn lazy to itemize which tables in tiger have and don't have geometries associated with them. But it still shouldn't be trying to create an index when I never asked for one.

[pramsey]

config->createindex now has an initialization to 0 at r5195 try it out

[robe]

That works. I promise to not look under the covers any more until after we release :)