How to get Geography data to GeoJSON in PostgreSQL?

Question

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?

• HeyOverThere: 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?
• John Powell: Have you tried casting to geometry, eg, SELECT ST_As_GeoJSON(geog::geometry) from area

Answer 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!

Answer 2

I had a similar situation, where all PostGIS extensions were installed under the extensions schema, with my data tables residing under the public schema.

My original query:

SELECT ST_AsGeoJSON(geom)::jsonb as geometry FROM features

failed with this error message:

ERROR: function st_asgeojson(extensions.geometry) does not exist

In my case, the function st_asgeojson was not visible from the public schema, and it needed to be fully qualified (prefixed with the extensions schema name) for the query to work:

SELECT extensions.ST_AsGeoJSON(geom)::jsonb as geometry FROM features

Answer 3

Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. Answers without enough detail may be edited or deleted.

you can try this:

select st_asgeojson(st_astext(geog)) from area;

• John Powell: ST_ASGeoJSON takes either a geom or a geog but most definitely not a text string.