I have had PostgreSQL 9.3.1
and PostGIS 2.1.0
installed (via https://github.com/PostgresApp/PostgresApp/releases/tag/9.3.1.1-RC1) and working on my laptop for some time, and have been doing Ruby (2.0.0
) on Rails (4.0.0
) development with them.
I was able to successfully run typical operations, for example using @ to determine whether a point (postgis.geometry(Point,3785)
) is in a bounding box. Most recently I took the exact points and query I was successfully running in dev and made an equivalent feature test. This is when I began noticing issues...
Whereas a point on the dev side was stored like this (SRID 3785
):
POINT (-13740247.269752283 6126532.67447704)
...the test side saw it like this:
POINT (-8238307.241152702 4970299.628630948)
I wanted to post a Stack Overflow question about the issue, so I wanted the exact DB version. Whereas select postgis_full_version();
previously worked (it's still in my Terminal scrollback history), now it was returning ERROR: function postgis_full_version() does not exist
. At that point I didn't think to check the search_path
, and instead I followed https://stackoverflow.com/a/8459682 -- first I tried CREATE EXTENSION postgis;
but got ERROR: extension "postgis" already exists
-- and then I executed:
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/spatial_ref_sys.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis_comments.sql
Unfortunately those did not do the trick, and worse, now when I run my usual query I am seeing:
PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry
How could things go so wrong? I have not yet reset my computer. I only tried restarting PostgreSQL so far. Any ideas? Thank you for reading.
SHOW search_path
and\dt *.geometry
please.postgis
extension (drop extension postgis cascade;
). Afterbundle exec rake db:create
,bundle exec rake db:gis:setup
, andbundle exec rake db:migrate
, I was able to run my queries again. Sadly, I am unable to try what you suggested, though I can give you a partial idea from my Terminal scrollback history:\dT geometry
had returned justpublic
. Unfortunately I never executeddT *.geometry
...psql
and started it up again. At that pointshow search_path;
returned justpublic
(I don't yet know how to keep thesearch_path
fixed for a given DB). Anyway, the error that emerged wasERROR: type "geometry" does not exist
. I am sure that, as you suggest, the problem was related tosearch_path
and conflicts with PostGIS installs.