Running query on PostGIS using PHP

Question

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

Answer 1

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;
}

• Catlover: 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: 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)
• simo: indeed, I focus on the error message at first glance. Not on finding the cause. Glad you got it easily.

Answer 2

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.