[Mapserver-users] Error when querying PostGIS layer
Dylan Keon
keon at nacse.org
Fri Jan 16 12:21:37 EST 2004
To check your PostGIS version go to
postgresql-7.x.x/contrib/postgis-0.x.x. In my case, I left the postgis
directory name as postgis-0.8.1. Or you can look at the top lines in
CHANGES or README.postgis.
Yes, the SQL reported by PostGIS as an error runs fine from psql. Is
that the same in your case?
--Dylan
Eric Bridger wrote:
> I am also experiencing very similiar symptoms. Code which is working on
> our development server MS 4.0.1 and Postgres 7.3.4 is failing on our
> production server. Both Free BSD 4.8. But I'm not sure how to check the
> PostGIS version?
>
> The SQL reported by PostGIS as an error in fact runs fine from psql.???
>
> Eric
>
>
> On Fri, 2004-01-16 at 10:54, Dylan Keon wrote:
>
>>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)
>>
>>
>>_______________________________________________
>>Mapserver-users mailing list
>>Mapserver-users at lists.gis.umn.edu
>>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>>
>
>
More information about the mapserver-users
mailing list