Différences

Ci-dessous, les différences entre deux révisions de la page.

--- database:requetes-sql-utiles [2024/05/24 19:48] – jpmilcent
+++ database:requetes-sql-utiles [2025/12/03 14:48] (Version actuelle) – [Différents calculs du rayon moyen d'un polygone] jpmilcent
@@ Ligne 119: / Ligne 119: @@
 </code>
+===== Calculer le diamètre d'un type de géométrie =====
+Pour le calcul du diamètre, nous utilisons la distance moyenne du centroïde du polygone a chaque point constituant son périmètre :
+<code sql>
+WITH areas AS (
+    SELECT
+        id_area,
+        area_name AS title,
+        area_code AS code,
+        geom
+    FROM ref_geo.l_areas
+    WHERE id_type = ref_geo.get_id_area_type('COM')
+        AND "enable" = True
+),
+perimeters AS (
+    SELECT
+        id_area,
+        (st_dumppoints(geom)).*
+    FROM areas
+),
+diameters AS (
+    SELECT
+        a.id_area,
+        a.title,
+        a.code,
+        (round(avg(st_distance(st_centroid(a.geom), p.geom))) * 2) AS "diameter"
+    FROM areas AS a
+        JOIN perimeters AS p
+            ON (a.id_area = p.id_area)
+    GROUP BY a.id_area, a.title, a.code
+    ORDER BY a.id_area
+)
+SELECT
+    avg("diameter")
+FROM diameters;
+</code>
 ===== Déterminer s'il manque des index =====
 Source: https://salayhin.wordpress.com/2018/01/02/finding-missing-index-in-postgresql/
@@ Ligne 184: / Ligne 218: @@
 Requête SQL permettant de déterminer les groupes de suites d'identifiants non contigü et le nombre d'id compris dedans :
 <code sql>
-SELECT grp, "min", "max", COUNT(id_data)
+SELECT
+	grp,
+	"min",
+	"max",
+	COUNT(id_data) AS downloaded,
+	td.nbr AS to_download
 FROM (
-                SELECT
+		SELECT
-                        grp,
+			grp,
-                        MIN(id) AS "min",
+			MIN(id) AS "min",
-                        MAX(id) AS "max"
+			MAX(id) AS "max"
-                FROM (
+		FROM (
-                        SELECT
+			SELECT
-                                id,
+				id,
-                        SUM(rst) OVER (ORDER BY id) AS grp
+				SUM(rst) OVER (ORDER BY id) AS grp
-                        FROM (
+			FROM (
-                                SELECT
+				SELECT
-                                        id_synthese AS id,
+					id_synthese AS id,
-                                        CASE WHEN COALESCE(LAG(id_synthese + 10000) OVER (ORDER BY id_synthese), 0) < id_synthese THEN 1 END AS rst
+					CASE WHEN COALESCE(LAG(id_synthese + 10000) OVER (ORDER BY id_synthese), 0) < id_synthese THEN 1 END AS rst
-                                FROM gn2pg_flavia.id_synthese_pole_invertebres AS ispi
+				FROM gn2pg_flavia.id_synthese_pole_invertebres AS ispi
-                                        LEFT JOIN gn2pg_flavia.data_json AS dj
+					LEFT JOIN gn2pg_flavia.data_json AS dj
-                                                ON ispi.id_synthese = dj.id_data
+						ON ispi.id_synthese = dj.id_data
-                                WHERE dj.id_data IS NULL
+				WHERE dj.id_data IS NULL
-                                ORDER BY ispi.id_synthese ASC
+				ORDER BY ispi.id_synthese ASC
-                        ) t
+			) AS t
-                ) t
+		) AS t
-                GROUP BY grp
+		GROUP BY grp
-                ORDER BY 1
+		ORDER BY 1
-        ) AS d
+	) AS d
-        LEFT JOIN gn2pg_flavia.data_json AS dj
+	LEFT JOIN gn2pg_flavia.data_json AS dj
-                ON dj.id_data > d.min AND dj.id_data < d.max
+		ON dj.id_data > d.min AND dj.id_data < d.max,
-GROUP BY d.grp, d."min", d."max"
+	LATERAL (
+		SELECT COUNT(id_synthese) AS nbr
+		FROM gn2pg_flavia.id_synthese_pole_invertebres
+		WHERE id_synthese > d.min AND id_synthese < d.max
+	) AS td
+WHERE td.nbr > 0
+GROUP BY d.grp, d."min", d."max", td.nbr
 ORDER BY d.grp;
 </code>
@@ Ligne 216: / Ligne 261: @@
 Résultats :
 <code>
-|grp|min       |max       |count| Done|
+|grp|min       |max       |downloaded|to_download|
-|---|----------|----------|-----|-----|
+|---|----------|----------|----------|-----------|
-|1  |3 215 985  |3 339 850  |0    | X   |
+|1  |5 839 897  |6 467 981  |3 255     |581 087    |
-|2  |5 839 897  |6 467 981  |0    |
+|2  |9 404 094  |9 576 583  |0         |172 488    |
-|3  |9 404 094  |9 576 583  |0    |
+|3  |15 444 377 |15 455 826 |2 454     |2 773      |
-|4  |15 444 377 |15 455 826 |2 454 |
+|4  |15 609 091 |15 609 795 |703       |703        |
-|5  |15 542 006 |15 542 006 |0    |
+|5  |16 335 991 |16 336 391 |1         |52         |
-|6  |15 609 091 |15 609 795 |703  |
+|6  |16 640 640 |16 641 280 |290       |639        |
-|7  |15 843 950 |15 843 951 |0    |
-|8  |16 335 991 |16 336 391 |1    |
-|9  |16 640 640 |16 641 280 |290  |
 </code>