3

It seems that I have a variant of ST_Distance function in my PostGIS (3.2.1) that is not on the documentation

CREATE OR REPLACE FUNCTION public.st_distance(text, text)                                     
  RETURNS double precision                                                                     
  LANGUAGE sql                                                                                 
  IMMUTABLE PARALLEL SAFE STRICT                                                               
 AS $function$ SELECT public.ST_Distance($1::public.geometry, $2::public.geometry);  $function$
 ;

So, \df returns:

=> \df st_distance
                                                  List of functions
 Schema |    Name     | Result data type |                         Argument data types                         | Type 
--------+-------------+------------------+---------------------------------------------------------------------+------
 public | st_distance | double precision | geog1 geography, geog2 geography, use_spheroid boolean DEFAULT true | func
 public | st_distance | double precision | geom1 geometry, geom2 geometry                                      | func
 public | st_distance | double precision | text, text                                                          | func
(3 rows)

I don't remember defining that function (the third variant) myself.

By comparison, ST_Hausdorffdistance does not have the text version.

=> \df st_hausdorffdistance
                                             List of functions
 Schema |         Name         | Result data type |               Argument data types                | Type 
--------+----------------------+------------------+--------------------------------------------------+------
 public | st_hausdorffdistance | double precision | geom1 geometry, geom2 geometry                   | func
 public | st_hausdorffdistance | double precision | geom1 geometry, geom2 geometry, double precision | func
(2 rows)

Could anyone confirm whether if it's defined by PostGIS and what purpose does it serve?

-- Clarification --

The reason I am asking is that I have defined a type that automatically converts to both the geometry and text types. And the fact that ST_Distance accepts both types seems to be causing an ambiguity, although it was intended in 1.5 to avoid ambiguity per the source code in the answer.

1
  • 1
    Just to add context: PostgreSQL has a sequenced try-and-error best-or-first methodology to identify an appropriate function signature for given arguments. Without that 'hack', or the newer implicit CAST(TEXT AS GEOMETRY), any unknown arguments will raise an ambiguity error. All functions with a signature for GEOGRAPHY that were present in PostGIS < 2.0 have these additional signatures!
    – geozelot
    Commented May 3, 2022 at 8:48

2 Answers 2

7

The function ST_Distance(text, text) is defined by PostGIS. Here's the source code:

-- Availability: 1.5.0 - this is just a hack to prevent unknown from causing ambiguous name because of geography
CREATE OR REPLACE FUNCTION ST_Distance(text, text)
    RETURNS float8 AS
    $$ SELECT @[email protected]_Distance($1::@[email protected], $2::@[email protected]);  $$
    LANGUAGE 'sql' IMMUTABLE STRICT PARALLEL SAFE;

This may no longer be needed, now that text automatically casts to geometry.

2

Very short, but you should be able to understand it:

postgres=# SELECT ST_Distance('POINT(0 0)','POINT(1 1)');
    st_distance     
--------------------
 1.4142135623730951
(1 row)

or more explicitly:

postgres=# SELECT ST_Distance('POINT(0 0)'::text,'POINT(1 1)'::text);
    st_distance     
--------------------
 1.4142135623730951
(1 row)

vs

postgres=# SELECT ST_Distance(ST_Point(0,0), ST_Point(1,1));
    st_distance     
--------------------
 1.4142135623730951
(1 row)

Please note that:

postgres=# SELECT pg_typeof('Point(0 0)'::text);
 pg_typeof 
-----------
 text
(1 row)

and:

postgres=# SELECT pg_typeof('Point(0 0)'::geometry);
 pg_typeof 
-----------
 geometry
(1 row)

See "Table 9.70. System Catalog Information Functions" here: https://www.postgresql.org/docs/14/functions-info.html fore more information on the pg_typeof() function, which, at the time of writing, tells:

pg_typeof ( "any" ) → regtype

Returns the OID of the data type of the value that is passed to it. This can be helpful for troubleshooting or dynamically constructing SQL queries. The function is declared as returning regtype, which is an OID alias type (see Section 8.19); this means that it is the same as an OID for comparison purposes but displays as a type name.

For example:

SELECT pg_typeof(33); pg_typeof
-----------
integer

SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); typlen
--------
4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.