current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 2 down vote favorite

When I run a PostgreSQL query containing ::geometry casting, I get a type "geometry" does not exist error. I am using php5-pgsql V5.3.10, php5-fpm 5.4.13, Laravel 4, Postgresql 9.1, PostGIS 2.0.1 on Ubuntu 12.04. geometry type is PostGIS-specific.

Without the casting, the query runs fine. The original query also works fine when queried directly against the PostgreSQL database using pgAdmin3. Why is this?

Query

$busstops = DB::connection('pgsql')
    ->table('locations')
    ->select(DB::raw('geog::geometry as lat, geog::geometry as lng'))
    ->get();

Query without casting (No errors)

$busstops = DB::connection('pgsql')
    ->table('locations')
    ->select(DB::raw('geog as lat, geog as lng'))
    ->get();

Error:

Exception: SQLSTATE[42704]: Undefined object: 7 ERROR: type "geometry" does not exist LINE 1: select geog::geometry as lat from "locations" ^ (SQL: select geog::geometry as lat from "locations") (Bindings: array ( ))

\dT geometry

                     List of data types
 Schema |   Name   |               Description
--------+----------+-----------------------------------------
 public | geometry | postgis type: Planar spatial data type.
(1 row)
share|improve this question
    
Any chance you installed the geometry type into a separate schema that's on the search_path in manual queries but not via your tool? Or that you're not really connecting to the same DB in your tool and manual queries? – Craig Ringer Apr 3 '13 at 7:23
    
@CraigRinger I ran CREATE EXTENSION postgis; CREATE EXTENSION postgis-topology; when connected to the database, which I assumed (incorrectly?) that it will install geometry type for all schemas? I do have multiple schemas, how do I install geometry type for a specfic/all schemas? – Nyxynyx Apr 3 '13 at 7:26
1  
You don't have to install the type for each schema. You can optionally install extensions in their own schema, in which case that schema must be on the search_path for the extension to be usable. You don't seem to have done that so I doubt this is a search_path issue. Confirm with \dT geometry in psql, confirm what schema the geometry type is listed as being in. If it's installed in public then perhaps your search_path is being set so that it doesn't include the public schema by your app? – Craig Ringer Apr 3 '13 at 7:34
    
@CraigRinger Updated question with the output. Seems like its only available in the public schema!! – Nyxynyx Apr 3 '13 at 7:38
    
Yep, so you need to make sure the public schema is always on the search_path, or install it do a different schema that contains nothing but PostGIS (called, say, postgis) and make sure that's always on the search_path. See the SCHEMA clause of CREATE EXTENSION: postgresql.org/docs/current/static/sql-createextension.html – Craig Ringer Apr 3 '13 at 7:40

1 Answer 1

up vote 2 down vote accepted

The application is switching the search_path around so that public isn't on the search_path. By default extensions are installed into public, so you're finding that geometry and the other PostGIS types and functions become unavailable from the application when it switches the search_path.

You need to:

  • Create a new schema postgis;
  • Move the PostGIS extension into the postgis schema; and
  • Make sure that the new postgis schema is always on the search_path, probably using application-specific settings
share|improve this answer
    
Is there a way to ensure that certain schemas are always in the search path? – Ethereal Apr 3 '13 at 17:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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