[postgis-users] Postgis function does not exist in pgAdmin; Rails has no problems

[Jason Fleetwood-Boldt]

After dropping & recreating my Postgres database, I am seeing some very strange behavior in pgAdmin while debugging my Rails app. 

I'm getting this error:
function st_distance(postgis.geography, unknown) does not exist


Here's how I'm setting up my database

rake db:create
--> I now have 2 extensions plpgsql and postgis; also have two schemas postgis and public


rake db:migrate

--> I now have 3 extensions plpgsql,  postgis, hstore
--> Also I now have 50 tables (what I am expecting)



Then I restore my production dump to my local dev machine. At this point, everything looks ok when I examine it in pgAdmin, but I am unable to use any Postgis functions.

Now here's where it gets weird. When my query runs in Rails, it works fine (as expected). However, when i run it in pgAdmin, it fails, giving me this error:

ERROR:  function st_distance(postgis.geography, unknown) does not exist
LINE 1: SELECT DISTINCT places.*, ST_Distance("places"."coords", '00...
                                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


When this query runs inside of my Rails app it executes as expected -- which is behavior I've never seen from Rails before, so I must be missing something basic. 

Can anyone point in the right direction here, I'm really stuck trying to figure why Rails behaves differently than pgAdmin on the exact same database.



My full query is below, as you can see it is rather large:

SELECT DISTINCT places.*, ST_Distance("places"."coords", '0020000001000010e6c0527e94b7b289544044645492ff4ba5') AS distance, CASE WHEN (premium_listings.id IS NOT NULL AND (ST_Distance(places.coords, '0020000001000010e6c0527e94b7b289544044645492ff4ba5')*0.621371) < premium_listings.reach_radius_mi) THEN 1 ELSE 0 END as premium_listing, ur.rating AS user_rating, CASE WHEN fp.id IS NULL THEN 'false' ELSE 'true' END AS user_favorite, CASE WHEN featured_places.count > 0 THEN 'true' ELSE 'false' END AS featured FROM "places" LEFT OUTER JOIN premium_listing_places ON premium_listing_places.place_id = places.id LEFT OUTER JOIN premium_listings ON (premium_listings.id = premium_listing_places.premium_listing_id AND premium_listings.starts_at < NOW() AND premium_listings.ends_at > NOW()) LEFT OUTER JOIN ratings AS ur ON ur.rateable_id = places.id AND ur.rateable_type = 'Place' AND ur.user_id = -1 LEFT OUTER JOIN user_favorite_places AS fp ON fp.place_id = places.id AND fp.user_id = -1 LEFT OUTER JOIN (SELECT places.id AS id, COUNT(*) AS count FROM "promotions" INNER JOIN "place_promotions" ON "place_promotions"."promotion_id" = "promotions"."id" INNER JOIN "places" ON "places"."id" = "place_promotions"."place_id" WHERE "promotions"."featured" = 't' AND ((("promotions"."starts_at" <= '2014-06-10' AND "promotions"."ends_at" >= '2014-06-10') OR ("promotions"."starts_at" <= '2014-06-10' AND "promotions"."ends_at" IS NULL))) GROUP BY places.id) AS featured_places ON featured_places.id = places.id JOIN taggings place_taggings_e280fa7 ON place_taggings_e280fa7.taggable_id = places.id AND place_taggings_e280fa7.taggable_type = 'Place' WHERE "places"."status" = 'approved' AND (ST_DWithin("places"."coords", '0020000001000010e6c0527e94b7b289544044645492ff4ba5', 50000)) AND (place_taggings_e280fa7.tag_id = 80 OR place_taggings_e280fa7.tag_id = 102 OR place_taggings_e280fa7.tag_id = 8 OR place_taggings_e280fa7.tag_id = 76 OR place_taggings_e280fa7.tag_id = 260 OR place_taggings_e280fa7.tag_id = 261 OR place_taggings_e280fa7.tag_id = 217 OR place_taggings_e280fa7.tag_id = 226 OR place_taggings_e280fa7.tag_id = 258 OR place_taggings_e280fa7.tag_id = 286 OR place_taggings_e280fa7.tag_id = 155 OR place_taggings_e280fa7.tag_id = 262) GROUP BY places.id, premium_listings.id, ur.rating, fp.id, featured_places.count ORDER BY premium_listing DESC, distance LIMIT 50





For your reference, my database.yml file looks like this:


common: &common
  adapter: postgis
  host: localhost
  username: <%= ENV['USER'] %>
  encoding: unicode
  pool: 5
  timeout: 5000
  postgis_extension: postgis
  schema_search_path: public,postgis


development:
  <<: *common
  database: mn_development




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140610/ee91e6e6/attachment.html>