[postgis-users] FOREIGN TABLES and spatial_ref_sys

[Paul Ramsey]

You don't know what the problem *is* yet :)
You can "explain (analyze, verbose) select ..." to see the SQL being
sent over the wire.
You can also turn the statement logging on your foreign host to see
what SQL it being run over there.
That should at least clarify what's going wrong.
P

On Mon, Dec 18, 2017 at 12:04 AM, Martin Høgh <mhoegh at gmail.com> wrote:
> I'm using a postgres_fdw to wrap PostGIS tables in a remote server:
>
> CREATE SERVER gc2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
> '172.30.0.41', dbname 'dk', port '5432', extensions 'postgis'); (The last
> option is needed for performance)
>
> When running something like this (where foo.bar is the foreign table):
>
> SELECT
>   *
> FROM foo.bar
> WHERE ST_Distance(ST_Transform("the_geom", 3857),
> ST_GeomFromText('POINT(1082546.5790602104 7346842.908572207)', 3857)) < 23;
>
> I get this error: "spatial_ref_sys" does not exist
>
> I believe this happens because ST_Distance is not using the schema qualified
> name for "spatial_ref_sys". But again I thought all calls in PostGIS
> functions was schema qualified in version 2.3+?
>
> Is there a work-a-round for this problem?
>
> --
> Martin Høgh
> MapCentia ApS
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users