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 ?
<!-- WARNING: No featureid defined for typename 'Geoparcs'. Output will not validate. -->