[postgis-devel] Shp2pgsql picks wrong field type?

[Markus Schaber]

Hi, Mark,

Mark Cave-Ayland schrieb:

> Here's another problem I've found with the shapefile loader for PostGIS
> 1.0.0-rc6 where it picks up the wrong datatype for a column.

This and optimizing possibilities (i. E. if a char(1) field is
guaranteed to only contain Y and N we can make boolean out of it, or
some ints can be even made an int2) is the reason why I invented the -p
flag (see my patches). We usually save the output of shp2pgsql and
modify it to fulfil our needs, resulting in a file that creates all ouf
our tables, and a second one that adds the indices after data insertion.

Another reason is that we can add additional columns (e. G. source of
data or release version) that are implicitly set by modifying their
columns DEFAULT values before inserting the data via shp2pgsql -a -D.
Thankfully, shp2pgsql includes the column names in their COPY and InSERT
statements, so this works great. We have up to about 50 shapefiles with
identical colunms inserted into the same table, and this way we can
easily know which row originated from which shapefile.

In addition to the speedup, using -D forces an all-or-nothing approach,
you do not end up with half-loaded shapefiles.

> Taking a closer look, it appears as if shp2pgsql determines the wrong
> datatype for the "fraddr" field, which shp2pgsql assumes is an int8. This is
> fine until the .sql script gets to line 1153 at which point we find the
> "fraddr" field is set to '11-82' which of course will cause the insert to
> fail.
> I'm not sure yet whether this is an incorrectly generated shapefile or a
> bug, however what should shp2pgsql do (if anything) if it detects data that
> doesn't correctly match the field type?

Maybe you can open the associated dbf file containing this data with any
dbf reading tool, and see what the column type is. If it is something
numeric, then I would blame the shapefile generator.

Markus