CODEWITHSUNDEEP
postgresqlpostgis
Nyxynyx
Nyxynyx

Reputation: 63619

PostgreSQL query error when using PHP

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)

Upvotes: 2

Views: 1790

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324455

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

Upvotes: 2

Related Questions