36 lines
941 B
PHP
36 lines
941 B
PHP
<?php
|
|
require_once __DIR__ . '/common.php';
|
|
|
|
$dbconn = getDBConnection();
|
|
|
|
// Performing SQL query
|
|
$query = "WITH subquery_points AS (
|
|
SELECT geom AS point_geometry
|
|
FROM cams
|
|
WHERE active = true and lastsuccess IS NOT NULL AND (EXTRACT(EPOCH FROM (current_timestamp - lastsuccess ))/60) < 60
|
|
)
|
|
SELECT jsonb_build_object(
|
|
'type', 'FeatureCollection',
|
|
'features', jsonb_agg(jsonb_build_object(
|
|
'type', 'Feature',
|
|
'geometry', ST_AsGeoJSON(ST_Buffer(point_geometry::geography, 8000))::jsonb
|
|
))
|
|
) AS feature_collection
|
|
FROM subquery_points";
|
|
|
|
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
|
|
|
|
// Printing results in HTML
|
|
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
|
|
$array[] = $line;
|
|
}
|
|
print_r($array[0]['feature_collection']);
|
|
|
|
// Free resultset
|
|
pg_free_result($result);
|
|
|
|
// Close database connection when needed
|
|
if (isset($dbconn)) {
|
|
pg_close($dbconn);
|
|
}
|
|
?>
|