Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#406 closed defect (invalid)

shp2pgsql commandline sometimes creating geometry index without -I option

Reported by: robe Owned by: pramsey
Priority: high Milestone: PostGIS 1.5.0
Component: postgis Version: master
Keywords: Cc:

Description

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/

Change History (12)

comment:1 by pramsey, 13 years ago

Resolution: invalid
Status: newclosed

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;

comment:2 by pramsey, 13 years ago

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

comment:3 by pramsey, 13 years ago

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;

comment:4 by robe, 13 years ago

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

comment:5 by robe, 13 years ago

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.

comment:6 by pramsey, 13 years ago

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;

comment:7 by robe, 13 years ago

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.

comment:8 by robe, 13 years ago

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?

comment:9 by pramsey, 13 years ago

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.

comment:10 by robe, 13 years ago

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.

comment:11 by pramsey, 13 years ago

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

comment:12 by robe, 13 years ago

Summary: DBF only load in shp2pgsql is brokenshp2pgsql commandline sometimes creating geometry index without -I option

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

Note: See TracTickets for help on using tickets.