Cannibalisation

Bien sûr visuellement nous avons déjà une bonne idée des cannibalisations à l'œuvre, mais il est intéressant d'être capable de les lister.

Identifions-les grâce aux Virtal Layers de QGIS et au SQL spatial à notre disposition : outil Database/DB Manager/Virtal Layers/isochrones. .

D'abord un peu de SQL très simple pour lister les identifiants des magasins de nos 308 zones isochrones, en guise d'entraînement.

SELECT id_mag
FROM isochrones

Testons maintenant la fonction ST_INTERSECTS qui nous permet d'intersecter les géométries (du SQL spatial donc) :

SELECT id_mag
FROM isochrones
WHERE ST_INTERSECTS(isochrones.geometry,isochrones.geometry)

Humm... Chaque zone s'intersecte elle-même, et ne récupère même pas les intersections croisées (seulement 308 résultats).

Ajoutons donc une jointure réflexive, pour croiser notre table sur elle-même. En même temps, nous ajouterons des alias à nos tables, afin d'y voir clair dans notre jointure.

SELECT a.id_mag AS a_id, b.id_mag AS b_id
FROM isochrones AS a, isochrones AS b
WHERE ST_INTERSECTS(a.geometry,b.geometry)

Là oui ! En revanche l'intersection conserve encore les zones s'intersectant elles-mêmes. Arrrggh !

Ajoutons donc une comparaison par identifiant pour exclure les zones qui s'intersectent elles-mêmes.

SELECT a.id_mag AS a_id, b.id_mag AS b_id
FROM isochrones AS a, isochrones AS b
WHERE ST_INTERSECTS(a.geometry,b.geometry)
AND a_id <> b_id

Nous commençons à arriver à quelque chose. Observez vos résultats et faites une sélection de quelques magasins pour vérifier. Exemple, dans Select By Expression :

"id_mag" = 'd186766319'
OR
"id_mag" = 'd71408480'
OR
"id_mag" = 'd220108727'

Il semble que nous soyons parvenus à nos fins. Mais chaque zone intersectante apparaît 1 fois par magasin intersecté, c'est peu lisible.

Mettons notre requête d'intersection dans une sous-requête, et ajoutons une requête de regroupement pour compter les zones intersectantes par magasins.

SELECT a_id, GROUP_CONCAT(b_id) AS b_id
FROM
(SELECT a.id_mag AS a_id, b.id_mag AS b_id
FROM isochrones AS a, isochrones AS b
WHERE ST_INTERSECTS(a.geometry,b.geometry)
AND a_id <> b_id)
GROUP BY a_id

C'est mieux. Nous pouvons encore aussi ajouter un comptage des magasins cannibalisés par un autre :

SELECT a_id, GROUP_CONCAT(b_id) AS b_id, COUNT(b_id) AS cannibalized
FROM
(SELECT a.id_mag AS a_id, b.id_mag AS b_id
FROM isochrones AS a, isochrones AS b
WHERE ST_INTERSECTS(a.geometry,b.geometry)
AND a_id <> b_id)
GROUP BY a_id

Enfin, vérifions que chaque magasin intersecté possède sa propre ligne :

SELECT a_id, GROUP_CONCAT(b_id) AS b_id, COUNT(b_id) AS cannibalized
FROM
(SELECT a.id_mag AS a_id, b.id_mag AS b_id
FROM isochrones AS a, isochrones AS b
WHERE ST_INTERSECTS(a.geometry,b.geometry)
AND a_id <> b_id)
GROUP BY a_id
HAVING a_id IN ('d100939496', 'd196253319', 'd8191733594')

C'est bien le cas (notez le HAVING, qui vient remplacer le WHERE dans les requêtes de regroupement GROUP BY).

Nous pouvons donc dire que sur nos 308 magasins, à partir de zones isochrones en voiture de 15 minutes, nous avons 200 magasins en proie à une cannibalisation, et pour chacun le décompte des magasins impliqués.

Nous pourrions aussi nous amuser à compter les cas de cannibalisation, en regroupant dans une seule ligne les magasins cannibalisés entre eux. Pour cela il suffirait de fusionner les zones s'intersectant (en conservant les doublons : une zone intersectant 2 zones qui elles-mêmes ne s'intersectent pas, devra être liée à 2 entités fusionnées distinctes). Puis d'intersecter les magasins appartenant à ces entités de fusion. Tout cela pourrait être fait en SQL spatial, je vous laisse vous amuser...

Bien sûr la notion de cannibalisation est contextuelle, et devrait être enrichie des zones de chalandise cumulées, du peuplement territorial, des densités de population, de la taille des magasins, d'enquêtes de terrain... Mais c'est un bon début !

Liens ou pièces jointes

isochrones15m.zip	[Les 308 zones isochrones fusionnées]	979 Ko
Code complet de création des zones isochrones avec le service ORS	[code Python]	2 Ko
isochrones_ORS.zip	[Les 308 fichiers GEOJSON des zones isochrones]	786 Ko
Export OSM des magasins de sports français le 27 décembre 2020	[key=shop ; value=sport ; in=France]	0 Ko