1

I am trying to find the way to download on our PostgreSQL+PostGIS database the content of several WFS at regular intervals. It has to be done at regular intervals so we want to the whole thing to be completely automated, especially since there will be over a hundred layers coming from a dozen different WFS. Some WFS layers are quite huge.

After finding out the existence of ogr2ogr (thanks to ThomasG77), it seems the weapon of choice for downloading a WFS layer straight into the database.

So I have been running tests with one of the WFS layers (the smallest one) : http://ws.carmencarto.fr/WFS/119/fxx_inpn?version=2.0.0&REQUEST=GetFeature&TYPENAME=ms:Geoparcs

Here is the bash code used for my tests. Basically, it uses ogr2ogr to download the WFS layer in several formats : GeoJSON, shapefiles, GPKG, GML and into the Postgresql+PostGIS database.

destination_host='localhost'
destination_port='5432'
destination_user='my_user'
destination_password='my_pwd'
destination_dbname='Tests'
destination_schema='tests_ogr2ogr'
destination_table='Geoparcs'
destination_SRS='EPSG:2154'

source_url='http://ws.carmencarto.fr/WFS/119/fxx_inpn'
source_wfs_version='2.0.0'
source_layer_name='ms:Geoparcs'
source_SRS='EPSG:3857'

destination="host='${destination_host}' port='${destination_port}' dbname='${destination_dbname}' user='${destination_user}' password='${destination_password}'"
source="WFS:${source_url}?version=${source_wfs_version}&REQUEST=GetFeature&TYPENAME=${source_layer_name}"

ogr2ogr -f "GPKG" -overwrite -progress "outputs/test_ogr2ogr-GPKG.gpkg" "${source}" 
ogr2ogr -f "GeoJSON" -overwrite -progress "outputs/test_ogr2ogr-GeoJSON.geojson" "${source}" 
ogr2ogr -f "ESRI Shapefile" -overwrite -progress  "outputs/test_ogr2ogr-SHP.shp" "${source}" 
ogr2ogr -f "GML" -overwrite -progress "outputs/test_ogr2ogr-GML.gml" "${source}" 
ogr2ogr -f "PostgreSQL" -overwrite -progress -s_srs "${source_SRS}" -t_srs "${destination_SRS}" PG:"${destination}" "${source}" -nln "${destination_schema}.${destination_table}"

Results : it works fine for shapefiles and GeoJSON but fails for GML, Postgresql and GPKG with basically the same error :

  • GPKG : "failed to execute insert : NOT NULL constraint failed: ms:Geoparcs.gml_id"
  • GML : "Field gml_id has a NULL content which is not allowed"
  • PostgreSQL : "null value in column "gml_id" violates not-null constraint"

From what I can read, in GML, there is a mandatory unique identifier field named gmal:id. I am not sure why GPKG and Postgresql also stumble on an error that should be a peculiarity of GML ... Would ogr2ogr when asked to ETL from WFS to GPKG/Postgresql use GML as an intermediate format ?

Now, when I have a look at http://ws.carmencarto.fr/WFS/119/fxx_inpn?version=2.0.0&REQUEST=GetFeature&TYPENAME=ms:Geoparcs using a browser, I see no attribute "gml:id=" in any tag that would be empty. Visualizing this WFS layer straight into QGIS does not show any gml_id attribute. I suppose because it is a system attribute of no interest in the GUI.

Also, using ogrinfo :

$ ogrinfo "WFS:http://ws.carmencarto.fr/WFS/119/fxx_inpn?version=2.0.0&REQUEST=GetFeature&TYPENAME=ms:Geoparcs" ms:Geoparcs -so
ERROR 1: Server is read-only WFS; no WFS-T feature advertized
Had to open data source read-only.
INFO: Open of `WFS:http://ws.carmencarto.fr/WFS/119/fxx_inpn?version=2.0.0&REQUEST=GetFeature&TYPENAME=ms:Geoparcs'
      using driver `WFS' successful.
Metadata:
  PROVIDER_NAME=Muséum national d Histoire naturelle
  TITLE=MNHN - INPN - Metropole

Layer name: ms:Geoparcs
Metadata:
  TITLE=Géoparcs
Geometry: Unknown (any)
Feature Count: 7
Extent: (577022.800000, 6287714.438800) - (997466.471300, 6596522.856900)
Layer SRS WKT:
PROJCS["RGF93 / Lambert-93",
    GEOGCS["RGF93",
        DATUM["Reseau_Geodesique_Francais_1993",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY["EPSG","6171"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AXIS["Latitude",NORTH],
        AXIS["Longitude",EAST],
        AUTHORITY["EPSG","4171"]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["standard_parallel_1",49],
    PARAMETER["standard_parallel_2",44],
    PARAMETER["latitude_of_origin",46.5],
    PARAMETER["central_meridian",3],
    PARAMETER["false_easting",700000],
    PARAMETER["false_northing",6600000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["X",EAST],
    AXIS["Y",NORTH],
    AUTHORITY["EPSG","2154"]]
Geometry Column = msGeometry
gml_id: String (0.0) NOT NULL
ID_LOCAL: String (0.0)
ID_MNHN: String (0.0)
P1_NATURE: String (0.0)
P2_CULTURE: String (0.0)
P3_PAYSAGE: String (0.0)
P4_GEOLOGI: String (0.0)
P5_SPELEO: String (0.0)
P6_ARCHEO: String (0.0)
P7_PALEOB: String (0.0)
P8_ANTHROP: String (0.0)
P9_SCIENCE: String (0.0)
P10_PUBLIC: String (0.0)
P11_DD: String (0.0)
P12_AUTRE: String (0.0)
NOM_SITE: String (0.0)
URL_FICHE: String (0.0)
SURF_OFF: String (0.0)
ACTE_FIN: String (0.0)
GEST_SITE: String (0.0)
OPERATEUR: String (0.0)
PRECISION: String (0.0)
SRC_GEOM: String (0.0)
SRC_ANNEE: String (0.0)
MARIN: String (0.0)
DATE_CREA: String (0.0)
MODIF_ADM: String (0.0)
MODIF_GEO: String (0.0)
ACTE_DEB: String (0.0)
NOM: String (0.0)
URL: String (0.0)

So, how do I get rid of this error and download this layer into my postgresql ?

10
  • Also, to see the capabilities of the server : ws.carmencarto.fr/WFS/119/… Apr 26, 2020 at 14:50
  • 1
    GML is the default output of a WFS which is why ogr2ogr is using it, the other errors come from constructing the tables to match the expected GML schema (that the WFS server told them) which says there is an unique id while you are trying to load NULL for all of them
    – Ian Turton
    Apr 26, 2020 at 16:03
  • 2
    Looks like the MapServer that is providing the WFS is misconfigured - it should specify one of gml_identifier, wfs_featureid or ows_featureid for each layer. See mapserver.org/id/ogc/wfs_server.html
    – Ian Turton
    Apr 26, 2020 at 16:08
  • 1
    The GML output shows even a very clear hint for the admins: <!-- WARNING: No featureid defined for typename 'Geoparcs'. Output will not validate. -->
    – user30184
    Apr 26, 2020 at 16:15
  • 1
    If you want to use this misconfigured server and find a workaround for saving the data directly into PostGIS without going through some other format that does not support NOT NULL constraint please edit the title and question for making it clear.
    – user30184
    Apr 26, 2020 at 18:33

1 Answer 1

1

Try to use ogr2ogr with -forceNullable option.

1
  • Had a similar problem trying to convert a GML document where features did not have GML IDs (which is perfectly valid). Adding the option as described here lead to the conversion (to Geopackage) succeeding
    – stempler
    Apr 5 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.