SQL Server 2008 : Ma démonstration des données spatiales en plénière des techdays le 12 février... (4/4)

La deuxième partie de cette démonstration a été réalisée à l'aide d'une application adaptée à la France, à partir d'une idée de John O'Brien. Regis et moi avons passé quelques temps (mais au final pas plus de 2 jours) pour adapter ce code initial à notre besoin.

L'architecture peut être représentée par le schéma suivant :

La base de données utilisée ici est celle des communes de France (incluant les DOM-TOM) avec leurs coordonnées géographiques.

Virtual Earth n°1 : les préfectures en 2D puis en 3D

Les punaises bleues (ou oranges ci-après) correspondent donc à des données stockées dans SQL Server 2008 qui sont passées à Virtual Earth pour affichage. Cela permet de profiter du fond de cartes et des options de zoom et de 2D/3D offerts par Virtual Earth.

Virtual Earth n°2 : les communes dans un rayon de 30 km autour de Marseille...

Ensuite nous avons utilisés les 2 champs situés en bas de page pour sélectionner uniquement les communes situées dans un rayon de 30 km autour de Marseille.

L'utilisation d'une requête de ce type est évidente : cela peut par exemple être utilisé sur un site d'emploi pour proposer uniquement des annonces qui sont situées à moins de 30km de votre domicile.

Voici la procédure stockée qui est appelée pour sélectionner les communes à afficher (via un web service) :

ALTER PROCEDURE [dbo].[GetPostcodesFRV2]
@km float, @mode int, @Dept varchar(2)
AS
BEGIN
SET NOCOUNT ON;

IF (@mode = 1) -- By departement
BEGIN
IF (@dept='0') -- All the prefectures
SELECT TOP 500
cast ([code postal] as varchar) as Title,
[Nom] as Description,
[Geo] as [Location]
FROM [dbo].[VillesFR] V, [Préfectures] P
WHERE V.[code Insee] =P.[code Insee]
ELSE -- All cities in a dept
SELECT TOP 500
cast ([code postal] as varchar) as Title
, [Nom] as Description
, [Geo] as [Location]
FROM [dbo].[VillesFR]
WHERE [Code postal] >= cast (@Dept AS int)*1000
AND [Code postal] < (cast (@Dept AS int)+1)*1000
END
ELSE IF (@mode = 2) -- By Circle around prefecture
BEGIN
IF (@dept='0') -- Circles around all the prefectures ?
SELECT TOP 500
cast ([code postal] as varchar) as Title,
[Nom] as Description,
[Geo] as [Location]
FROM [dbo].[VillesFR] V, [Préfectures] P
WHERE V.[code Insee] =P.[code Insee]
ELSE -- All cities around a prefecture
BEGIN
DECLARE @p geography;
SET @p = (SELECT Geo
FROM [dbo].[VillesFR] V, [Préfectures] P
WHERE V.[code Insee] =P.[code Insee]
AND (cast (@Dept AS int)*1000)=
[Code postal]);
SET @p= @p.STBuffer(@km*1000);
SELECT TOP 500
cast ([code postal] as varchar) as Title
, [Nom] as Description
, [Geo] as [Location]
FROM VillesFR
WHERE Geo.STIntersects(@p) = 1;
END
END
END

Les résultats sont ici :

On peut également mettre en évidence des zones particulières (sous forme de polygones) :

et y rattacher des affichettes et des liens vers d'autres sites :

Et pour finir en cliquant sur ces trois points, retour au palais des congrès de Paris :