CODEWITHSUNDEEP
sqlruby-on-railspostgresqlpostgisrgeo
boulder_ruby
boulder_ruby

Reputation: 39695

Methods of overcoming "ActiveRecord::StatementInvalid: PG::AmbiguousFunction" in rails or sql. (st_intersects is not a unique function name)

When I run this command in rails:

CensusBlockGroup.where{st_interects(:the_geom, buffer)}

which runs this command in SQL:

SELECT "census_block_groups".* FROM "census_block_groups"  WHERE (st_intersects("census_block_groups"."proj_shape_3361", '002000000300000d2...'))

I'm getting this error:

ActiveRecord::StatementInvalid: PG::AmbiguousFunction: 
ERROR:  function st_intersects(geometry, unknown) is not unique
LINE 1: ...lock_groups".* FROM "census_block_groups"  WHERE (st_interse...
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

I don't know how to add explicit type casts and honestly don't clearly understand what that even means. I see the st_intersects function above is getting passed in a second parameter that is 'unknown'. Perhaps I could "cast" that and get it to work...?

I think the problem may be that there are multiple function definitions of st_intersects in postgresql due to its "function overloading" feature. I'm not the first developer to install postgresql/postgis on this system, a contractor of mine did, so I'm concerned we may have duplicated our efforts.

I'm running the rails server using postgresql & postgis on an Ubuntu "12.04 LTS" VPS.

I'm pretty sure I could just reinstall postgis but that would wipe out my contractor's code that does some pretty sophisticated stuff with pg_routing which I do not want to touch. If I have to I'll just boot up a fresh linode and run the code on it but it seems kind of wasteful and perhaps even more significantly like a kind of defeat.

If I'm unable to properly solve the problem by removing the ambiguity (this normally works for me on macs and linux distros), I think another solution would be to flag postgresql to ignore the ambiguity and just choose the first st_intersects function it comes across.

Other notes:

Similar questions (that are still leaving me scratching my head as a rails developer crippled by the ORM):

Upvotes: 1

Views: 355

Answers (1)

Felippe Rangel
Felippe Rangel

Reputation: 94

I never worked with Rails, but indeed, in PostgreSQL the function ST_Intersects has an override, which means that depending on the type of the arguments, a different function shall be called.

In this case, those are the overrides:

boolean ST_Intersects( geometry geomA , geometry geomB );

boolean ST_Intersects( geography geogA , geography geogB );

Based on the generated query displayed by you, PostgreSQL might be having a hard time identifying which function to run, adding an explicit cast (a way to specify the type of a certain value) will tell which function should be called.

To do that in PostgreSQL simple use the "::" after a value, as:

GEOMETRY:

SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);

So make sure both values :the_geom and buffer matches the geometry type and you should be fine. Maybe ActiveRecord has a way to explicit change types? Or you can write the query directly to PostgreSQL?

(source: http://postgis.net/docs/ST_Intersects.html)

Upvotes: 1

Related Questions