Touch function in Postgres with PostGis

Question

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

`AND N2 = 'eds_census2011'` looks supicious, too (you cannot compare a table(alias) to a string constant) — wildplasser, May 09 '13 at 20:38
Sorry I made a mistake there when typed, it shold be: Royal Exchange A. Thats not the error I was getting anyway — Andrei Ivanov, May 09 '13 at 21:18
Do you actually have a column named `N2`, just like the table alias? Then you should table-qualify it! Else this is still syntactical nonsense: `AND N2 = 'Royal Exchange A'` — Erwin Brandstetter, May 09 '13 at 21:19

score 2 · Accepted Answer · edited May 23 '17 at 10:32

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)

I made a few changes: `code`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'`code` — Andrei Ivanov, May 09 '13 at 21:36
but still get an error: ERROR: operator does not exist: boolean = integer LINE 3: WHERE ST_Touches(N1.the_geom, N2.the_geom)=1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ********** Error ********** ERROR: operator does not exist: boolean = integer SQL state: 42883 Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Character: 132 — Andrei Ivanov, May 09 '13 at 21:37

score 0 · Answer 2 · answered May 09 '13 at 21:40

0

Got it - thanks to everyone, used the following:

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

answered May 09 '13 at 21:40

Andrei Ivanov

311
2
8
19

Since my answer covers it, this one is redundant. A short comment would do. – Erwin Brandstetter May 09 '13 at 21:49

Touch function in Postgres with PostGis

2 Answers2

Error after Update