Problem with nested query in PhP and PostGIS

manpreet Best Answer 2 years ago

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">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 ="";

for ($i = 0; $i < pg_numrows($query_result); $i++) {

    $townname = pg_result($query_result, $i, 0);
    $townkml =  pg_result($query_result, $i, 1);

    $kml .= "Town: ".$townname."".$townname."".$townkml."\n";
}

$kml .= "";

header('Content-Type: application/vnd.google-earth.kml+xml');
header("Content-Disposition: attachment; filename=sample75.kml");

echo $kml;

manpreet 2 years ago

$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)";