Différences

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

--- database:requetes-sql-utiles [2021/10/18 08:21] – [Différents calcul du rayon moyen d'un polygone] jpmilcent
+++ database:requetes-sql-utiles [2024/05/25 07:44] (Version actuelle) – [Déterminer les groupes d'identifiant contigu] 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 calcul du rayon moyen d'un polygone =====
-{{ :database:screenshot_20211018_102010.png?600|}}
+===== 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 : <code sql>round(radius(ST_MinimumBoundingRadius(geom)))</code>
+  - la fonction ''ST_MinimumBoundingRadius()'' de Postgis (trait oblique): <code sql>round(radius(ST_MinimumBoundingRadius(geom)))</code>
-  - le calcul du rayon d'un cercle à partir de son aire : <code sql>round(|/(st_area(geom)/pi()))::int</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>
-La première méthode retourne un rayon plus grand que cela seconde méthode...
+  - 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
-    round(|/(st_area(la.geom)/pi()))::int AS "precision_aire",
+    la.area_name,
-    round(radius(ST_MinimumBoundingRadius(la.geom))) AS "precision_bound_radius",
+    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_bound_radius,
+    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))) + round(|/(st_area(la.geom)/pi()))::int,
+    			ST_X(center(ST_MinimumBoundingRadius(la.geom))),
-	    		ST_Y(center(ST_MinimumBoundingRadius(la.geom)))
+	    		ST_Y(center(ST_MinimumBoundingRadius(la.geom))) + round(AVG(ST_Distance(st_centroid(la.geom), perimeters.geom)))
     		),
     	)
-    ) AS rayon_aire,
+    ) AS rayon_avgdistance
-    st_centroid(la.geom) AS centroid,
+FROM ref_geo.l_areas AS la JOIN (
-    la.geom,
+	SELECT id_area, (ST_DumpPoints(geom)).*
-    la.area_name
+	FROM ref_geo.l_areas
-FROM ref_geo.l_areas AS la
+	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')
-LIMIT 100;
+GROUP BY la.id_area, la.geom, la.area_name, la.area_code
+ORDER BY la.id_area
+LIMIT 10 ;
 </code>
@@ Ligne 158: / Ligne 181: @@
 </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>