<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 10.00.9200.16899"></HEAD>
<BODY
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space">
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>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).</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT><FONT color=#0000ff size=2
face=Arial><SPAN class=022564720-10062014>If </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>SELECT postgis_full_version();</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>doesn't work -- make sure to add postgis to your
database search_path. As we have described here:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014><A
href="http://www.postgresonline.com/journal/archives/279-Schema-and-search_path-surprises.html">http://www.postgresonline.com/journal/archives/279-Schema-and-search_path-surprises.html</A></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>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.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>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.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>This is easy with extensions:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>ALTER EXTENSION postgis SET SCHEMA
public;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>--to move back after restore</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014>ALTER EXTENSION postgis SET SCHEMA
postgis;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=022564720-10062014></SPAN></FONT> </DIV><BR>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> Paragon Corporation [mailto:lr@pcorp.us]
<BR><B>Sent:</B> Tuesday, June 10, 2014 4:47 PM<BR><B>To:</B> 'PostGIS Users
Discussion'<BR><B>Subject:</B> RE: [postgis-users] Postgis function does not
exist in pgAdmin; Rails has no problems<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial>Jason,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial>can you try </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial>SELECT postgis_full_version();</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=036253920-10062014>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:</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=036253920-10062014></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=036253920-10062014><A
href="http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade">http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade</A></SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=036253920-10062014></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=036253920-10062014></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial>hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036253920-10062014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Jason
Fleetwood-Boldt<BR><B>Sent:</B> Tuesday, June 10, 2014 1:39 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Postgis function
does not exist in pgAdmin; Rails has no problems<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>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. </DIV>
<DIV><BR></DIV>
<DIV>Whenever I run the restore of my database, I get these kinds of errors
(hundreds and hundreds of them... this just one example)</DIV>
<DIV>
<DIV
style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px; MIN-HEIGHT: 16px"><BR></DIV>
<DIV
style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">pg_restore:
creating FUNCTION geography_out(geography)</DIV>
<DIV
style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">pg_restore:
[archiver (db)] Error from TOC entry 894; 1255 17188 FUNCTION
geography_out(geography) postgres</DIV>
<DIV
style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">pg_restore:
[archiver (db)] could not execute query: ERROR: could not access file
"$libdir/postgis-1.5": No such file or directory</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">
Command was: CREATE FUNCTION geography_out(geography) RETURNS
cstring</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">
LANGUAGE c IMMUTABLE STRICT</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">
AS '$libdir/postgis-1.5', 'geog...</DIV>
<DIV
style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">pg_restore:
[archiver (db)] could not execute query: ERROR: function
public.geography_out(geography) does not exist</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: 'Lucida Grande'; MARGIN: 0px">
Command was: ALTER FUNCTION public.geography_out(geography) OWNER TO
postgres;</DIV></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>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 <A
href="http://postgis.net/install/">http://postgis.net/install/</A> but not
matter what I do I always see these errors when I run restore. </DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>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).</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>Can anyone point me in the right direction? I've spent several hours
googling for answers here and am completely stuck at this point. </DIV>
<DIV><BR></DIV>
<DIV>I am running Postgres 9.3.4.0</DIV>
<DIV><BR></DIV>
<DIV>-Jason</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV><BR>
<DIV>
<DIV>On Jun 10, 2014, at 11:39 AM, Jason Fleetwood-Boldt <<A
href="mailto:tech@datatravels.com">tech@datatravels.com</A>> wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<DIV
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space">
<DIV><BR></DIV>
<DIV>After dropping & recreating my Postgres database, I am seeing some
very strange behavior in pgAdmin while debugging my Rails app. </DIV>
<DIV><BR></DIV>
<DIV>I'm getting this error:</DIV>
<DIV><SPAN style="FONT-SIZE: 12px; FONT-FAMILY: Monaco">function
st_distance(postgis.geography, unknown) does not exist</SPAN></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>Here's how I'm setting up my database</DIV>
<DIV><BR></DIV>
<DIV>rake db:create</DIV>
<DIV>--> I now have 2 extensions <B>plpgsql</B> and <B>postgis</B>; also
have two schemas <B>postgis</B> and <B>public</B></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>rake db:migrate</DIV>
<DIV><BR></DIV>
<DIV>--> I now have 3
extensions <B>plpgsql, </B> <B>postgis, hstore</B></DIV>
<DIV>--> Also I now have 50 tables (what I am expecting)</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>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:</DIV>
<DIV><SPAN style="FONT-SIZE: 12px; FONT-FAMILY: Monaco"><BR></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 12px; FONT-FAMILY: Monaco">ERROR:</SPAN><SPAN
style="FONT-SIZE: 12px; FONT-FAMILY: Monaco"> </SPAN><SPAN
style="FONT-SIZE: 12px; FONT-FAMILY: Monaco">function
st_distance(postgis.geography, unknown) does not exist</SPAN></DIV>
<DIV style="FONT-SIZE: 12px; FONT-FAMILY: Monaco; MARGIN: 0px">LINE 1: SELECT
DISTINCT places.*, ST_Distance("places"."coords", '00...</DIV>
<DIV style="FONT-SIZE: 12px; FONT-FAMILY: Monaco; MARGIN: 0px">
^</DIV>
<DIV style="FONT-SIZE: 12px; FONT-FAMILY: Monaco; MARGIN: 0px">HINT: No
function matches the given name and argument types. You might need to add
explicit type casts.</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><B>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. </B></DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>My full query is below, as you can see it is rather large:</DIV>
<DIV><BR></DIV>
<DIV>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</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>For your reference, my database.yml file looks like this:</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>
<DIV>common: &common</DIV>
<DIV> adapter: postgis</DIV>
<DIV> host: localhost</DIV>
<DIV> username: <%= ENV['USER'] %></DIV>
<DIV> encoding: unicode</DIV>
<DIV> pool: 5</DIV>
<DIV> timeout: 5000</DIV>
<DIV> postgis_extension: postgis</DIV>
<DIV> schema_search_path: public,postgis</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>development:</DIV>
<DIV> <<: *common</DIV>
<DIV> database: mn_development</DIV></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV></DIV>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</A><BR>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</BLOCKQUOTE></DIV><BR></BODY></HTML>