Problem with nested query in PhP and PostGIS

Question

I have a simple problem regarding a nested query. I would like to select all buildings in a given circular buffer and create a kml file of those selected objects. The following query does the job very well and returns the results when I run it in PgAdmin.

SELECT total_pop, ST_AsKML(ST_Transform(geom,26918)) AS kmlgm FROM h_buildings WHERE ST_DWithin(ST_Transform(geom, 26918), (SELECT ST_Transform(ST_GeomFromText('POINT(1116858.6062789 7086290.6680572)', 900913), 26918)), 1000)

the "POINT(1116858.6062789 7086290.6680572)" is the center of the circle, and the value 1000 is the radius.

As you can see the query has another SELECT inside it (nested).

The following PHP code is able to generate kml file based on the results of query. I have already tested it with a simple SELECT query and it works fine. However, when I use the above-metioned query it does not work, and it does not give an error.

Does this mean that PHP does not support nested query?!

I'm a beginner, Can anybody please help me to find where the problem is and how it can be solved?

PHP code:

<?php
$dbconn = pg_connect("dbname='mydb' user='postgres' password='****' host='localhost'");
if (!$dbconn) {
 echo "Not connected : " . pg_error();
 exit;
}
$srid = '4326';
$sql = 'SELECT name, ST_AsKML(ST_Transform(geom,26918)) AS kmlgm FROM Buildings WHERE ST_DWithin(ST_Transform(geom, 26918), (SELECT ST_Transform(ST_GeomFromText('POINT(1116858.6062789 7086290.6680572)', 900913), 26918)), 1000)'; 
 # Try query or error
$query_result = pg_query($dbconn, $sql);
if (!$query_result) {
 echo "An SQL error occured.\n";
 echo pg_last_error($dbconn);
 exit;
}
$kml ="<?xml version='1.0' encoding='UTF-8'?><kml xmlns='http://earth.google.com/kml/2.1'><Document>";
for ($i = 0; $i < pg_numrows($query_result); $i++) {
 $townname = pg_result($query_result, $i, 0);
 $townkml = pg_result($query_result, $i, 1);
 $kml .= "<Placemark><name>Town: ".$townname."</name><description>".$townname."</description>".$townkml."</Placemark>\n";
}
$kml .= "</Document></kml>";
header('Content-Type: application/vnd.google-earth.kml+xml');
header("Content-Disposition: attachment; filename=sample75.kml");
echo $kml;
pg_close($dbconn);
?>

Answer 1

PHP knows nothing about the SQL query sent to PostGIS. Therefore, it doesn't matter if you have nested queries or not.

But it is very important for PostGIS, in order to perform the query, to receive a valid query, like you did when you have used PgAdmin.

So, I think you need to revise your php $sql variable, like this:

$sql = "SELECT name, ST_AsKML(ST_Transform(geom,26918)) AS kmlgm FROM Buildings WHERE ST_DWithin(ST_Transform(geom, 26918), (SELECT ST_Transform(ST_GeomFromText('POINT(1116858.6062789 7086290.6680572)', 900913), 26918)), 1000)"; 

• Catlover: you mean I only need to change to double-quotations? Or are their more differences between your query and mine?
• Sorin Călinică: No other changes than the double quotes.
• Ralph Dell: I am curious. Did changing to double quotes resolve you problem?