Opened 5 weeks ago

Closed 5 weeks ago

#5280 closed defect (fixed)

Can't load dc_place table with shp2pgsql, invalid characer varying(0)

Reported by: robe Owned by: robe
Priority: blocker Milestone: PostGIS 2.5.9
Component: loader/dumper Version: 3.3.x
Keywords: Cc:

Description

In trying to update for tiger 2022, I seem to have run into an issue with loading file - https://www2.census.gov/geo/tiger/TIGER2022/PLACE/tl_2022_12_place.zip

Using my compiled shp2pgsql for from PostGIS 3.3.1.

this command:

shp2pgsql -D -c -s 4269 -g the_geom   -W "latin1" tl_2022_11_place.dbf tiger_staging.dc_place

Is generating a table structure that looks like this:

CREATE TABLE "tiger_staging"."dc_place" (gid serial,
"statefp" varchar(2),
"placefp" varchar(5),
"placens" varchar(8),
"geoid" varchar(7),
"name" varchar(100),
"namelsad" varchar(100),
"lsad" varchar(2),
"classfp" varchar(2),
"pcicbsa" varchar(0),
"pcinecta" varchar(0),
"mtfcc" varchar(5),
"funcstat" varchar(1),
"aland" float8,
"awater" float8,
"intptlat" varchar(11),
"intptlon" varchar(12));

So it's failing on the create table because of the pcinecta, pcicbsa columns being varchar(0) which is illegal.

I've been able to load other tables.

When I use ogr_fdw, it creates a structure like this:

CREATE FOREIGN TABLE IF NOT EXISTS staging.tl_2022_11_place(
    fid bigint NULL,
    geom geometry(Polygon,4269) NULL,
    statefp character varying(2) NULL COLLATE pg_catalog."default",
    placefp character varying(5) NULL COLLATE pg_catalog."default",
    placens character varying(8) NULL COLLATE pg_catalog."default",
    geoid character varying(7) NULL COLLATE pg_catalog."default",
    name character varying(100) NULL COLLATE pg_catalog."default",
    namelsad character varying(100) NULL COLLATE pg_catalog."default",
    lsad character varying(2) NULL COLLATE pg_catalog."default",
    classfp character varying(2) NULL COLLATE pg_catalog."default",
    pcicbsa character varying NULL COLLATE pg_catalog."default",
    pcinecta character varying NULL COLLATE pg_catalog."default",
    mtfcc character varying(5) NULL COLLATE pg_catalog."default",
    funcstat character varying(1) NULL COLLATE pg_catalog."default",
    aland bigint NULL,
    awater bigint NULL,
    intptlat character varying(11) NULL COLLATE pg_catalog."default",
    intptlon character varying(12) NULL COLLATE pg_catalog."default"
)
    SERVER svr_shps
    OPTIONS (layer 'tl_2022_11_place');

Which I think is the right thing to do when no length is provided.

I haven't checked to see if this is a windows only issue, or just something that has always been present and not an issue before.

When I compare this table to the https://www2.census.gov/geo/tiger/TIGER2021/PLACE/tl_2021_12_place.zip

the 2021 registered these columns and character varying(1)

Change History (10)

comment:1 by robe, 5 weeks ago

Milestone: PostGIS 3.3.2PostGIS 2.5.9

comment:2 by Regina Obe <lr@…>, 5 weeks ago

In 8e3cbc23/git:

Handle load of dbase character fields with not width specified
References #5280 for PostGIS 3.4.0

comment:3 by Regina Obe <lr@…>, 5 weeks ago

In 0e63f02f/git:

Handle load of dbase character fields with not width specified
References #5280 for PostGIS 3.3.2

comment:4 by Regina Obe <lr@…>, 5 weeks ago

In 534fab4/git:

Handle load of dbase character fields with no width specified
References #5280 for PostGIS 3.2.4

comment:5 by Regina Obe <lr@…>, 5 weeks ago

In 30c47cf/git:

News references #5280

comment:6 by Regina Obe <lr@…>, 5 weeks ago

In 96e0a45/git:

Handle load of dbase character fields with no width specified
References #5280 for PostGIS 3.1.8

comment:7 by Regina Obe <lr@…>, 5 weeks ago

In fe5eb4f/git:

NEWS for 3.1.8 References #5280

comment:8 by Regina Obe <lr@…>, 5 weeks ago

In 5d5b9756/git:

Handle load of dbase character fields with not width specified
References #5280 for PostGIS 3.0.8

comment:9 by Regina Obe <lr@…>, 5 weeks ago

In 9bf759d/git:

NEWS for 3.0.8 References #5280

comment:10 by Regina Obe <lr@…>, 5 weeks ago

Resolution: fixed
Status: newclosed

In 7f75bde/git:

shp2pgsql: Handle load of dbase character fields
with no width specified
Closes #5280 for PostGIS 2.5.9

Note: See TracTickets for help on using tickets.