[gdal-dev] How to append data into an existing database table

[Jeremy Palmer]

Hi All,

I've got an existing spatial table in PostgreSQL with has an existing primary key (non-serial) and shape column:

CREATE TABLE test_1
(
  id integer NOT NULL,
  appellation TEXT,
  affected_surveys TEXT,
  parcel_intent TEXT NOT NULL,
  topology_type VARCHAR(100) NOT NULL,
  statutory_actions TEXT,
  land_district VARCHAR(100) NOT NULL,
  titles TEXT,
  survey_area numeric(20,4),
  calc_area numeric(20,4) NOT NULL,
  shape geometry,
  PRIMARY KEY (id)
);

I'm trying to append data from a CSV file into a pg database but I'm having problems:

ogr2ogr -append -f PostgreSQL "PG:host=xxxx dbname=xxxx" test1.vrt -nln test_1

ERROR 1: INSERT command for new feature failed.
ERROR:  null value in column "id" violates not-null constraint

Command: INSERT INTO "public"."test_csv_table_1" ("affected_surveys", "appellation", "calc_area", "land_district", "parcel_intent", "statutory_actions", "survey_area", "titles", "topology_type") VALUES ('SO 11957, SO 1207', 'Part Sec 11 Waimea West DIST',              10.5708, 'Nelson', 'DCDB', '[Create] For the Use, Convenience and Enjoyment of a Road New Zealand Gazette 1979 p 3136',  10.0000, '', 'Primary') RETURNING id

ERROR 1: Terminating translation prematurely after failed
translation of layer test1 (use -skipfailures to skip errors)

Looks to me like it's expecting the id to be automatically populated and returned from the database. But I actually want the id values within the CSV to be used. Plus the geometry data from the CSV has been completed omitted.

Here's the CSV vrt schema:

<OGRVRTDataSource>
    <OGRVRTLayer name="test1">
        <SrcDataSource relativeToVRT="1">test1.csv</SrcDataSource>
        <GeometryType>wkbMultiPolygon</GeometryType>
        <LayerSRS>EPSG:4167</LayerSRS>
        <Field name="id" src="id" type="Integer"/>
        <Field name="appellation" src="appellation" type="String" />
        <Field name="affected_surveys" src="affected_surveys" type="String" />
        <Field name="parcel_intent" src="parcel_intent" type="String" />
        <Field name="topology_type" src="topology_type" type="String" width="100" />
        <Field name="statutory_actions" src="statutory_actions" type="String" />
        <Field name="land_district" src="land_district" type="String" width="100" />
        <Field name="titles" src="titles" type="String" />
        <Field name="survey_area" src="survey_area" type="Real" width="20" precision ="4" />
        <Field name="calc_area" src="calc_area" type="Real" width="20" precision ="4" />
        <GeometryField encoding="WKT" field="shape" reportSrcColumn="FALSE"/>
        <FID>id</FID>
    </OGRVRTLayer>
</OGRVRTDataSource>


I see there is a layer creation options that changes the fid and geometry column names, but because I'm appending it won't let me use these options.

Also note that my CSV file contains polygon and multipolygon WKT.

I have also tried doing similar operations with the spatialite and MSSQL drivers and got the same error.

I'm using gdal/ogr 1.8.0

Cheers,
Jeremy


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

This message contains information, which is confidential and may be subject to legal privilege. 
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info at linz.govt.nz) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank You.

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