[Mapserver-users] Error when querying PostGIS layer

[Dylan Keon]

I'd be really grateful if some of you postgis/postgres gurus could give 
me a hand with this (see question below) - it's got me stumped.  Jason 
Nielsen posted a similar question a little while ago.

I've already tried GRANT SELECT ON quad24k TO PUBLIC; and verified that 
the asbinary SELECT statement following 'DECLARE mycursor BINARY CURSOR 
FOR' works in psql.

TIA for any help.  I pasted some more info at the bottom of the message.

--Dylan


Dylan Keon wrote:
> I'm having trouble querying a PostGIS layer via PHP Mapscript.  The 
> layer renders fine in the app and labels get generated, etc.  But I get 
> an error when trying to query the layer.  It fails when it reaches 
> getShape() - if I comment out that line everything else works.  Also, if 
> I run this code on a shapefile version of the same layer it works fine.
> 
> I'm using MS 4.0.1, Postgres 7.4.1, PostGIS 0.8.1 on Linux.  Thanks for 
> any help.
> 
> --Dylan
> 
> ----------------------------------------------------------------------
> 
>  From the apache error log:
> 
> PHP Warning:  [MapServer Error]: msDrawMap(): Failed to draw layer named
> 'quad_index_24k'.
>  in /opt/httpd/htdocs/test.php on line 132
> PHP Warning:  [MapServer Error]: prep_DB(): Error executing POSTGIS
> DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR 
> FOR SELECT 
> nf1::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text 
> from quad24k WHERE the_geom && setSRID('BOX3D(311625 301705,586625 
> 577205)'::BOX3D, find_srid('','quad24k','the_geom') )' <br><br>
> 
> Postgresql reports the error ''<br><br>
> 
> More Help:<br><br>
> 
> Error with POSTGIS data variable. You specified '&lt;check your .map 
> file&gt;'.<br>
> Standard ways of specifiying are : <br>
> (1) 'geometry_column from geometry_table' <br>
> (2) 'geometry_column from (&lt;sub query&gt;) as foo using unique 
> &lt;column name&gt; using SRID=&lt;srid#&gt;' <br><br>
> 
> Make sure you put in the 'using unique  &lt;column name&gt;' and 'using
> SRID=#' clauses in.
> 
> <br><br>For more help, please see
> http://postgis.refractions.net/documentation.php
> 
> <br><br>Mappostgis.c - version of June 12/2003.
> 
> in /opt/httpd/htdocs/test.php on line 132
> PHP Fatal error:  Call to a member function on a non-object in 
> /opt/httpd/htdocs/test.php on line 133
> 
> ----------------------------------------------------------------------
> 
> // simple function for testing postgis layer query
> function doQuery($map, $map_x, $map_y, $last_extent)
> {
>         $layer = $map->getLayerByName('quad_index_24k');
>         $coords = pix2geo($map, $map_x, $map_y, $last_extent);
>         $geo = preg_split("/,/", $coords);
> 
>         // create point object
>         $click_pt = ms_newPointObj();
>         $click_pt->setXY($geo[0], $geo[1]);
> 
>         // query the map at the point clicked
>         @$map->queryByPoint($click_pt, MS_SINGLE, -1);
>         $count_results = $layer->getNumResults();
> echo "count_results = $count_results<br>\n";
> 
>         if($count_results > 0) {
>                 $layer->open();
>                 $result = $layer->getResult(0);
> echo "<pre>getResult:<br>"; print_r($result); ech "</pre>";
>                 $resultShape = $layer->getShape(-1,$result->shapeindex);
> echo "<pre>getShape:<br>"; print_r($resultShape); echo "</pre>";
>         } else echo "No records returned";
> 
>         $click_pt->free();
>         $resultShape->free();
> }
> 
> ----------------------------------------------------------------------
> 
> LAYER
>   NAME quad_index_24k
>   TYPE POLYGON
>   CONNECTIONTYPE POSTGIS
>   CONNECTION "user=read dbname=oregon"
>   DATA "the_geom from quad24k"
>   STATUS ON
>   MAXSCALE 2400000
>   LABELMAXSCALE 600000
>   LABELMINSCALE 20000
>   TEMPLATE "null"
>   CLASS
>     OUTLINECOLOR 0 25 140
>     COLOR -1 -1 -1
>     COLOR 190 230 190
>     TEXT ([name] [nf1])
>     LABEL
>       WRAP " "
>       POSITION CC
>       SIZE SMALL
>       BUFFER 50
>       FORCE TRUE
>       MINFEATURESIZE 35
>     END
>   END
> END
> 
> ----------------------------------------------------------------------


Here are some additional data:


oregon=# select * from geometry_columns where f_table_name = 'quad24k';
  f_table_catalog | f_table_schema | f_table_name | f_geometry_column | 
coord_dimension | srid |     type     | attrelid | varattnum | stats
-----------------+----------------+--------------+-------------------+-----------------+------+--------------+----------+-----------+-------
                  | public         | quad24k      | the_geom          | 
               2 |   -1 | MULTIPOLYGON |    17407 |
19 |
(1 row)


oregon-# \d quad24k
             Table "public.quad24k"
   Column   |         Type         | Modifiers
-----------+----------------------+-----------
  gid       | integer              |
  area      | double precision     |
  perimeter | double precision     |
  id        | bigint               |
  name      | character varying    |
  states    | character varying    |
  latlon    | character varying    |
  usgscode  | character varying    |
  scale     | character varying    |
  ohiocode  | character varying    |
  st_s      | integer              |
  st_w      | integer              |
  the_geom  | geometry             |
  usfscode  | character varying(7) |
  nf1       | character varying(3) |
  nf2       | character varying(3) |
  nf3       | character varying(3) |
  geoloc    | character varying(4) |
Indexes:
     "quad24k_gist" gist (the_geom)
     "quad24k_oid" btree (oid)