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
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
