Search code examples
sqlfunctionpostgresqlpostgisspatial

Touch function in Postgres with PostGis

I have PostgreSQL installed with PostGis, I try to run the following:

SELECT N1.edname AS "Borders Royal Exchange A"
FROM eds_census2011 N1, eds_census2011 N2
WHERE Touch(N1.the_geom, N2.the_geom)
AND N2 = 'Royal Exchange A'

And I get an error (below) is there anything I have to add to Postgres or enable something?

ERROR:  function touch(geometry, geometry) does not exist
LINE 3: WHERE Touch(N1.the_geom, N2.the_geom)
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function touch(geometry, geometry) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 96
Solution

  • Run these two queries (in the same session you are trying the above query) to pin down the problem:

    In which schema does the function touch() live?

    SELECT p.proname, n.nspname
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  proname = 'touch';

    What is the current schema search_path of my role:

    SHOW search_path;

    If the function exists, the schema must be in the current search_path so that Postgres can find it. How to adapt the search_path?
    How does the search_path influence identifier resolution and the "current schema"

    BTW, I can't find a function Touch() in the function reference of the Postgis manual. There is one called ST_Touches(). Any chance you meant that one?

    Be aware that this query has a cost of O(N²), since it calculates a value for every combination of any two qualifying rows in eds_census2011. If your condition N2.edname = 'Royal Exchange A' is selective enough, this won't be a problem.

    Also, you may want to exclude rows joining themselves with an additional WHERE item like:

    AND N1.pk_id <> N2.pk_id

    Error after Update

    Your updated query makes more sense:

    SELECT N1.edname AS "Borders Royal Exchange A"
FROM   eds_census2011 N1, eds_census2011 N2
WHERE  ST_Touches(N1.the_geom, N2.the_geom)=1
AND    N2.edname = 'Royal Exchange A';

    But ST_Touches() returns boolean, so the Where clause should just be:

    WHERE  ST_Touches(N1.the_geom, N2.the_geom)