[Mapserver-users] Error when querying PostGIS layer
Dylan Keon
keon at nacse.org
Fri Jan 16 10:54:04 EST 2004
Thanks. I tried that already in psql and the query works fine.
Any possibility this could be a problem with PostGIS 0.8.x? I ask only
because two of us on the list are having very similar problems right now.
My versions again: MS 4.0.1, Postgres 7.4.1, PostGIS 0.8.1 on Linux.
Thanks for any additional insights...
--Dylan
Mark Cave-Ayland wrote:
> Hi Dylan,
>
> Sometimes working with PostGIS/Mapserver I find that error messages
> don't always make it through to Mapserver. A good trick is to copy the
> query from the select without the cursor declaration, paste it into a
> psql session, and you should then see the exact error message being
> generated by PostgreSQL.
>
> For example, in your case, type the following in psql and see if it
> gives a more verbose error message:
>
> 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') )
>
> Cheers,
>
> Mark.
>
>>-----Original Message-----
>>From: mapserver-users-admin at lists.gis.umn.edu
>>[mailto:mapserver-users-admin at lists.gis.umn.edu] On Behalf Of
>>Dylan Keon
>>Sent: 15 January 2004 20:01
>>To: mapserver-users at lists.gis.umn.edu
>>Subject: Re: [Mapserver-users] Error when querying PostGIS layer
>>
>>
>>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 '<check your .map
>>>file>'.<br>
>>>Standard ways of specifiying are : <br>
>>>(1) 'geometry_column from geometry_table' <br>
>>>(2) 'geometry_column from (<sub query>) as foo using unique
>>><column name> using SRID=<srid#>' <br><br>
>>>
>>>Make sure you put in the 'using unique <column name>' 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)
More information about the mapserver-users
mailing list