[gdal-dev] Append a shapefile to a postgis table using the GDAL/OGR
Python interface
Luke Peterson
luke.peterson at gmail.com
Wed Jul 6 11:11:31 EDT 2011
On Sat, Jul 24, 2010 at 10:31PM, Chaitanya kumar CH <chaitanya.ch at
gmail.com> wrote:
>
>Esben,
>
>I am not sure why the value is not quoted in the error report. The
>PostgreSQL driver quotes the string values.
>When using CreateFeature() you need to make sure to set the feature's FID to
>OGRNullFID using SetFID(OGRNullFID). In your error report it shows that
>ogc_fid is set to 0. Perhaps there is already a feature with that value?
>
I'm reviving a nearly-year-old thread here, sort of. I'm trying to
create cross-compatibility between MSSQL and PostGIS on some OGR-based
code I wrote in Python for the MSSQL environment: I had been running
into issues simply creating layers with OGR -- would run something
like this:
name = serverDS.CopyLayer(shapeDS.GetLayerByIndex(0),table,options).GetName()
with serverDS being an open OGR datastore (destination) and shapeDS
being an opened Shapefile (with an OGR-imposed FID).
This code works fine when serverDS is a MSSQLSpatial driver, but I get
hit with the following error when I run with a PostgresSQL driver:
"You've inserted feature with an already set FID and that's perhaps
the reason for the failure. If so, this can happen if you reuse the
same feature object for sequential insertions. Indeed, since GDAL
1.8.0, the FID of an inserted feature is got from the server, so it is
not a good ideato reuse it afterwards... All in all, try unsetting the
FID with SetFID(-1) before calling CreateFeature()"
OK, clear enough. I took a look at the DataStore code here and here:
http://trac.osgeo.org/gdal/browser/branches/1.8/gdal/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp
http://trac.osgeo.org/gdal/browser/branches/1.8/gdal/ogr/ogrsf_frmts/generic/ogrdatasource.cpp
I noticed that the PG driver has no PG-native code for CopyLayer() at
all; the generic driver's CopyLayer() function doesn't set a null FID
for CreateFeature(). So, it seems CopyLayer() is useless for PostGIS
in my situation.
The code I'm going with right now uses CopyLayer() out of the box
except if serverDS.GetDriver().GetName() == 'PostgreSQL', in which
case I'm re-implementing most of the logic from CopyLayer() in Python
except adding a SetFID(-1) call:
newLayer =
serverDS.CreateLayer(table,shapeDS.GetLayerByIndex(0).GetSpatialRef(),ogr.wkbUnknown,options)
for x in
xrange(shapeDS.GetLayerByIndex(0).GetLayerDefn().GetFieldCount()):
newLayer.CreateField(shapeDS.GetLayerByIndex(0).GetLayerDefn().GetFieldDefn(x))
newLayer.StartTransaction()
for x in xrange(shapeDS.GetLayerByIndex(0).GetFeatureCount()):
newFeature = shapeDS.GetLayerByIndex(0).GetFeature(x)
newFeature.SetFID(-1)
newLayer.CreateFeature(newFeature)
if x % 128 == 0:
newLayer.CommitTransaction()
newLayer.StartTransaction()
newLayer.CommitTransaction()
So I guess my question is whether I'm missing something, or whether I
should submit a bug report or feature request on the PG driver to
create a parallel implementation for CopyLayer() that either natively
incorporates the SetFID(-1) or that takes an option that could trigger
that behavior. Seems a shame this function exists but doesn't work
under certain circumstances (again -- maybe I'm missing something?)
Regards,
Luke
>
>On Fri, Jul 23, 2010 at 7:05 PM, Esben Taudorf <eta at le34.dk> wrote:
>
>> Hi everybody
>>
>>
>>
>> I just started using the GDAL/OGR CSharp interface in Visual Studio and it
>> works great.
>>
>> I am trying to append a shapefile to a postgis table. This I can do with
>> the following lines in ogr2ogr:
>>
>>
>>
>> Fist import the shapefile to postgis a table
>>
>> ogr2ogr -f "PostgreSQL" PG:"dbname='postgis' host='localhost' port='5432'
>> user='...' password='...'" -lco PRECISION=NO -lco GEOMETRY_NAME=geom -a_srs
>> EPSG:25832 "c:\temp\testfile.shp"
>>
>>
>>
>> Then append the same shapefile (this is a test) to the same postgis table
>> like this
>>
>> ogr2ogr -append -update -f "PostgreSQL" PG:"dbname='postgis'
>> host='localhost' port='5432' user='...' password='...'"
>> "c:\temp\testfile.shp"
>>
>>
>>
>>
>>
>> But how do I do this in the GDAL/OGR CSharp interface or which classes and
>> functions do I use?
>>
>>
>>
>> So far what I have done is:
>>
>>
>>
>> Open a layer from the shapefile:
>>
>> string fileShp = @"c:\temp\testfile.shp";
>>
>> DataSource InputDataSource = Ogr.Open(fileShp, 0);
>>
>> Layer inputLayer = InputDataSource.GetLayerByIndex(0);
>>
>> FeatureDefn def = inputLayer.GetLayerDefn();
>>
>>
>>
>> Open a datasource to postgis:
>>
>> OutputDriver = Ogr.GetDriverByName("PostgreSQL");
>>
>> DataSource OutputDataSource = OutputDriver.Open("PG:dbname='postgis'
>> host='localhost' port='5432' user='...' password='...'", 1);
>>
>>
>>
>>
>>
>> Copy the layer to the postgis table:
>>
>> Layer outputLayer = OutputDataSource.CopyLayer(inputLayer, def.GetName(),
>> new string[] { "GEOMETRY_NAME=geom", "PRECISION=NO" }); //How to add -a_srs
>> EPSG:25832?
>>
>>
>>
>>
>>
>> This works to import the shapefile to a postgis table. But what should I do
>> when to append the same shapefile to the postgis a table. So far I have done
>> this:
>>
>>
>>
>> Open a layer from the shapefile:
>>
>> string fileShp = @"c:\temp\testfile.shp";
>>
>> DataSource InputDataSource = Ogr.Open(fileShp, 0);
>>
>> Layer inputLayer = InputDataSource.GetLayerByIndex(0);
>>
>> FeatureDefn def = inputLayer.GetLayerDefn();
>>
>>
>>
>> Open a datasource to postgis:
>>
>> OutputDriver = Ogr.GetDriverByName("PostgreSQL");
>>
>> DataSource OutputDataSource = OutputDriver.Open("PG:dbname='postgis'
>> host='localhost' port='5432' user='...' password='...'", 1);
>>
>>
>>
>>
>>
>> Layer outputLayer = OutputDataSource.GetLayerByName(def.GetName());
>>
>> Feature feat;
>>
>> while ((feat = inputLayer.GetNextFeature()) != null)
>>
>> {
>>
>>
>> outputLayer.CreateFeature(feat);
>>
>> }
>>
>>
>>
>> The CreateFeature functions fails with a syntax error in the INSERT
>> command. Below is a short version of the command with the syntax error:
>>
>>
>>
>> Command: INSERT INTO "testfile" ("geom" , "ogc_fid" , "name") VALUES
>> (GeomFromEWKT('SRID=-1;POINT (701678 6152444)'::TEXT) , 0 , Test)
>>
>>
>>
>> The field "name" is a character varying in postgis a the command needs
>> single quotes around 'Test'.
>>
>>
>>
>> How do I solve this syntax error?
>>
>>
>>
>> I am also wondering how to assign an output SRS when creating the postgis
>> table like the ogr2ogr flag -a_srs EPSG:25832?
>>
>>
>>
>> Any help or comment would be appreciated.
>>
>>
>>
>>
>>
>> Regards Esben.
>>
>> _______________________________________________
>> gdal-dev mailing list
>> gdal-dev at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>
>
>
>--
>Best regards,
>Chaitanya kumar CH.
>/t?a???nj?/ /k?m?r/
>+91-9494447584
>17.2416N 80.1426E
>
More information about the gdal-dev
mailing list