2

I run command:

ogr2ogr -f PostgreSQL PG:"dbname=regional host=localhost user=*** password=***" Hsp_09_13.MIF -t_srs EPSG:3857 -nlt MULTIPOLYGON -overwrite -nln postcode_sector_boundaries

And I have error:

ERROR 1: Layer postcode_sector_boundaries already exists, CreateLayer failed. Use the layer creation option OVERWRITE=YES to replace it.

So I run this command then:

ogr2ogr -f PostgreSQL PG:"dbname=regional host=localhost user=*** password=***" Hsp_09_13.MIF -t_srs EPSG:3857 -nlt MULTIPOLYGON -lco OVERWRITE=YES -overwrite -nln postcode_sector_boundaries

and got error:

ERROR 1: CREATE TABLE "public"."postcode_sector_boundaries" ( OGC_FID SERIAL, PRIMARY KEY (OGC_FID) ) ERROR: relation "postcode_sector_boundaries" already exists

So I remove table postcode_sector_boundaries and run once more last command and got:

ERROR 1: 'CREATE INDEX "postcode_sector_boundaries_geom_idx" ON "public"."postcode_sector_boundaries" USING GIST ("wkb_geometry")' failed for layer postcode_sector_boundaries, index creation has failed.

postcode_sector_boundaries_geom_idx is unique key so I think that maybe my data are broken. I would like that ogr2ogr create table without that index but I can not find where definition for this create query can be. Any one knows? Or maybe there is another solution for my problem?

edit:

Ok, I know what was the problem - it was because there were some other tables using values from one I wanted recreate.

nospor
  • 141
  • 1
  • 7
  • So, you are trying to update/overwrite an existing PostGIS table using ogr? – artwork21 May 31 '16 at 13:18
  • Yes. It works for other tables with other data but not for this table. – nospor May 31 '16 at 13:21
  • I've always used the -update and -overwrite switches like: ogr2ogr -update -overwrite -f PostgreSQL PG:"dbname=regional host=localhost user=*** password=***" Hsp_09_13.MIF -t_srs EPSG:3857 -nlt MULTIPOLYGON -nln postcode_sector_boundaries – artwork21 May 31 '16 at 13:23
  • Adding -update does not help. I still have ERROR 1: Layer postcode_sector_boundaries already exists, CreateLayer failed. – nospor May 31 '16 at 13:25
  • You have both the ogr2ogr option -overwrite and driver layer creation option -lco overwrite=yes. Try to remove the ogr2ogr overwrite. – user30184 May 31 '16 at 13:47
  • It does not help @user30184 – nospor May 31 '16 at 13:53
  • Ok, I know what was the problem - it was because there were some other tables using values from one I wanted recreate. – nospor May 31 '16 at 13:58
  • Did you had some views or triggers? – user30184 May 31 '16 at 15:18
  • Yes. As I mentioned before it was because of some other sources which were using this table. – nospor May 31 '16 at 15:41
  • 1
    @nospor This is the reason I use the "append" and "truncate" method, to avoid problems with depending views. I don't know why it does nothing in your case. – thibautg May 31 '16 at 17:17
  • @thibautg I checked/run your code few times to make sure and each time no data were updated. Maybe some specific case – nospor May 31 '16 at 20:57
  • Fortunately you found another solution. Maybe there is an error in my command. Here's a similar question: http://gis.stackexchange.com/questions/188078/use-ogr2ogr-to-delete-content-of-table-and-load-new-data-from-shapefile – thibautg May 31 '16 at 21:15

1 Answers1

2

I've had some success with -append (to avoid recreating the table) and --config OGR_TRUNCATE YES (to truncate/empty the table before uploading new data)

ogr2ogr -append -f PostgreSQL PG:"dbname=regional host=localhost user=*** password=***" Hsp_09_13.MIF -t_srs EPSG:3857 -nlt MULTIPOLYGON -nln postcode_sector_boundaries --config OGR_TRUNCATE YES

(not tested with the above command but I use it regularly with other layers)

thibautg
  • 2,097
  • 10
  • 16