Postgis Plugin: ERROR: function st_srid(bytea) is not unique

[Qtianv587]

I tried to add a postgis layer and I filled the parameters like this:

connection: host=xxx.xxx.xxx.xxx port=5432 user=xxx password=xxx dbname=enc

table or subquery: rivers

Geometry field: wkb_geometry

Extent: Pre-calculate

SRS: WGS84

Other parameters remains blank.

And the error message is:

Postgis Plugin: ERROR:  function st_srid(bytea) is not unique
LINE 1: SELECT ST_SRID("wkb_geometry") AS srid FROM rivers WHERE "wk...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
in executeQuery Full sql was: 'SELECT ST_SRID("wkb_geometry") AS srid FROM rivers WHERE "wkb_geometry" IS NOT NULL LIMIT 1;'

I tried all ways to handle it but failed. Can someone solve this? @springmeyer

[csytsma]

Have you tried entering a SQL query instead of just the table name? Also, is your connection local or is it on a remote machine? I'm assuming the latter, because you have a host value. Have you verified the connection is good, and not causing problems?

[csytsma]

@Qtianv587 Were you able to solve this? Can you post back your solution, to help others?

[Qtianv587]

Well, I have solved this. Actually the problem is obvious.

ERROR: function st_srid(bytea) is not unique shows that there is not only one function called "st_srid()" in postgis database.

You might need to add explicit type casts shows that we need add explicit type casts to the parameters in function st_srid().

Sadly the SQL is embedded in TileMill and we can't edit it, so we need try other ways.

There are two ways to handle it and I recommend the second way:

1. Change the parameter "table or subquery". I filled a table name "river" before. Replace the table name "river" with a subquery: (select "wkb_geometry" :: geometry from river) as data

2. Delete or change other functions which have the same name called st_srid() in postgis database.

The first way helps us cast the field or parameter wkb_geometry to an explicit type geometry. But we can query only wkb_geometry data. If we need other fields' data, we must add fields in subquery like: (select "wkb_geometry" :: geometry, "ogc_fid" from river) as data If there is a large amount of fields, it's hard to edit the subquery. And when we need add postgis data in mapnik, the first way is inoperative. So I recommend the second way. I use the pgAdmin4 to change function's name manually.

[csytsma]

Thanks for posting an update with your resolution.