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

[Stephen Woodbridge]

Andreas,

Do you use different users when connecting from the different clients? 
If you do then you probably need to change the search_path for the PHP5 
client to include the public schema. It is also possible that PHP5 is 
resetting the search_path but this is likely to be the root of the problem.

-Steve

Andreas Neumann wrote:
> 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
>