[GIS] Problem in running query on PostGIS using PHP

PHPpostgispostgis-1.5postgis-2.0postgresql

Would you kindly help me solving this problem. I'm trying to use the PHP code provided here in order to read geo-data from PostGIS. The code seems clear without any problem (also nobody has encountered any problem using it), but when I run it, gives me the error: "An SQL error occurred", which based on the following code provided below, is shown whenever the query cannot be executed and crashes. Does anybody know what the problem could be?

Below is the place where the problem is happening:

# Try query or error
    $rs = pg_query($conn, $sql);
    if (!$rs) {
        echo "An SQL error occured.\n";
        exit;
    }

The full PHP code is:

<?php
/**
 * @param       string      $geotable       The PostGIS layer name *REQUIRED*
 * @param       string      $geomfield      The PostGIS geometry field *REQUIRED*
 */
function escapeJsonString($value) { # list from www.json.org: (\b backspace, \f formfeed)
  $escapers = array("\\", "/", "\"", "\n", "\r", "\t", "\x08", "\x0c");
  $replacements = array("\\\\", "\\/", "\\\"", "\\n", "\\r", "\\t", "\\f", "\\b");
  $result = str_replace($escapers, $replacements, $value);
  return $result;
}
    $geotable = 'mytable';
    $geomfield = 'the_geom';

# Connect to PostgreSQL database
$conn = pg_connect("dbname='mydbname' user='myusername' password='mypassword' host='localhost'");
if (!$conn) {
    echo "Not connected : " . pg_error();
    exit;
}

# Build SQL SELECT statement and return the geometry as a GeoJSON element in EPSG: 4326
$sql = "SELECT " . pg_escape_string($fields) . ", st_asgeojson(transform(" . pg_escape_string($geomfield) . ",$srid)) AS geojson FROM " . pg_escape_string($geotable);
if (strlen(trim($parameters)) > 0) {
    $sql .= " WHERE " . pg_escape_string($parameters);
}
if (strlen(trim($orderby)) > 0) {
    $sql .= " ORDER BY " . pg_escape_string($orderby) . " " . $sort;
}
if (strlen(trim($limit)) > 0) {
    $sql .= " LIMIT " . pg_escape_string($limit);
}
if (strlen(trim($offset)) > 0) {
    $sql .= " OFFSET " . pg_escape_string($offset);
}
//echo $sql;

# Try query or error
$rs = pg_query($conn, $sql);
if (!$rs) {
    echo "An SQL error occured.\n";
    exit;
}

# Build GeoJSON
$output    = '';
$rowOutput = '';

while ($row = pg_fetch_assoc($rs)) {
    $rowOutput = (strlen($rowOutput) > 0 ? ',' : '') . '{"type": "Feature", "geometry": ' . $row['geojson'] . ', "properties": {';
    $props = '';
    $id    = '';
    foreach ($row as $key => $val) {
        if ($key != "geojson") {
            $props .= (strlen($props) > 0 ? ',' : '') . '"' . $key . '":"' . escapeJsonString($val) . '"';
        }
        if ($key == "id") {
            $id .= ',"id":"' . escapeJsonString($val) . '"';
        }
    }

    $rowOutput .= $props . '}';
    $rowOutput .= $id;
    $rowOutput .= '}';
    $output .= $rowOutput;
}

$output = '{ "type": "FeatureCollection", "features": [ ' . $output . ' ]}';
echo $output;
?>

Best Answer

The first thing to do is to display the returned error as follow. You should then be able to find the error :

# Try query or error
$rs = pg_query($conn, $sql);
if (!$rs) {
    echo "An SQL error occured.\n";
    echo pg_last_error($conn); 
    exit;
}
Related Question