Problem with PostGIS ogc_fdw: unknown SRS

Question

I am trying to connect a WFS from within PostGIS using the ogr_fdw foreign data wrapper [https://github.com/pramsey/pgsql-ogr-fdw].

With most servers this works great! With one specific server I get the error:

EPSG PCS/GCS code 404000 not found in EPSG support files. Is this a valid EPSG coordinate system?

The SQL code is like this:

CREATE SERVER kvb_wfs FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource 'WFS:online-service.kvb-koeln.de/geoserver/ows', format 'WFS' ); I tracked it down and figured out, that the problem comes from the GeoServer which actually has this code defined as being a wildcard crs.

I tried to put the code in the gdal file (psc.csv) and the proj4 file (epsg). No success.

What am I doing wrong?

Answer 1

EPSG PCS/GCS code 404000 not found in EPSG support files. Is this a valid EPSG coordinate system?

No.

In general to check if a code is defined by EPSG you can use their official registry, but specifically in this case GIS.SE tag wiki gives a fuller answer ~ https://gis.stackexchange.com/tags/epsg-40400/info

Answer 2

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:

1. 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)!

2. 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');