Shapefile Foreign Table Shows with Incorrect SRID when Generated using IMPORT FOREIGN SCHEMA

[apjoseph]

Source File

Postgis Full Version "POSTGIS="2.3.0dev r15032" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.2.0dev, released 2016/99/99" LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY RASTER"

Server Def

DROP SERVER IF EXISTS shpfile CASCADE;
CREATE SERVER shpfile
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource '/tmp/community_registry',
    format 'ESRI Shapefile'
  );
  DROP SCHEMA IF EXISTS shpschema CASCADE;
  CREATE SCHEMA shpschema;

IMPORT FOREIGN SCHEMA community_registry FROM SERVER shpfile INTO shpschema;

Prj File

PROJCS["NAD_1983_StatePlane_Texas_Central_FIPS_4203_Feet",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],P
ARAMETER["False_Easting",2296583.333333333],PARAMETER["False_Northing",9842500.0],PARAMETER["Central_Meridian",-100.3333333333333],PARAMETER["Standard_Parallel_1",30.11666666666667],PARAMETER["Standard_Parallel_2",31.88333333333333],PARAMETER["Latitude_Of_Origin",2
9.66666666666667],UNIT["Foot_US",0.3048006096012192]]

The following query:

SELECT
  * 
FROM 
  postgis.geometry_columns 
WHERE
  f_table_schema = 'shpschema' and f_table_name = 'community_registry'

...will show the SRID as 4926 when it should show as 2277;

[pramsey]

Back in my day, FDW servers didn't magically try and figure out the SRID from the PRJ file, and we tied an onion to the belt, as was the style at the time.

Basically, there's some magic code in OGR we use to try to ascertain the SRID using the PRJ as an input, and sometimes it is wrong. Probably (?) you can

ALTER TABLE community_registry 
  ALTER COLUMN geom TYPE Geometry(MultiPolygon, 2277)

and manually force the SRID to be advertised the way you wish. Fixing the OGR SRID detection code is beyond the remit of this particular project :)