1

I'm catching features from my PostGIS database with PHP, which generates dynamic sql requests. Since I can't manage to wrap my response into an ol.layer.Image I thought about using ST_UNION or ST_COLLECT to improve map rendering performance. My current generated SQL is looking like this:

SELECT krs,class, st_asgeojson(st_transform(ST_SimplifyPreserveTopology(the_geom,0),4326),6) AS geojson 
FROM tbl_detailansicht_f 
WHERE krs = 'Rotenburg (Wümme)'  AND class = 5 AND the_geom &&  st_transform(st_makeenvelope(7.981262207031247,52.23004870271677,11.112365722656248,53.695628373000176,4326),4326)

I would like to get a sql statement like this:

SELECT krs,class, st_asgeojson(st_transform(ST_SimplifyPreserveTopology(st_collect(the_geom),0),4326),6) AS geojson 
FROM tbl_detailansicht_f 
WHERE krs = 'Rotenburg (Wümme)'  AND class = 5 AND the_geom &&  st_transform(st_makeenvelope(7.981262207031247,52.23004870271677,11.112365722656248,53.695628373000176,4326),4326)
GROUP BY krs, class

So my PHP (by https://github.com/bmcbride/PHP-Database-GeoJSON) needs to be adjusted in order to consider this change, which I fail at.

    <?php
/**
 * GET GeoJSON from PostGIS
 * Query a PostGIS table or view and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.
 * Author:  Bryan R. McBride, GISP, adapted by G.Moes
 * Contact: bryanmcbride.com
 * GitHub:  https://github.com/bmcbride/PHP-Database-GeoJSON
 * 
 * @param       string      $geotable       The PostGIS layer name *REQUIRED*
 * @param       string      $geomfield      The PostGIS geometry field *REQUIRED*
 * @param       string      $srid           The SRID of the returned GeoJSON *OPTIONAL (If omitted, EPSG: 2169 will be used)*
 * @param       string      $fields         Fields to be returned *OPTIONAL (If omitted, all fields will be returned)* 
 *                              NOTE- Uppercase field names should be wrapped in double quotes
 * @param       string      $parameters     SQL WHERE clause parameters *OPTIONAL*
 * @param       string      $orderby        SQL ORDER BY constraint *OPTIONAL*
 * @param       string      $sort           SQL ORDER BY sort order (ASC or DESC) *OPTIONAL*
 * @param       string      $limit          Limit number of results returned *OPTIONAL*
 * @param       integer     $precision      digits of returned geojson 6 = 0.111 m submeter as DEFAULT *OPTIONAL*
 * @param       real        $simplify       simplify geometry to >5.0m as DEFAULT *OPTIONAL*  
 * @param       string      $offset         Offset used in conjunction with limit *OPTIONAL*
 * @return      string                  resulting geojson string
 */


# Connect to PostgreSQL database You need to pass here the credentials to connect to Your database
require("./connect.php");


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


# Retrive URL variables
if (empty($_GET['geotable'])) {
    echo "missing required parameter: <i>geotable</i>";
    exit;
} else
    $geotable = $_GET['geotable'];
if (empty($_GET['geomfield'])) {
    $geomfield='the_geom';
} else
    $geomfield = $_GET['geomfield'];
if (empty($_GET['srid'])) {
    $srid = 4326;    // changethis if You need another standard SRID
} else
    $srid = $_GET['srid'];
if (empty($_GET['fields'])) {
    $fields = '*';
} else
    $fields = $_GET['fields'];
$parameters = $_GET['where'];
$bbox = $_GET['bbox'];
if (empty($_GET['precision'])) {
    $precision = 6;    // change this to Your needs
} else
    $precision = $_GET['precision'];
if (empty($_GET['simplify'])) {
    $simplify = 0;   // change this to Your needs
} else
    $simplify = $_GET['simplify'];    
$orderby    = $_GET['orderby'];
if (empty($_GET['sort'])) {
    $sort = 'ASC';
} else
    $sort = $_GET['sort'];
$limit      = $_GET['limit'];
$offset     = $_GET['offset'];



# Build SQL SELECT statement and return the geometry as a GeoJSON element in EPSG: 4326
$sql = "SELECT " . pg_escape_string($fields) . ", st_asgeojson(st_transform(ST_SimplifyPreserveTopology(" . pg_escape_string($geomfield) . ",".$simplify."),4326),".$precision.") AS geojson FROM " . pg_escape_string($geotable);
if (strlen(trim($parameters)) > 0) {

      $sql .= " WHERE " . str_replace("''", "'", pg_escape_string($parameters));
}
if (strlen(trim($parameters)) > 0 AND strlen(trim($bbox)) > 0){
     $sql .= " AND the_geom &&  st_transform(st_makeenvelope(".pg_escape_string ($bbox).",4326),".$srid.")";
}
if (strlen(trim($parameters)) <= 0 AND strlen(trim($bbox)) > 0) {
      $sql .= " WHERE the_geom &&  st_transform(st_makeenvelope(".pg_escape_string ($bbox).",4326),".$srid.")";
}
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($db_connection, $sql);
if (!$rs) {
    echo "An SQL error occured.\n";
    echo $sql;
    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 . ' ]}';

header('Content-type:application/json;charset=utf-8');
echo json_encode( $output);
?>
1

got it!

  $sql = "SELECT " . pg_escape_string($fields) . ", st_asgeojson(st_transform(ST_SimplifyPreserveTopology(ST_Collect(" . pg_escape_string($geomfield) .   "),".$simplify."),4326),".$precision.") AS geojson FROM " . pg_escape_string($geotable);
if (strlen(trim($parameters)) > 0) {

      $sql .= " WHERE " . str_replace("''", "'", pg_escape_string($parameters));
}
if (strlen(trim($parameters)) > 0 AND strlen(trim($bbox)) > 0){
     $sql .= " AND the_geom &&  st_transform(st_makeenvelope(".pg_escape_string ($bbox).",4326),".$srid.")";
}
if (strlen(trim($parameters)) <= 0 AND strlen(trim($bbox)) > 0) {
      $sql .= " WHERE the_geom &&  st_transform(st_makeenvelope(".pg_escape_string ($bbox).",4326),".$srid.")";
}
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);
}
$sql .= " GROUP BY " . pg_escape_string($fields);

sadly that doesn't improve map rendering performance at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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