[SpatiaLite] tirage aléatoire dans une base de donnée

Pour apprendre les bases de SpatiaLite, direction le cookbook en Francais: ici

Données:
- batiments (id, surface_sol, geometry) - type: polygon - + Index Spatial Rtree
- riviere (id, nomn geometry) - type: ligne -

Objectif: Tirer aléatoirement 5 batiments ayant une surface au sol > 100m² et situés à moins de 2500m du cours d'eau

Solution:
SELECT
a.*
FROM batiments as a
JOIN riviere as b ON (
ST_Distance(a.geometry,b.geometry)<=2500
AND
a.rowid IN (
SELECT pkid from idx_batiments_geometry
WHERE pkid MATCH RTreeintersects(
MBRminX(b.geometry)-2500,
MBRminY(b.geometry)-2500,
MBRmaxX(b.geometry)+2500,
MBRmaxY(b.geometry)+2500,
))
)
WHERE
a.surface>=100
ORDER BY random()
LIMIT 5

Quelques explications:
Requête SELECT ... FROM ... WHERE ... classique permettant de selectionner les bâtiments de plus de 100 m². La clause JOIN...ON... permet de joindre la table riviere, en filtrant uniquement les bâtiments situés à moins de 2500m d'un cours d'eau via st_distance(). Le code SQL en rouge , facultatif, utilise l'index spatial R*Tree de la table batiments afin de préfiltrer les batiments intersectant le MBR des cours d'eau, élargis de 2500 m, et ainsi améliorer la performance de la requête.
Le point le plus important ici réside dans l'utilisation de la fonction ORDER BY random() : ceci va permettre d'afficher les lignes du résultat dans un ordre aléatoire.
La clause LIMIT 5 permet de ne selectionner que les 5 premiers résultats.


L'association de GROUP BY random() et LIMIT x va permettre de simuler un tirage aléatoire de x lignes d'une table. Ceci est particulièrement intéréssant pour préparer des échantillonnages.
L'un des principaux avantages de réaliser le tirage aléatoire directement par SQL, est de pouvoir specifier des critères sur la population initiale.

Encore plus fort ....

Tentons maintenant de réaliser en une seule étape un échantillonnage stratifié.
Parmis les bâtiments situés à moins de 2500m d'un cours d'eau, je veux échantillonner:
- 5 batiments au hasard parmis les bâtiments de plus de 100 m² (>=100m²)
- 2 batiments au hasard parmis les bâtiments de moins de 100 m² (<100m²)

Solution:
SELECT id FROM
( ...requete précédente... )
UNION
SELECT id FROM
( ...requete précédente, en remplaçant >=100 par <100 et LIMIT 5 by LIMIT 2 ... )

La clause UNION va permettre de fusionner les résultats des deux requêtes. Ainsi, votre résultat se composera de l'id de l'ensemble des batiments à échantillonner.