IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

GEOGRAPHY ou 2 colonnes longitude/latitude


Sujet :

Développement SQL Server

  1. #1
    Membre confirmé Avatar de joKED
    Profil pro
    Imposteur en chef
    Inscrit en
    février 2006
    Messages
    335
    Détails du profil
    Informations personnelles :
    Âge : 39
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Imposteur en chef

    Informations forums :
    Inscription : février 2006
    Messages : 335
    Points : 452
    Points
    452
    Par défaut GEOGRAPHY ou 2 colonnes longitude/latitude
    Bonjour à tous,

    Je travaille actuellement sur un système ayant de gros besoins de reverse geocoding (transformer une paire de coordonnées longitude/latitude en adresse postale).
    Le volume de requête est aux alentours de 250k par jour, ce qui n'est pas négligeable.
    Une requête de reverse geocoding (au travers d'une api web externe) prend en moyenne 150ms.
    Actuellement, j'utilise différents mécanismes permettant de minimiser le nombre de requêtes de reverse geocoding, ce qui permet d'avoir des performances acceptables.

    Utilisant SQL Server 2014, je réfléchis à une solution de mise en cache de ces données, mais je souhaite m'assurer que les performances soient au rendez vous sans pour autant mettre à genoux notre serveur.
    Je me doute que d'un point de vue performances, il va être nécessaire d'avoir une structure cohérente, d'utiliser les bons types de donnée pour le job, et de poser les bons indexes.
    Attention, je ne suis qu'un simple développeur, et n'ai probablement pas les bons réflexes qu'un DBA aurait. Mais j'essaye d'apprendre et de comprendre.

    Je pensais donc utiliser une table telle que celle ci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    CREATE TABLE T_GEOCACHE (
    	REQ_ID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    	LONGI DECIMAL(9,6) NOT NULL,
    	LATI DECIMAL(9,6) NOT NULL,
    	FORMATTED_ADDRESS VARCHAR(MAX) NOT NULL
    )
    Les longitude/latitude seront stockées sous forme de DECIMAL(9,6).
    En principe, pour des coordonnées au format WGS84 (décimales), il n'est pas nécessaires d'aller au delà de 4 chiffres après la virgule (précision à 11.1m). Toutefois, je souhaite aller jusqu'à 5 (requirement du cahier des charges).

    Pour une recherche simple, j'ai juste à effectuer une requête telle que :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT * FROM T_GEOCACHE WHERE LONGI = @myLongi AND LATI = @myLati
    Pour une recherche avec plusieurs points, je peux créer un type table utilisateur du genre :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE TYPE TBL_COORD AS TABLE (
    	LONGI DECIMAL(9,6) NOT NULL,
    	LATI DECIMAL(9,6) NOT NULL
    )
    Puis, pour la recherche :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    DECLARE @MonTypeTable TBL_COORD
    INSERT INTO @MonTypeTable (LONGI,LATI) VALUES (...)
    INSERT INTO @MonTypeTable (LONGI,LATI) VALUES (...)
    ...
     
    SELECT TM.SOMEID, TG.*
    FROM T_GEOCACHE TG
    LEFT OUTER JOIN @MonTypeTable M ON TG.LONGI = M.LONGI AND TG.LATI = M.LATI
    Jusqu'ici, tout va bien (ou pas ?).

    La recherche s'effectuant sur les couples longitude/latitude, je pensais créer un index du genre :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE NONCLUSTERED INDEX [IX_COORD] ON [dbo].[T_GEOCACHE]
    (
    	[LONGI] ASC,
    	[LATI] ASC
    )
    Puisque la recherche se fera sur ces deux valeurs systématiquement, ça me semble cohérent. Est ce la bonne stratégie ?


    Autre possibilité : Utiliser un type GEOGRAPHY (puisque ma version de SQL Server le supporte).
    Dans le cadre de mon cas d'utilisation, est ce une bonne stratégie ?
    Je me pose cette question notamment car :
    - La recherche va se limiter à des comparaison de valeurs (longitude et latitude), et n'utilisera aucune des fonctions associées aux types GEOGRAPHY (du genre, STIntersect, et autres...)
    - Le type GEOGRAPHY est à priori (pour le peu que j'en ai lu) stocké en tant que VARBINARY(MAX). Vu le volume important de lignes qui seront insérées, c'est un point à prendre en compte

    Mes questions pour résumer :

    - Est il intéressant, pour mon cas d'utilisation, d'utiliser un type GEOGRAPHY ?
    - Si j'utilise un simple combo longitude/latitude, ma structure de données est elle ok, et la création d'index telle que présentée a t'elle du sens ?
    - Avez vous des remarques particulières et/ou des points sur lesquels je devrais être particulièrement vigilant ?

    Je vous remercie par avance pour votre aide.
    Tant va la cruche à l'eau qu'à la fin y'a plus d'eau.

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 815
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 815
    Points : 49 304
    Points
    49 304
    Billets dans le blog
    1
    Par défaut
    Je stockerait en GEOMETRY (plutôt qu'en GEOGRAPHY) et exposerait les cordonnées longitude/latitude sous forme de colonnes calculées persistante.

    Comme ça tu as les deux...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. Réponses: 7
    Dernier message: 04/07/2011, 11h55
  2. Conversion adresse/longitude latitude
    Par Alex06 dans le forum 4D
    Réponses: 8
    Dernier message: 20/07/2010, 15h28
  3. Réponses: 6
    Dernier message: 03/03/2008, 18h39
  4. créer Longitude/latitude en (°,',") dans une table
    Par guestCam dans le forum PostgreSQL
    Réponses: 7
    Dernier message: 01/08/2007, 00h16
  5. distance entre deux ville à partir de longitude/latitude
    Par sami_c dans le forum Algorithmes et structures de données
    Réponses: 8
    Dernier message: 09/02/2007, 20h57

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo