Discussion :

[joKED]

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.

[SQLpro]

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 +