Functions in Prepared Statements

[ejc123]

I've been trying to use geom.asGeoJson() in a query that gets turned into a prepared statement. The problem is that I get

ERROR: function st_asgeojson(bytea, integer) is not unique at character 31
HINT: Could not choose a best candidate function. You might need to add explicit type casts.

in my Postgres logs.

Here's what Postgres is trying to do:

STATEMENT: select x2."name", x2."state", ST_AsGeoJson(ST_AsEWKB(x2."geom"),1), x2."country", x3."email", x4."term", x5."career" from "term" x4, "career" x5, "cities" x2, "students" x3 where (((x4."term" = $1) and (x5."career" = $2)) and (x2."id" = $3)) and (((x3."city" = x2."id") and (x3."term" = x4."id")) and (x3."career" = x5."id"))

The query works wonderfully if it's not in a prepared statement.

[ahinz]

Ah, it looks like PostGIS helpfully defines two 2-input ST_AsGeoJson functions:

CREATE OR REPLACE FUNCTION ST_AsGeoJson(int4, geometry)

and

CREATE OR REPLACE FUNCTION ST_AsGeoJson(geometry, int4)

So, as the actually helpful hint mentions, all we should need to do it typecast "integer" to a int4. Unfortunately, this is going to happen for pretty much all of the PostGIS functions. I'm not sure if there is an easy way to add type information to function arguments.

I'll try to tackle this over the weekend.

[ejc123]

Actually it's because of these two:

text ST_AsGeoJSON(geometry geom, integer maxdecimaldigits=15, integer options=0);

text ST_AsGeoJSON(geography geog, integer maxdecimaldigits=15, integer options=0);

and the fact that postgres can't tell if we have geom or geog when GeoSlick returns the geometry column as bytea ala ST_AsEWKB()

As an experiment I tried this directly from psql:
gis=# SELECT ST_AsGeoJSON('LINESTRING(1 2 3, 4 5 6)');
st_asgeojson

---

{"type":"LineString","coordinates":[[1,2,3],[4,5,6]]}
(1 row)

gis=# SELECT ST_AsGeoJSON(ST_AsEWKB('LINESTRING(1 2 3, 4 5 6)'));
ERROR: function st_asgeojson(bytea) is not unique
LINE 1: SELECT ST_AsGeoJSON(ST_AsEWKB('LINESTRING(1 2 3, 4 5 6)'));
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.

As expected, this works:

gis=# SELECT ST_AsGeoJSON(ST_AsEWKB('LINESTRING(1 2 3, 4 5 6)')::GEOMETRY);
st_asgeojson

---

{"type":"LineString","coordinates":[[1,2,3],[4,5,6]]}
(1 row)

I tried to add the cast to GEOMETRY in GeoSlick, but I could not figure out how to do it. As an experiment I commented out the call to wrapEWKB in postgis.scala and it works for my purposes -- not sure what it breaks. Any hints on casting would be appreciated. I think it should be possible.