PostgreSQL query error when using PHP

Question

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)

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
– Craig Ringer, Commented Apr 3, 2013 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
– Nyxynyx, Commented Apr 3, 2013 at 7:26
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
– Craig Ringer, Commented Apr 3, 2013 at 7:34
@CraigRinger Updated question with the output. Seems like its only available in the public schema!! — Nyxynyx
– Nyxynyx, Commented Apr 3, 2013 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
– Craig Ringer, Commented Apr 3, 2013 at 7:40

Craig Ringer · Accepted Answer · 2013-04-03 08:10:58Z

2

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

answered Apr 3, 2013 at 8:10

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ethereal Over a year ago

Is there a way to ensure that certain schemas are always in the search path?

Collectives™ on Stack Overflow

PostgreSQL query error when using PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related