database:requetes-sql-utiles [CBNA SINP]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Requête SQL utiles ======

===== Correspondance entre code INSEE présent dans additional_data et cor_area_synthese =====

Trouver les codes INSEE fournis dans le champ ''additional_data'' attribut ''communeInseeCode'', existant dans la table ''ref_geo.l_areas'' mais qui ne correspondent pas à ceux présent dans la table ''gn_synthese.cor_area_synthee'' :
<code sql>
WITH communes AS (
	SELECT la.id_area, la.area_code AS insee_code, la.area_name
	FROM ref_geo.l_areas AS la 
	WHERE la.id_type = ref_geo.get_id_area_type_by_code('COM')
		AND la."enable" = true
)
SELECT s.unique_id_sinp, s.the_geom_4326, s.additional_data::json->>'communeInseeCode' AS code_insee_json, c.area_name AS area_name_cas, c.insee_code AS code_insee_cas
FROM gn_synthese.synthese AS s 
	LEFT JOIN gn_synthese.cor_area_synthese AS cas 
		ON (s.id_synthese = cas.id_synthese)
	JOIN communes AS c
		ON (cas.id_area = c.id_area)
WHERE s."precision" IS NULL 
	AND s.additional_data::json->>'communeInseeCode' != c.insee_code ;
</code>

Trouver les codes INSEE fournis dans le champ ''additional_data'' attribut ''communeInseeCode'' qui ne correspondent pas à ceux présent dans la table ''gn_synthese.cor_area_synthee'' car ils n'existent pas dans la table ''ref_geo.l_areas'' :
<code sql>
WITH communes AS (
	SELECT la.id_area, la.area_code AS insee_code, la.area_name
	FROM ref_geo.l_areas AS la 
	WHERE la.id_type = ref_geo.get_id_area_type_by_code('COM')
		AND la."enable" = true
)
SELECT DISTINCT s.additional_data::json->>'communeInseeCode' AS code_insee_json
FROM gn_synthese.synthese AS s 
	LEFT JOIN gn_synthese.cor_area_synthese AS cas 
		ON (s.id_synthese = cas.id_synthese)
	JOIN communes AS c
		ON (cas.id_area = c.id_area)
WHERE s."precision" IS NULL 
	AND s.additional_data::json->>'communeInseeCode' != c.insee_code 
	AND s.additional_data::json->>'communeInseeCode' NOT IN (SELECT insee_code FROM communes);
</code>

===== Calculer le rayon du cercle comprenant un polygone (communes) =====
{{ :database:screenshot_20211013_161602.png?400|}}
<code sql>
SELECT
    unique_id_sinp,
    round(radius(ST_MinimumBoundingRadius(la.geom))) AS "precision",
    center(ST_MinimumBoundingRadius(la.geom)) AS rayon,
    ST_MinimumBoundingCircle(la.geom) AS cercle,
    ST_LongestLine(center(ST_MinimumBoundingRadius(la.geom)), ST_MinimumBoundingCircle(la.geom)) AS rayon,
    st_centroid(la.geom) AS centroid,
    la.geom,
    la.area_name 
FROM gn_synthese.synthese AS s 
    LEFT JOIN gn_synthese.cor_area_synthese AS cas 
	    ON (s.id_synthese = cas.id_synthese)
    JOIN ref_geo.l_areas AS la
        ON (cas.id_area = la.id_area)
WHERE s.id_source != gn_synthese.get_id_source_by_name('SI CBN')
    AND s."precision" IS NULL
    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)))
            ),
    	    2154
        )
    ) 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)))
    		),
    		2154
    	)
    ) 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/
<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>