Forgot to mention. Rails installs postgis in a schema called postgis. Looking at your errors, it looks like your backup had it in public (which is the way most people do it). If SELECT postgis_full_version(); doesn't work -- make sure to add postgis to your database search_path. As we have described here: http://www.postgresonline.com/journal/archives/279-Schema-and-search_path-su rprises.html And as I said -- you really want to use postgis_restore.pl to restore your data so it doesn't bring back 1.5 junk. If your data doesn't restore, you might have to move your postgis extension back to public during install and tehn you can move it back to postgis. This is easy with extensions: ALTER EXTENSION postgis SET SCHEMA public; --to move back after restore ALTER EXTENSION postgis SET SCHEMA postgis;
_____ From: Paragon Corporation [mailto:[email protected]] Sent: Tuesday, June 10, 2014 4:47 PM To: 'PostGIS Users Discussion' Subject: RE: [postgis-users] Postgis function does not exist in pgAdmin; Rails has no problems Jason, can you try SELECT postgis_full_version(); To see if you get anything. If rails works, then you probably have PostGIS installed but a newer version 2.0 or 2.1 and might have a mix of stuff from old 1.5. The best way to upgrade a 1.5 to 2.something is going hard upgrade: Basically filtering out all the 1.5 functions etc. from your backup as documented here: http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade hope that helps, Regina _____ From: [email protected] [mailto:[email protected]] On Behalf Of Jason Fleetwood-Boldt Sent: Tuesday, June 10, 2014 1:39 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Postgis function does not exist in pgAdmin; Rails has no problems I've narrowed down my problem (I think) but I really stuck on something here, any advice to point me in the right direction would be greatly appreciated. Whenever I run the restore of my database, I get these kinds of errors (hundreds and hundreds of them... this just one example) pg_restore: creating FUNCTION geography_out(geography) pg_restore: [archiver (db)] Error from TOC entry 894; 1255 17188 FUNCTION geography_out(geography) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not access file "$libdir/postgis-1.5": No such file or directory Command was: CREATE FUNCTION geography_out(geography) RETURNS cstring LANGUAGE c IMMUTABLE STRICT AS '$libdir/postgis-1.5', 'geog... pg_restore: [archiver (db)] could not execute query: ERROR: function public.geography_out(geography) does not exist Command was: ALTER FUNCTION public.geography_out(geography) OWNER TO postgres; So, it seems like something is not set up correctly with Postgis. I've tried droping & recreating my database (both from pgAdmin and also from rails rake tasks) multiple times, and followed the instructions here http://postgis.net/install/ but not matter what I do I always see these errors when I run restore. It seems like these functions aren't being created correctly, which makes sense to me, because then after I load up the database I am unable to use them in pgAdmin (except my rails app continues to work, completely counter-intuitively). Can anyone point me in the right direction? I've spent several hours googling for answers here and am completely stuck at this point. I am running Postgres 9.3.4.0 -Jason On Jun 10, 2014, at 11:39 AM, Jason Fleetwood-Boldt <[email protected]> wrote: 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
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
