1

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);
?>

1 Answer 1

2

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)";  
3
  • you mean I only need to change to double-quotations? Or are their more differences between your query and mine?
    – Catlover
    Commented Mar 2, 2014 at 19:36
  • No other changes than the double quotes. Commented Mar 2, 2014 at 19:37
  • I am curious. Did changing to double quotes resolve you problem?
    – Ralph Dell
    Commented Mar 3, 2014 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.