4

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.

Improve this question
3
  • SHOW search_path and \dt *.geometry please.
    – Craig Ringer
    Dec 31, 2013 at 2:56
  • Thanks a lot for your comment and answer. A few hours before you responded to my question, I was at my wits' end and I dropped my dev database. That didn't help, so I also dropped the postgis extension (drop extension postgis cascade;). After bundle exec rake db:create, bundle exec rake db:gis:setup, and bundle 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 just public. Unfortunately I never executed dT *.geometry...
    – user664833
    Dec 31, 2013 at 5:23
  • I am piecing this together from my Terminal scrollback history. When I started having problems with my test db, I quit psql and started it up again. At that point show search_path; returned just public (I don't yet know how to keep the search_path fixed for a given DB). Anyway, the error that emerged was ERROR: type "geometry" does not exist. I am sure that, as you suggest, the problem was related to search_path and conflicts with PostGIS installs.
    – user664833
    Dec 31, 2013 at 5:33

1 Answer 1

Reset to default
2

Sounds to me like you have PostGIS installed in your database more than once, possibly once via CREATE EXTENSION and again via a load script, or from a dump.

The hint is that:

PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry

mentions the schema qualifier postgis.geometry for one operand, and omits it for another. I'd say you have two copies of the the geometry type in different schemas, probably public and postgis.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.