I try to summarize the solutions I found, perhaps they are of interest to others:
The ogr_fdw extension for PostGIS is a really great thing when you like to integrate data from foreign servers into your PostGIS infrastructure, e.g. for further analysis with local data in your DB.
After experimenting with it I found some problems and solutions:
If you want to reference data from a foreign WFS server, that reports an unknown EPSG code, it fails with this message:
EPSG PCS/GCS code 404000 not found in EPSG support files. Is this a valid EPSG coordinate system?
This happens even if none of the layers that you like to integrate is actually using this code. In my case it was a foreign GeoServer implementation with the above code as a wildcard in its list of available spatial reference system codes. So you have no control over what a foreign WFS server might respond.
The solution is to modify the list of EPSG-codes in the GDAL installations which ogr_fdw is using. In my case this was the file located in /usr/share/gdal/2.1/pcs.csv. I put in a fake line for the erroneous code - and it worked, but only after restarting the postgres service (I forgot to do that in the beginning)!
- I also tried to access ArcGIS server based MapServices and Feature services. First I failed, because of problems with naming etc. I could create the foreign server with the following structure:
CREATE SERVER test_server FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource 'https://somewhere/arcgis/rest/services/somefolder/somepath/MapServer/1/query?where=objectid+%3D+objectid&outfields=*&f=json', format 'GeoJSON' );
However, I had problems creating tables. The issue was that the layer name that is given by the ESRI-service does not work. ogrinfo came to the rescue. By issuing the following command:
ogrinfo -ro -al "https://somewhere/arcgis/rest/services/somefolder/somepath/MapServer/1/query?where=objectid+%3D+objectid&outfields=*&f=json
you will get all the details, e.g. :
Layer name: OGRGeoJSON
Geometry: Line String
...
...
AUTHORITY["EPSG","102100"]
FID Column = OBJECTID_1
OBJECTID_1: Integer (0.0)
OBJECTID: Integer (0.0)
STRAB_K_ID: Real (0.0)
....
....
The information contains again a fake EPSG-code (102100), actually being used by ESRI but basically it is EPSG:3857. You can just duplicate the 3857 line in the gdal file pcs.csv It also contains - important - the Layername, which is always OGRGeoJSON.
You can then create the table like this:
CREATE FOREIGN TABLE my_schema.myforeigntable (
id_bus_stop INTEGER OPTIONS (column_name 'OBJECTID') ,
esri_fid text OPTIONS (column_name 'fid_1'),
ass text,
wkb_geometry Geometry(MULTILINESTRING,3857)
) SERVER test_server
OPTIONS (layer 'OGRGeoJSON');