[GIS] PostGIS ST within does not exist

postgispostgresqlspatial-query

I'm quite new to postgresql and postgis so this error will probably due to some rookie mistake.

I want to run the spatial query ST_within. When I describe the query like this

SELECT nw.entity_id AS NavElem_id, nw.featuretype, nw.processingstatus AS NavElem_ProcessingStatus, nw.frc, nw.geometry AS NavElem_geom, aaa.processingstatus AS AA_processingstatus, aaa.networktype, aaa.geometry AS AA_geom, aaa.entity_id AS AA_id
FROM sml.network nw, sml.aa_geo_att aaa
WHERE ST_Within (nw.geometry, aaa.geometry);

It works fine, however I do not want to provide the schema name everytime (in this case sml.), I want to make it generic so that I can use it in an sql file.

When I try to set the schema path in a first query and the ST_within in second query

SET search_path TO sml;
SELECT nw.entity_id AS NavElem_id, nw.featuretype, nw.processingstatus AS NavElem_ProcessingStatus, nw.frc, nw.geometry AS NavElem_geom, aaa.processingstatus AS AA_processingstatus, aaa.networktype, aaa.geometry AS AA_geom, aaa.entity_id AS AA_id
FROM network nw, aa_geo_att aaa
WHERE ST_Within (nw.geometry, aaa.geometry);

I get the following error:

ERROR:  function st_within(public.geometry, public.geometry) does not exist
LINE 5: WHERE ST_Within (nw.geometry, aaa.geometry);
          ^
HINT:  No function matches the given name and argument types. You might need to add   explicit type casts.
********** Error **********

ERROR: function st_within(public.geometry, public.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: 311

Even though I set the schema, ST within is looking in the public schema, which I don't want.

Best Answer

Your problem is that you have excluded the public schema from the search_path. All functions and types is in the public schema (by default)

So what you have to do is just include the public schema in the search_path

SET search_path TO sml, public;

means that the database will write to sml if you don't give any schema

SET search_path TO public, sml;

means that the database will write to public if no schema is given.

But in both cases both schemas will be searched for tables, functions and so on

Related Question