Différences

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

--- database:requetes-sql-utiles [2021/10/13 14:19] – 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 60: / Ligne 61: @@
     AND la.id_type = ref_geo.get_id_area_type_by_code('COM')
 LIMIT 100;
+</code>
+===== 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>
+===== Déterminer s'il manque des index =====
+Source: https://salayhin.wordpress.com/2018/01/02/finding-missing-index-in-postgresql/
+<code sql>
+SELECT
+	schemaname,
+	relname,
+	seq_scan - idx_scan AS too_much_seq,
+	CASE
+		WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index ?'
+		ELSE 'OK'
+	END,
+	pg_relation_size(CONCAT(schemaname, '.', relname)::regclass) AS rel_size,
+	seq_scan, idx_scan
+FROM pg_stat_all_tables
+WHERE pg_relation_size(CONCAT(schemaname, '.', relname)::regclass) > 80000
+ORDER BY too_much_seq DESC;
+</code>
+<code sql>
+SELECT
+  x1.table_in_trouble,
+  pg_relation_size(x1.table_in_trouble) AS sz_n_byts,
+  x1.seq_scan,
+  x1.idx_scan,
+  CASE
+	WHEN pg_relation_size(x1.table_in_trouble) > 500000000
+		THEN 'Exceeds 500 megs, too large to count in a view. For a count, count individually'::text
+	ELSE count(x1.table_in_trouble)::text
+  END AS tbl_rec_count,
+  x1.priority
+FROM
+  (
+    SELECT
+      (schemaname::text || '.'::text) || relname::text AS table_in_trouble,
+      seq_scan,
+      idx_scan,
+      CASE
+	      WHEN (seq_scan - idx_scan) < 500
+	        THEN 'Minor Problem'::text
+	      WHEN (seq_scan - idx_scan) >= 500 AND (seq_scan - idx_scan) < 2500
+	        THEN 'Major Problem'::text
+	      WHEN (seq_scan - idx_scan) >= 2500
+	        THEN 'Extreme Problem'::text
+	      ELSE NULL::text
+      END AS priority
+    FROM
+      pg_stat_all_tables
+    WHERE
+      seq_scan > idx_scan
+      AND schemaname != 'pg_catalog'::name
+    AND seq_scan > 100) x1
+GROUP BY
+  x1.table_in_trouble,
+  x1.seq_scan,
+  x1.idx_scan,
+  x1.priority
+ORDER BY
+  x1.priority DESC,
+  x1.seq_scan;
+</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>