Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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;
?>
share|improve this question

2 Answers 2

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;
}
share|improve this answer
    
Thanks for the tip. The error is as follow (I guess the function st_asgeojson is not working properly? any suggestion how it can be fixed?): ERROR: function transform(geometry, integer) does not exist LINE 1: SELECT *, st_asgeojson(transform(geom,26918)) AS geojson FRO... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. –  Catlover Feb 24 at 13:25
    
I searched for the function transform(), and found out that the correct function name is ST_Transform(). Replacing this solved the problem. Thanks for your help Simo(+1) –  Catlover Feb 24 at 13:34
    
indeed, I focus on the error message at first glance. Not on finding the cause. Glad you got it easily. –  simo Feb 24 at 17:16
up vote 2 down vote accepted

The error was actually happening where the sql command was generated at this line of code:

$sql = "SELECT " . pg_escape_string($fields) . ", st_asgeojson(transform(" . pg_escape_string($geomfield) . ",$srid)) AS geojson FROM " . pg_escape_string($geotable);

Turns out that there is a bug in the code presented here, and the correct name of the transform function is:

ST_Transform()

You can read more information about it here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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