Différences

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

--- database:requetes-sql-utiles [2021/10/15 13:42] – [Déterminer s'il manque des index] jpmilcent
+++ database:requetes-sql-utiles [2025/12/03 14:48] (Version actuelle) – [Différents calculs du rayon moyen d'un polygone] jpmilcent
@@ Ligne 40: / Ligne 40: @@
 </code>
-===== Calculer le rayon du cercle comprenant un polygon (communes) =====
+===== Calculer le rayon du cercle comprenant un polygone (communes) =====
+{{ :database:screenshot_20211013_161602.png?400|}}
 <code sql>
 SELECT
@@ Ligne 61: / Ligne 62: @@
 LIMIT 100;
 </code>
-{{:database:screenshot_20211013_161602.png?400|}}
+===== Différents calculs du rayon moyen d'un polygone =====
+{{ :database:screenshot_20211018_102010.png?400|}}
+Il est possible d'utiliser :
+  - la fonction ''ST_MinimumBoundingRadius()'' de Postgis (trait oblique): <code sql>round(radius(ST_MinimumBoundingRadius(geom)))</code>
+  - la distance moyenne du centroïde du polygone a chaque point constituant son périmètre (trait vertical) : <code sql>round(AVG(ST_Distance(st_centroid(la.geom), perimeters.geom)))</code>
+  - le calcul du rayon d'un cercle à partir de son aire (trait horizontal) : <code sql>round(|/(st_area(geom)/pi()))::int</code>
+La première méthode retourne un rayon plus grand que la seconde méthode, en moyenne la plus petite valeur obtenue étant avec le calcul du rayon d'un cercle à partir de son aire...
+Nous avons retenu le calcul n°2.
+<code sql>
+SELECT
+    la.area_name,
+    la.area_code,
+    round(AVG(ST_Distance(st_centroid(la.geom), perimeters.geom))) AS "precision_avgdistance",
+    round(|/(st_area(la.geom)/pi()))::int AS "precision_calculaire",
+    round(radius(ST_MinimumBoundingRadius(la.geom))) AS "precision_minboundingradius",
+    la.geom,
+    st_centroid(la.geom) AS centroid,
+    center(ST_MinimumBoundingRadius(la.geom)) AS centre,
+    ST_MinimumBoundingCircle(la.geom) AS cercle,
+    ST_LongestLine(center(ST_MinimumBoundingRadius(la.geom)), ST_MinimumBoundingCircle(la.geom)) AS rayon_minboundingradius,
+    ST_MakeLine(
+    center(ST_MinimumBoundingRadius(la.geom)),
+        ST_SetSRID(
+            ST_MakePoint(
+                ST_X(center(ST_MinimumBoundingRadius(la.geom))) + round(|/(st_area(la.geom)/pi()))::int,
+                ST_Y(center(ST_MinimumBoundingRadius(la.geom)))
+            ),
+        )
+    ) AS rayon_calculaire,
+    ST_MakeLine(
+    	center(ST_MinimumBoundingRadius(la.geom)),
+    	ST_SetSRID(
+    		ST_MakePoint(
+    			ST_X(center(ST_MinimumBoundingRadius(la.geom))),
+	    		ST_Y(center(ST_MinimumBoundingRadius(la.geom))) + round(AVG(ST_Distance(st_centroid(la.geom), perimeters.geom)))
+    		),
+    	)
+    ) AS rayon_avgdistance
+FROM ref_geo.l_areas AS la JOIN (
+	SELECT id_area, (ST_DumpPoints(geom)).*
+	FROM ref_geo.l_areas
+	WHERE id_type = ref_geo.get_id_area_type('COM')
+) AS perimeters
+	ON (la.id_area = perimeters.id_area)
+WHERE la.id_type = ref_geo.get_id_area_type('COM')
+GROUP BY la.id_area, la.geom, la.area_name, la.area_code
+ORDER BY la.id_area
+LIMIT 10 ;
+</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 124: / Ligne 215: @@
 </code>
+===== Déterminer les groupes d'identifiant contigu =====
+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) AS downloaded,
+	td.nbr AS to_download
+FROM (
+		SELECT
+			grp,
+			MIN(id) AS "min",
+			MAX(id) AS "max"
+		FROM (
+			SELECT
+				id,
+				SUM(rst) OVER (ORDER BY id) AS grp
+			FROM (
+				SELECT
+					id_synthese AS id,
+					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
+					LEFT JOIN gn2pg_flavia.data_json AS dj
+						ON ispi.id_synthese = dj.id_data
+				WHERE dj.id_data IS NULL
+				ORDER BY ispi.id_synthese ASC
+			) AS t
+		) AS t
+		GROUP BY grp
+		ORDER BY 1
+	) AS d
+	LEFT JOIN gn2pg_flavia.data_json AS dj
+		ON dj.id_data > d.min AND dj.id_data < 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>
+Résultats :
+<code>
+|grp|min       |max       |downloaded|to_download|
+|---|----------|----------|----------|-----------|
+|1  |5 839 897  |6 467 981  |3 255     |581 087    |
+|2  |9 404 094  |9 576 583  |0         |172 488    |
+|3  |15 444 377 |15 455 826 |2 454     |2 773      |
+|4  |15 609 091 |15 609 795 |703       |703        |
+|5  |16 335 991 |16 336 391 |1         |52         |
+|6  |16 640 640 |16 641 280 |290       |639        |
+</code>