[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 17:36:52 EDT 2011
On Wed, Jul 6, 2011 at 3:56 PM, Even Rouault
<even.rouault at mines-paris.org> wrote:
> Le mercredi 06 juillet 2011 21:46:58, Luke Peterson a écrit :
>> What CopyLayer() is supposed to do (and the code I've written does) is
>> create a new layer based on an existing layer, copies a field set from
>> the source layer, then iterates through each of the features in the
>> source layer, creating a copy in the new layer. The problem is, if I
>> use it as-is with a shapefile as the source DS (for which OGR has
>> generated an FID), it will copy and create a new layer, but it will
>> only insert the first feature and then die with an error saying I need
>> to manually set the FID to -1. But I can't do that with CopyLayer(),
>> because CopyLayer() automatically pulls an FID into the destination
>> feature if one exists in the source DS in each CreateFeature iteration
>> (line 318).
>
> Yes, but that doesn't cause a problem for me when I test. Not sure why you get
> this error. If you create a new layer you should manage to create features
> with arbitrary FID. Can you set CPL_DEBUG=ON as a environement variable and
> retry ?
OK, here goes. Running with the following code:
from osgeo import ogr
from osgeo import gdal
gdal.SetConfigOption('CPL_DEBUG','ON')
def testLoad(serverDS, table, sourceFile):
ogr.RegisterAll()
shapeDS = ogr.Open(sourceFile)
sourceLayer = shapeDS.GetLayerByIndex(0)
options = []
name = serverDS.CopyLayer(sourceLayer,table,options).GetName()
return name
if __name__ == '__main__':
serverName = 'localhost'
database = 'database'
port = '5432'
usr = 'usr'
pw = 'pw'
table = 'temp_block'
connectionString = "PG:dbname='%s' host='%s' port='%s' user='%s'
password='%s'" % (database,serverName,port,usr,pw)
ogrds = ogr.Open(connectionString)
name = testLoad(ogrds,table,'../files/testBlock/tl_2010_01001_tabblock10.shp')
# ogrds.DeleteLayer(table)
pass
(The referenced shapefile is available here:
ftp://ftp2.census.gov/geo/tiger/TIGER2010/TABBLOCK/2010/tl_2010_01001_tabblock10.zip)
Here's what pops up in the console:
PG: DBName="'blockAssignment'"
PG: PostgreSQL version string : 'PostgreSQL 8.3.15, compiled by Visual
C++ build 1400'
PG: PostGIS version string : '1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
OGR: OGROpen(PG:dbname='database' host='localhost' port='5432'
user='usr' password='pw') succeeded as PostgreSQL.
OGR: OGROpen(../files/testBlock/tl_2010_01001_tabblock10.shp/00C76F80)
succeeded as ESRI Shapefile.
OGR_PG_NOTICE: NOTICE: CREATE TABLE will create implicit sequence
"temp_block_ogc_fid_seq" for serial column "temp_block.ogc_fid"
OGR_PG_NOTICE: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "temp_block_pk" for table "temp_block"
PG: Primary key name (FID): ogc_fid
PG: Using column 'ogc_fid' as FID for table 'temp_block'
PG: Layer 'temp_block' geometry type: POLYGON:Polygon, Dim=2
PG: LaunderName('STATEFP10') -> 'statefp10'
PG: LaunderName('COUNTYFP10') -> 'countyfp10'
PG: LaunderName('TRACTCE10') -> 'tractce10'
PG: LaunderName('BLOCKCE10') -> 'blockce10'
PG: LaunderName('GEOID10') -> 'geoid10'
PG: LaunderName('NAME10') -> 'name10'
PG: LaunderName('MTFCC10') -> 'mtfcc10'
PG: LaunderName('UR10') -> 'ur10'
PG: LaunderName('UACE10') -> 'uace10'
PG: LaunderName('FUNCSTAT10') -> 'funcstat10'
PG: LaunderName('ALAND10') -> 'aland10'
PG: LaunderName('AWATER10') -> 'awater10'
PG: LaunderName('INTPTLAT10') -> 'intptlat10'
PG: LaunderName('INTPTLON10') -> 'intptlon10'
Warning 1: Geometry to be inserted is of type Multi Polygon, whereas
the layer geometry type is Polygon.
Insertion is likely to fail
ERROR 1: INSERT command for new feature failed.
ERROR: new row for relation "temp_block" violates check constraint
"enforce_geotype_wkb_geometry"
Command: INSERT INTO "temp_block" ("wkb_geometry" , "ogc_fid" ,
"statefp10", "countyfp10", "tractce10", "blockce10", "geoid10",
"name10", "mtfcc10", "funcstat10", "aland10", "awater10",
"intptlat10", "intptlon10") VALUES
(GeomFromEWKT('SRID=4269;MULTIPOLYGON (((-86.91814
32.652694,-86.918165 32.652683,-86.918262 32.652689,-86.918301
32.652721,-86.918313 32.652814,-86.918228 32.653013,-86.918059
32.653526,-86.918009 32.653702,-86.917937 32.653723,-86.917782
32.653574,-86.917721 32.65343,-86.917723 32.653211,-86.91782
32.653091,-86.91791 32.652849,-86.918008 32.652755,-86.91814
32.652694)),((-86.911588 32.630683,-86.911684 32.630727,-86.91177
32.630947,-86.911788 32.631183,-86.911764 32.631332,-86.911615
32.631276,-86.911483 32.631127,-86.911164 32.630722,-86.911165
32.630612,-86.91123 32.63056,-86.911444 32.630616,-86.911588
32.630683)))'::TEXT) , 1231 , '01', '001', '021000', '1036',
'010010210001036', 'Block 1036', 'G5040', 'S', 6326,
0, '+32.6529996', '-086.9180901')
Warning 1: 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()
PG: SoftRollback() with no transaction active.
OGR: ReleaseDataSource(../files/testBlock/tl_2010_01001_tabblock10.shp/00C76F80)
on unshared datasource!
Deleting directly.
Shape: 1280 features read on layer 'tl_2010_01001_tabblock10'.
OGR: ReleaseDataSource(PG:dbname='database' host='localhost'
port='5432' user='usr' password='pw') on unshared datasource!
Deleting directly.
Now, what I am left with is an incomplete table -- 1887 input features
but only 1231 rows on the server. If I use this code instead for the
function and leave everything else:
def testLoad(serverDS, table, sourceFile):
ogr.RegisterAll()
shapeDS = ogr.Open(sourceFile)
sourceLayer = shapeDS.GetLayerByIndex(0)
options = []
newLayer = serverDS.CreateLayer(table,sourceLayer.GetSpatialRef(),ogr.wkbUnknown,options)
for x in xrange(sourceLayer.GetLayerDefn().GetFieldCount()):
newLayer.CreateField(sourceLayer.GetLayerDefn().GetFieldDefn(x))
newLayer.StartTransaction()
for x in xrange(sourceLayer.GetFeatureCount()):
newFeature = sourceLayer.GetNextFeature()
newFeature.SetFID(-1)
newLayer.CreateFeature(newFeature)
if x % 128 == 0:
newLayer.CommitTransaction()
newLayer.StartTransaction()
newLayer.CommitTransaction()
return newLayer.GetName()
I get the following at the console, and all 1887 features loaded on
the server successfully:
PG: DBName="'database'"
PG: PostgreSQL version string : 'PostgreSQL 8.3.15, compiled by Visual
C++ build 1400'
PG: PostGIS version string : '1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
OGR: OGROpen(PG:dbname='database' host='localhost' port='5432'
user='usr' password='pw') succeeded as PostgreSQL.
OGR: OGROpen(../files/testBlock/tl_2010_01001_tabblock10.shp/00BBFCF0)
succeeded as ESRI Shapefile.
OGR_PG_NOTICE: NOTICE: CREATE TABLE will create implicit sequence
"temp_block_ogc_fid_seq" for serial column "temp_block.ogc_fid"
OGR_PG_NOTICE: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "temp_block_pk" for table "temp_block"
PG: Primary key name (FID): ogc_fid
PG: Using column 'ogc_fid' as FID for table 'temp_block'
PG: Layer 'temp_block' geometry type: GEOMETRY:Unknown (any), Dim=2
PG: LaunderName('STATEFP10') -> 'statefp10'
PG: LaunderName('COUNTYFP10') -> 'countyfp10'
PG: LaunderName('TRACTCE10') -> 'tractce10'
PG: LaunderName('BLOCKCE10') -> 'blockce10'
PG: LaunderName('GEOID10') -> 'geoid10'
PG: LaunderName('NAME10') -> 'name10'
PG: LaunderName('MTFCC10') -> 'mtfcc10'
PG: LaunderName('UR10') -> 'ur10'
PG: LaunderName('UACE10') -> 'uace10'
PG: LaunderName('FUNCSTAT10') -> 'funcstat10'
PG: LaunderName('ALAND10') -> 'aland10'
PG: LaunderName('AWATER10') -> 'awater10'
PG: LaunderName('INTPTLAT10') -> 'intptlat10'
PG: LaunderName('INTPTLON10') -> 'intptlon10'
OGR: ReleaseDataSource(../files/testBlock/tl_2010_01001_tabblock10.shp/00BBFCF0)
on unshared datasource!
Deleting directly.
Shape: 1887 features read on layer 'tl_2010_01001_tabblock10'.
OGR: ReleaseDataSource(PG:dbname='database' host='localhost'
port='5432' user='usr' password='pw') on unshared datasource!
Deleting directly.
-----
Luke Peterson
More information about the gdal-dev
mailing list