Geospatial Functions Vertica St_GeomFromText

elghali

Hello,
I am having a problem with vertica Geospatial functions, precisely the ST_GeomFromText function (Vertica version 9.0.1),
I am just trying to run a select statement from a table column of type varchar
{code}
SELECT ST_GeomFromText(district_wkt) FROM custmap_district
{code}
and i'm getting this error
{code}
ERROR 3457: Function ST_GeomFromText(varchar) does not exist, or permission is denied for ST_GeomFromText(varchar)
HINT: No function matches the given name and argument types. You may need to add explicit type casts
{code}
I know the function takes in Long Varchar arguments so i tried to cast the column to Long Varchar, but the same error with Long Varchar instead of varchar in the error message,
I even altered the column data type to long varchar with no luck,
I even tried to run the example from the documentation
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Geospatial/ST_AsText.htm%3FTocPath%3DSQL%2520Reference%2520Manual%7CSQL%2520Functions%7CGeospatial%2520Functions%7C_____1

{code}
SELECT LENGTH(ST_AsText(St_GeomFromText('POLYGON ((-1 2, 0 3, 1 2,
0 1, -1 2))')));
{code}
also with no luck,
and i'm sure i have the right permissions as i'm logged in as dbadmin which is the owner of the function as per user_functions system table,

P.S: please note that this behavior is not always like this, sometimes it works for the same scenarios mentioned above and this is where the frustration is

Ariel_Cary

Hi elghali,

Geospatial functions are installed in the public schema. Can you check if public is in your search path?
show search_path;

The fact that sometimes you can run ST_GeomFromText makes me think that public may be absent from this path when your query fails. If this is the case, just restore public in your search path and you should be good to go.
set search_path to ...;

Regardless of the search path, you can try qualifying the function in your query, as in:
SELECT public.ST_GeomFromText(district_wkt) FROM custmap_district;

Ariel

elghali

Thank you very much @Ariel_Cary , this solved my problem, I really appreciate your help