ERROR using ST_AsGeoJSON with PostGIS

Question

When I try to run the following command:

SELECT ST_AsGeoJSON(the_geom)

I get the following Error message:

HINT: Could not choose a best candidate function. You might need to add explicit type casts.

I got this Error message after I made a Backup of my DB and then restored it on a locally hosted new one. Any suggestions on how to solve this problem? I browsed the web and found out that a possible solution would be to run the "postgis_upgrade_20_minor.sql file". (https://groups.google.com/forum/#!topic/postgis-users/qrk4FD-k5H8) But I have no clue what that is and where I can find this file (since I am very new to SQL and PostGIS).

Answer 1

The problem is that due to the restore you now have ambiguous functions called "ST_AsGeoJSON". You can run the following query to check:

select * from pg_proc where proname = 'st_asgeojson'

In a clean install, that should return you five rows. Bet you get more...

The solution is indeed to run the "postgis_upgrade_20_minor.sql" file. That is a file containing a list of SQL commands which cleans up things like the errant st_asGeoJSON functions. On Windows it's in the "share/contrib/postgis-[version]" directory under your postgresql installation. In my case, the full path is "C:\Program Files\PostgreSQL\9.3\share\contrib\postgis-2.1\postgis_upgrade_21_minor.sql" - your path might be different depending on your OS and the Postgresql and Postgis version you're running.

If you're using PGAdmin, you can connect to your database and open an SQL window. Open that sql file and you can either run the whole thing, or scroll to the bottom, highlight the commands beginning "DROP FUNCTION IF EXISTS ST_AsGeoJson" and just run those.

Execute "select * from pg_proc where proname = 'st_asgeojson'" again, to see if it's made a difference.

Good luck!

[EDIT] Here are the SQL commands to drop the functions, copied from the .sql file if you have difficulty finding it.

DROP FUNCTION IF EXISTS ST_AsGeoJson(geometry); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(geography); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(geometry,int4); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(geography,int4); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geometry); -- this one changed to use default args
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geography); -- this one changed to use default args
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geometry,int4); -- this one changed to use default args
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geography,int4); -- this one changed to use default args