[postgis-users] FOREIGN TABLES and spatial_ref_sys

[Regina Obe]

Oops that's a new one

This one is the one you are using:

ALTER FUNCTION st_transform(geometry, integer)
  SET search_path=public,postgis;


-----Original Message-----
From: Regina Obe [mailto:lr at pcorp.us] 
Sent: Tuesday, December 19, 2017 3:43 AM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: RE: [postgis-users] FOREIGN TABLES and spatial_ref_sys

> 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+?

Except for ST_Transform and I think a couple others in the transform family (or stuff I simply forgot to schema qualify). 
ST_Transform couldn't schema qualify.  See the code that calls spatial_ref_sys is actually in the C code, so I couldn't schema qualify it because we don't know where you will install postgis at compile time.

>
> Is there a work-a-round for this problem?
The work around would be to put in set search_path on the ST_Transform function definition.

--something like
ALTER FUNCTION st_transform(geometry, text, text)
  SET search_path=public,postgis;

I purposely didn't do this because under certain circumstances it slows queries down considerably and would depend on what you mix ST_Transform with.


Thanks,
Regina