4

I am using PostgreSQL database and there is a field in a table(area), i.e., public.geography(Points,4326). It contains below type of records

"0101000020E6100000BA83D89942334540780B24287ED051C0"

I am using ST_AsGeoJson(), to get the GeoJSON through above types of records.

My database is connected and I am using a query to fetch result, i.e.,

$query="SELECT  ST_AsGeoJSON(geog) FROM area"; 

But I am getting below warning and not getting any results

Warning: pg_query(): Query failed: ERROR: function st_asgeojson(public.geography) does not exist LINE 1: SELECT ST_AsGeoJSON(geog) FROM public.area ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in /home/domain/public_html/sg_database.php on line 20

How can I solve it?

2
  • 1
    What version of PostGIS are you using? If you have a really old version (older than 1.5.0) St_AsGeoJSON doesn't support geography, only geometry. Next thing to check, is PostGIS and its functions installed into its own schema, and if so, is that schema in your search path? – HeyOverThere Jan 31 '15 at 15:14
  • 1
    Have you tried casting to geometry, eg, SELECT ST_As_GeoJSON(geog::geometry) from area; – John Powell Jan 31 '15 at 18:43
1

I was struggling with the same problem today. Finally got a solution that I am sharing here.

The root of the problem is search_path of postgres database.

sudo su postgres
psql database_name

the check out the search_path of the database

database_name=# show search_path

your schema that has the postgis extension should be in the search_path. To check which schema has the postgis extionsion use following command in postgres psql

database_name=# \dx

This will list all your extensions and corresponding schemas. To add the postgis schema to search_path, use following query.

database_name=# ALTER DATABASE database_name SET search_path TO schema_name,public,topology;
database_name=# ALTER ROLE database_user SET search_path to schema_name, public, topology;

This worked for me. Hope it helps. Cheers!

-2

you can try this:

select st_asgeojson(st_astext(geog)) from area;
1
  • 4
    ST_ASGeoJSON takes either a geom or a geog but most definitely not a text string. – John Powell Sep 25 '15 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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