[fdo-users] AutoCAD Map 3d 2011 BulkCopy to PostGIS DB with FDOPostgreSQL Provider 3.5

[Hermann Karl]

Dear List,

########################################################################
#########################################

###   an Import of towns_poly.shp with shp2pgsql results in following
table
###   when mapping the columns from the shapefile "towns.shp" to the
columns of  the table "towns" in the postgis db bulkcopy will work

########################################################################
#########################################

-- Table: towns

-- DROP TABLE towns;

CREATE TABLE towns
(
  gid serial NOT NULL,
  objectid bigint,
  towns_id bigint,
  town_id smallint,
  town character varying(21),
  fips_stco integer,
  ccd_mcd character varying(3),
  fips_place character varying(5),
  pop1980 integer,
  pop1990 integer,
  pop2000 integer,
  popch80_90 integer,
  popch90_00 integer,
  fourcolor smallint,
  "type" character varying(2),
  island smallint,
  fips_mcd integer,
  fips_count smallint,
  shape_area numeric,
  shape_len numeric,
  the_geom geometry,
  CONSTRAINT towns_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'POLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 26986)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE towns OWNER TO postgres;

########################################################################
########################################################

###   bulkcopy of the shapefile "towns.shp" with FDOPostgreSQL Provider
3.5 to the public schema in postgis will result in the table
"towns_fdopoly" below
###   the wrong srid 31254 in the create table statement is an srid of a
dataset which was already present in the schema public. The correct srid
of the prj file of "towns.shp" is 26986 (MA83)
###   copying of the data in the columns shape_area and shape_len from
towns.shp to the postgis table which is already created from the
FDOPostgreSQLProvider as [numeric (19,11)]
###   causes following error
###   RDBMS: [PGRES_FATAL_ERROR] ERROR: Overflow by type >>numeric<<
###   DETAIL: rounding a value of a column with precision 19, scale 11
has to have less then 10^8

########################################################################
#########################################################


-- Table: "TOWNS_FDOPOLY"

-- DROP TABLE "TOWNS_FDOPOLY";

CREATE TABLE "TOWNS_FDOPOLY"
(
  "FeatId" serial NOT NULL,
  "OBJECTID" numeric(10,0),
  "TOWNS_ID" numeric(10,0),
  "TOWN_ID" numeric(3,0),
  "TOWN" character varying(21),
  "FIPS_STCO" numeric(5,0),
  "CCD_MCD" character varying(3),
  "FIPS_PLACE" character varying(5),
  "POP1980" numeric(6,0),
  "POP1990" numeric(6,0),
  "POP2000" numeric(6,0),
  "POPCH80_90" numeric(5,0),
  "POPCH90_00" numeric(5,0),
  "FOURCOLOR" numeric(1,0),
  "TYPE" character varying(2),
  "ISLAND" numeric(1,0),
  "FIPS_MCD" numeric(8,0),
  "FIPS_COUNT" numeric(3,0),
  "SHAPE_AREA" numeric(19,11),
  "SHAPE_LEN" numeric(19,11),
  "Geometry" geometry,
  CONSTRAINT pk_public_towns_fdopoly PRIMARY KEY ("FeatId"),
  CONSTRAINT "enforce_dims_Geometry" CHECK (st_ndims("Geometry") = 2),
  CONSTRAINT "enforce_srid_Geometry" CHECK (st_srid("Geometry") = 31254)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "TOWNS_FDOPOLY" OWNER TO postgres;

-- Index: towns_fdopoly_geometry_si

-- DROP INDEX towns_fdopoly_geometry_si;

CREATE INDEX towns_fdopoly_geometry_si
  ON "TOWNS_FDOPOLY"
  USING gist
  ("Geometry");

Karl

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-users/attachments/20100430/6de71ee8/attachment.html