Opened 3 months ago

Closed 3 months ago

Last modified 3 months ago

#7217 closed defect (fixed)

ogr2ogr -addfields option disrespects -select clause

Reported by: plesk Owned by: warmerdam
Priority: normal Milestone: 2.2.4
Component: Utilities Version: 2.2.1
Severity: normal Keywords:
Cc:

Description

Using the -addfields flag when using ogr2ogr seems to be disrespecting the -select flag when operating with the postgres driver (unsure of others).

ogr2ogr -f postgres "PG:host=####### port=5432 user='#####' password='#######' dbname='#####'" WFS:http://services.land.vic.gov.au/catalogue/publicproxy/guest/dv_geoserver/datavic/wfs?version=1.0.0 datavic:CULTURE_SENSITIVITY_PUBLIC -t_srs EPSG:3111 -select ogc_fid,objectid -spat 141 -37 143 -35  -append -addfields -nln datavic.cultural_sensitivity -nlt promote_to_multi

Running the above generates the error below:

ERROR 1: ALTER TABLE "datavic"."cultural_sensitivity" ADD COLUMN "gml_id" VARCHAR NOT NULL
ERROR:  column "gml_id" contains null values

I would have expected the gml_id column not to be added as it is not specified in the -select flag.

Change History (8)

comment:1 Changed 3 months ago by plesk

I see a problem here, typically using -addfields would not be used with a -select flag. So this may not be a issue. In any case, it appears -addfields is overriding the -select and may just be a documentation thing. We come across this in our scripting environment when we will occasionally modify the -select clause when our data providers change their schema.

comment:2 Changed 3 months ago by Jukka Rahkonen

For most use cases it is obvious that keeping FIDs is a good thing. Would it make any difference to add -unsetfid? Probably not because you have -append in the command but have a try anyway.

Or perhaps by using -sql instead of -select?

-dialect sqlite -sql "select ogc_fid, objectid, geometry FROM datavic:CULTURE_SENSITIVITY_PUBLIC"

By the way, how have you defined the primary key into your "datavic.cultural_sensitivity" table? How do you control that you do not add the same features as duplicates?

Last edited 3 months ago by Jukka Rahkonen (previous) (diff)

comment:3 Changed 3 months ago by plesk

The data provider does not guarantee that FIDs will persist. They use other columns for most of their datasets as PK, so using ogc_fid can be dangerous. In order to update datasets and avoid duplicates, ogr_truncate is used to clear the tables prior to update. -append is used as the table structure must remain to maintain views. -addfields has been added as schema changes happen and the provider can change or add important fields, and changing either the sql or select flag is one way to easily manager updates.

I probably should not have used the not-null error posted above for this example. I also got feedback informing me that ogr was attempting to create the SENSITIVITY column, which is a nullable field but was specified in in the select statement. I will try -sql tomorrow. Note that you cannot use datavic:CULTURE_SENSITIVITY_PUBLIC as the table name, the colon causes a syntax error.

ERROR 1: In ExecuteSQL(): sqlite3_prepare(select ogc_fid, objectid, geometry FROM datavic:CULTURE_SENSITIVITY_PUBLIC):
  near ":CULTURE_SENSITIVITY_PUBLIC": syntax error

I've had trouble with -sql on wfs in the past. Ordinarily its my goto selector for all other formats.

I suppose the reason to report this was if nothing else, for a clarification in the documentation as to how the -addfields behaves with other options.

comment:4 Changed 3 months ago by Jukka Rahkonen

Note, that the data from that WFS does not contain attribute "ogc_fid" so I hope that it is not important for you.

Name with colon may be usable as quoted. This works on Windows. And I know it is not valid SQL, it should really be between double quotes "colon:test" but the correct syntax does not work while the wrong one does.

ogrinfo -dialect sqlite -sql " select * from 'colon:test'" testi.sqlite

comment:5 Changed 3 months ago by plesk

The ogc_fid is not important in this case. -sql works but -select fails.

The following works the first time (when no dest exists) but on the second run disregards the -select statement, and adds in all of the source columns.

ogr2ogr -f postgres "PG:####" WFS:http://services.land.vic.gov.au/catalogue/publicproxy
/guest/dv_geoserver/datavic/wfs?version=1.0.0 datavic:CULTURE_SENSITIVITY_PUBLIC
 -t_srs EPSG:3111 -select objectid -spat 141 -37 143 -35  -append -addfields -nl
n public.test2 -nlt promote_to_multi

The following works, using -sql

ogr2ogr -f postgres "PG:####" WFS:http://services.land.vic.gov.au/catalogue/publicproxy
/guest/dv_geoserver/datavic/wfs?version=1.0.0 datavic:CULTURE_SENSITIVITY_PUBLIC
 -t_srs EPSG:3111 -sql "select objectid from ""datavic:CULTURE_SENSITIVITY_PUBLIC""" -spat 141 -37 143 -35  -append -addfields -nl
n public.test2 -nlt promote_to_multi

comment:6 Changed 3 months ago by Even Rouault

Resolution: fixed
Status: newclosed

In 41372:

ogr2ogr: honour -select when using -addfiels; add addFields and forceNullable options to Python gdal.VectorTranslate?(); fix converting from Memory to Memory datasources (fixes #7217)

comment:7 Changed 3 months ago by Even Rouault

In 41373:

ogr2ogr: honour -select when using -addfiels; add addFields and forceNullable options to Python gdal.VectorTranslate?(); fix converting from Memory to Memory datasources (fixes #7217)

comment:8 Changed 3 months ago by Even Rouault

Component: defaultUtilities
Milestone: 2.2.4
Note: See TracTickets for help on using tickets.