change PHP to generate dynamic SQL

manpreet Best Answer 2 years ago

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_UNIONor 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: geotable";
    exit;
} else
    $geotable = $_GET['geotable'];
if (empty($_GET['geomfield'])) {
    $geomfield='the_geom';
} else
    $geomfield = $_GET['geomfield'];
if (empty($_GET