That is php code. if that is what you want, but I don't do php.
SQL function: ST_HexagonGrid(float8
size
, geometry
bounds
);
This will create a set of hexagons according to the parameters you pass:
size - the length of one side of the hexagon
geometry - a Postgis geometry used to determine the extent covered by the set
of hexagon polygons.
You will need to determine the hex size you want generated for each scale, but
the extent should be the same for all scales.
I suggest you ask for SQL code, not just code, unless you prefer Python, or R
or some other language.
Brent Wood
Principal Technician, Fisheries
NIWA
DDI: +64 (4) 3860529
________________________________
From: Sylvain Ard <sylvain....@gmail.com>
Sent: Saturday, March 15, 2025 09:07
To: Brent Wood <brent.w...@niwa.co.nz>
Cc: qgis-user@lists.osgeo.org <qgis-user@lists.osgeo.org>
Subject: Re: [Qgis-user] question
sorry I ask chatGPT to make hexagons size constant, this is its code :
I precise that I ask it to make circles and squares too
backend :
<?php
header('Content-Type: application/json');
// Connexion Redis
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$redis->auth('your_strong_password');
// Connexion PostgreSQL
$pdo = new PDO("pgsql:host=localhost;dbname=database", "user", "password", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$shape = $_GET['shape'] ?? 'hexagone';
$bbox = explode(',', $_GET['bbox']);
$size = $_GET['size'] ?? 1000;
$radius = $_GET['radius'] ?? 500;
$resolution = $_GET['resolution'] ?? 8;
$zoom = $_GET['zoom'] ?? 2;
$cacheKey = "$shape:" . implode('-', $bbox) .
":$size:$radius:$resolution:$zoom";
// Vérifier le cache Redis
if ($redis->exists($cacheKey)) {
echo $redis->get($cacheKey);
exit;
}
list($minLng, $minLat, $maxLng, $maxLat) = $bbox;
$params = [':minLng' => $minLng, ':minLat' => $minLat, ':maxLng' => $maxLng,
':maxLat' => $maxLat];
if ($shape === "hexagone") {
$sql = "
SELECT h3_geo_to_h3(ST_Y(geom), ST_X(geom), :resolution) AS h3_cell,
COUNT(*) AS point_count
FROM your_points_table
WHERE geom && ST_MakeEnvelope(:minLng, :minLat, :maxLng, :maxLat, 4326)
GROUP BY h3_cell;
";
$params[':resolution'] = $resolution;
} elseif ($shape === "cercle") {
$sql = "
SELECT ST_AsGeoJSON(ST_Buffer(geom::geography, :radius)::geometry) AS
shape, COUNT(*) AS point_count
FROM your_points_table
WHERE geom && ST_MakeEnvelope(:minLng, :minLat, :maxLng, :maxLat, 4326)
GROUP BY shape;
";
$params[':radius'] = $radius;
} else {
$sql = "
SELECT ST_AsGeoJSON(ST_Envelope(ST_Buffer(geom::geography, :size,
'quad_segs=1')::geometry)) AS shape, COUNT(*) AS point_count
FROM your_points_table
WHERE geom && ST_MakeEnvelope(:minLng, :minLat, :maxLng, :maxLat, 4326)
GROUP BY shape;
";
$params[':size'] = $size;
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Mise en cache Redis (10 minutes)
$redis->setEx($cacheKey, 600, json_encode($result));
echo json_encode($result);
frontend :
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="UTF-8">
<title>Cartographie Dynamique (PHP + jQuery)</title>
<link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css";>
<script src="https://unpkg.com/leaflet/dist/leaflet.js";></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js";></script>
<style>
#map { height: 500px; width: 100%; }
</style>
</head>
<body>
<h2>Choisissez la forme :</h2>
<select id="shapeSelector">
<option value="hexagone">Hexagone</option>
<option value="cercle">Cercle</option>
<option value="carre">Carré</option>
</select>
<div id="map"></div>
<script>
const map = L.map('map').setView([0, 0], 2);
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png').addTo(map<http://tile.openstreetmap.org/%7Bz%7D/%7Bx%7D/%7By%7D.png').addTo(map>);
let layer;
let currentShape = 'hexagone';
function getColor(count) {
return count > 100 ? '#800026' :
count > 50 ? '#BD0026' :
count > 20 ? '#E31A1C' :
count > 10 ? '#FC4E2A' :
'#FFEDA0';
}
function fetchData() {
const bounds = map.getBounds();
const bbox =
`${bounds.getWest()},${bounds.getSouth()},${bounds.getEast()},${bounds.getNorth()}`;
const zoom = map.getZoom();
$.getJSON(`backend.php?shape=${currentShape}&bbox=${bbox}&size=1000&radius=500&resolution=8&zoom=${zoom}`,
function (data) {
if (layer) map.removeLayer(layer);
layer = L.geoJSON(data.map(item => JSON.parse(item.shape ||
item.h3_cell)), {
style: (feature) => ({
color: getColor(feature.properties?.point_count || 0),
weight: 2,
fillOpacity: 0.5
})
}).addTo(map);
});
}
$("#shapeSelector").change(function () {
currentShape = $(this).val();
fetchData();
});
map.on('moveend zoomend', fetchData);
fetchData();
</script>
</body>
</html>
________________________________
Sylvain Ard
0549507724
0778380991
sylvain....@gmail.com<mailto:sylvain....@gmail.com>
https://www.sylvain-ard.fr<http://sylvain-ard.fr/>
Entreprise individuelle SIRET : 80079243400022
Appt 26 Bât A Résidence Le Patio
83 rue de la Bugellerie
86000 Poitiers
Le ven. 14 mars 2025 à 20:59, Sylvain Ard
<sylvain....@gmail.com<mailto:sylvain....@gmail.com>> a écrit :
this is the code of chatGPT with H3 :
backend :
<?php
header('Content-Type: application/json');
$pdo = new PDO("pgsql:host=localhost;dbname=database", "user", "password",
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$redis->auth('password');
$shape = $_GET['shape'];
$bbox = explode(',', $_GET['bbox']);
$size = $_GET['size'] ?? null;
$radius = $_GET['radius'] ?? null;
$resolution = $_GET['resolution'] ?? 8;
$cacheKey = "$shape:" . implode('-', $bbox) . ":$size:$radius:$resolution";
if ($redis->exists($cacheKey)) {
echo $redis->get($cacheKey);
exit;
}
if ($shape === "hexagone") {
$sql = "
SELECT h3_geo_to_h3(ST_Y(geom), ST_X(geom), :resolution) AS h3_cell,
COUNT(*) AS point_count
FROM your_points_table
WHERE geom && ST_MakeEnvelope(:minLng, :minLat, :maxLng, :maxLat, 4326)
GROUP BY h3_cell;
";
$params = [':resolution' => $resolution, ':minLng' => $bbox[0], ':minLat'
=> $bbox[1], ':maxLng' => $bbox[2], ':maxLat' => $bbox[3]];
} elseif ($shape === "cercle") {
$sql = "
SELECT ST_AsGeoJSON(ST_Buffer(geom::geography, :radius)::geometry) AS
shape, COUNT(*) AS point_count
FROM your_points_table
WHERE geom && ST_MakeEnvelope(:minLng, :minLat, :maxLng, :maxLat, 4326)
GROUP BY shape;
";
$params = [':radius' => $radius, ':minLng' => $bbox[0], ':minLat' =>
$bbox[1], ':maxLng' => $bbox[2], ':maxLat' => $bbox[3]];
} else {
$sql = "
SELECT ST_AsGeoJSON(ST_Envelope(ST_Buffer(geom::geography, :size,
'quad_segs=1')::geometry)) AS shape, COUNT(*) AS point_count
FROM your_points_table
WHERE geom && ST_MakeEnvelope(:minLng, :minLat, :maxLng, :maxLat, 4326)
GROUP BY shape;
";
$params = [':size' => $size, ':minLng' => $bbox[0], ':minLat' => $bbox[1],
':maxLng' => $bbox[2], ':maxLat' => $bbox[3]];
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$redis->setEx($cacheKey, 3600, json_encode($result));
echo json_encode($result);
frontend :
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="UTF-8">
<title>Cartographie avec Forme Sélectionnée</title>
<link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css";>
<script src="https://unpkg.com/leaflet/dist/leaflet.js";></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js";></script>
<style>
#map { height: 500px; width: 100%; }
</style>
</head>
<body>
<h2>Choisissez la forme :</h2>
<select id="shapeSelector">
<option value="hexagone">Hexagone</option>
<option value="cercle">Cercle</option>
<option value="carre">Carré</option>
</select>
<div id="map"></div>
<script>
const map = L.map('map').setView([0, 0], 2);
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png').addTo(map<http://tile.openstreetmap.org/%7Bz%7D/%7Bx%7D/%7By%7D.png').addTo(map>);
let layer;
function fetchData(shape) {
const bbox = "-10,-10,10,10";
const url =
`backend.php?shape=${shape}&bbox=${bbox}&size=1000&radius=500&resolution=8`;
$.getJSON(url, function (data) {
if (layer) map.removeLayer(layer);
layer = L.geoJSON(data, {
style: {
color: shape === "carre" ? "green" : shape === "cercle" ?
"blue" : "red",
weight: 2,
fillOpacity: 0.4
}
}).addTo(map);
});
}
$("#shapeSelector").change(function () {
fetchData($(this).val());
});
fetchData("hexagone"); // Chargement initial
</script>
</body>
</html>
Sylvain Ard
0549507724
0778380991
sylvain....@gmail.com<mailto:sylvain....@gmail.com>
https://www.sylvain-ard.fr<http://sylvain-ard.fr/>
Entreprise individuelle SIRET : 80079243400022
Appt 26 Bât A Résidence Le Patio
83 rue de la Bugellerie
86000 Poitiers
Le ven. 14 mars 2025 à 20:49, Sylvain Ard
<sylvain....@gmail.com<mailto:sylvain....@gmail.com>> a écrit :
yes but H3 can't simplify the job ?
Sylvain Ard
0549507724
0778380991
sylvain....@gmail.com<mailto:sylvain....@gmail.com>
https://www.sylvain-ard.fr<http://sylvain-ard.fr/>
Entreprise individuelle SIRET : 80079243400022
Appt 26 Bât A Résidence Le Patio
83 rue de la Bugellerie
86000 Poitiers
Le ven. 14 mars 2025 à 20:48, Brent Wood
<brent.w...@niwa.co.nz<mailto:brent.w...@niwa.co.nz>> a écrit :
Hi,
Off the top of my head...
Postgis can easily cope with millions of records. Given the points are there,
use that to create the hexagons as required - this can be done using
https://postgis.net/docs/ST_HexagonGrid.html
If you set up your QGIS project with preset zoom levels, and create a set of
appropriately sized hexagons for each zoom level, then populate a column with
the number of points it contains.
There will be some devilish details - as you zoom in, the polygon's real area
will get smaller, so will contain less points. A fixed symbology will therefore
change the colour range with the scale.
Calculating a normalised value, perhaps something like instead of the no of
points, you calculate a normalised value - the hex at each scale with the most
points gets 100, the rest get a value based on the no of points/the max for
that scale, so all polygon datasets will have colours set to values between 0
and 100. All doable with SQL.
Do all the data stuff in Postgis, then just plot in QGIS.
There are various ways to set QGIS up for this, but one reasonably
straightforward way that should work is having a separate hexagon layer for
each scale, with a scale limit on displaying it so that all map scales are
represented by one of the hexagon layers, with the fixed zoom levels/scales,
set the same symbology for each layer, then group them into a single group.
Something to consider anyway...
Cheers
Brent Wood
Principal Technician, Fisheries
NIWA
DDI: +64 (4) 3860529
________________________________
From: QGIS-User
<qgis-user-boun...@lists.osgeo.org<mailto:qgis-user-boun...@lists.osgeo.org>>
on behalf of Sylvain Ard via QGIS-User
<qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org>>
Sent: Friday, March 14, 2025 23:35
To: qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org>
<qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org>>
Subject: [Qgis-user] question
Hello,
I'd like a map with hexagons that are darker the more points there are
underneath. My points are in a PostgreSQL table. If there are no points, there
should be no hexagon. The hexagons have a constant size on the screen, so they
have to be recalculated each time the map is zoomed in. I want a truly
optimized solution because I have millions of points. So, perhaps I could use
QGIS-server.
Please suggest a solution for both the server and client side.
Best regards
PS : my map must be like GBIF maps
Sylvain Ard
0549507724
0778380991
sylvain....@gmail.com<mailto:sylvain....@gmail.com>
https://www.sylvain-ard.fr<http://sylvain-ard.fr/>
Entreprise individuelle SIRET : 80079243400022
Appt 26 Bât A Résidence Le Patio
83 rue de la Bugellerie
86000 Poitiers
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png]
<https://www.niwa.co.nz/>
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz/>
Facebook<https://www.facebook.com/nzniwa>
LinkedIn<https://www.linkedin.com/company/niwa>
Twitter<https://twitter.com/niwa_nz>
Instagram<https://www.instagram.com/niwa_science>
YouTube<https://www.youtube.com/channel/UCJ-j3MLMg1H59Ak2UaNLL3A>
To ensure compliance with legal requirements and to maintain cyber security
standards, NIWA's IT systems are subject to ongoing monitoring, activity
logging and auditing. This monitoring and auditing service may be provided by
third parties. Such third parties can access information transmitted to,
processed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may
contain information that is confidential or subject to legal professional
privilege. If you receive this email in error please immediately notify the
sender and delete the email.
_______________________________________________
QGIS-User mailing list
QGIS-User@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
