[postgis-users] php query problem "AsText(table.geometry)", multiple schemas

[Andreas Neumann]

Hi,

I have the very strange situation that a query works fine in the psql 
client or in pgadminIII, but the exact same query doesn't work in php5.

Maybe I have to mention that I have three schemas in the db: public 
(contains the postgis functions and tables), two other schemas 
containing the actual data.

Here is my SQL query:

SELECT place.brief_place AS place, place.place_number, event.event AS 
event_type, event_rels.display_date AS display_date, 
event_rels.start_date AS start_date, event_rels.end_date AS end_date, 
place.tgnid as tgnid, AsText(coordinates.the_geom_albers) AS geom FROM 
ulan.place, ulan.event, ulan.event_rels, tgn.coordinates WHERE 
event_rels.subject_id = 500019902 AND event.event_id = 
event_rels.event_id AND event_rels.place = place.place_number AND 
place.tgnid = coordinates.subject_id ORDER BY event_rels.display_date ASC

and here is my php code:

$mySQL = 'SELECT subject.subject_id AS subject_id, subject.level AS 
level, subject.scope AS biographylong, term.term AS name, 
biography.birth_date AS birth_date, biography.death_date AS death_date, 
biography.biography AS biography, biography.sex AS sex, 
ptype_role.ptype_role AS preferred_role, biography.birth_place AS 
birth_place, biography.death_place AS death_place FROM ulan.subject, 
ulan.term, ulan.biography, ulan.ptype_role_rels, ulan.ptype_role WHERE 
subject.level <= '.$level.' AND subject.record_type = \'P\' AND 
subject.subject_id = term.subject_id AND subject.subject_id = 
biography.subject_id AND subject.subject_id = ptype_role_rels.subject_id 
AND ptype_role_rels.ptype_role_id = ptype_role.ptype_role_id AND 
term.preferred = \'P\' AND biography.preferred = \'P\' AND 
ptype_role_rels.preferred = \'P\' AND biography.death_date >= 
'.$beginDate.' AND biography.birth_date <= '.$endDate.' ORDER BY 
biography.birth_date';

$my_result_set = pg_query($my_pg_connect,$mySQL) or die (pg_ErrorMessage());

and here is the error message that I get:

<br />
<b>Warning</b>:  %v%v() [<a href='function.%v'>function.%v</a>]: Query 
failed: ERROR:  function astext(public.geometry) does not exist
HINT:  No function matches the given name and argument types. You may 
need to add explicit type casts. in 
<b>/home/an/www/phd/prototype/unstable/sendArtistData.php</b> on line 
<b>92</b><br />
<br />
<b>Warning</b>:  pg_num_rows(): supplied argument is not a valid 
PostgreSQL result resource in 
<b>/home/an/www/phd/prototype/unstable/sendArtistData.php</b> on line 
<b>93</b><br />

It seems that it can't find the function AsText(). I made sure it is 
present in schema "public". The geometry data is in schema "tgn". Do I 
have to tell php something about the multiple schema situation? Some 
sort of environment variable?

Thanks for any information!

Andreas

-- 
----------------------------------------------
Andreas Neumann
Institute of Cartography
ETH Zurich
Wolfgang-Paulistrasse 15
CH-8093  Zurich, Switzerland

Phone: ++41-44-633 3031, Fax: ++41-44-633 1153
e-mail: neumann at karto.baug.ethz.ch
www: http://www.carto.net/neumann/
SVG.Open: http://www.svgopen.org/
Carto.net: http://www.carto.net/