Comment utiliser les spatiales pour rechercher un rayon de codes postaux?

c# entity-framework entity-framework-6 sql-server-2012

Question

Contexte

J'écris une application qui trouve des événements dans un certain rayon du code postal. Vous pouvez penser à cela comme à Ticketmaster, où vous entrez votre code postal et tous les concerts dans le rayon x apparaissent.

J'ai une table de base de données qui a le code postal et la latitude et la longitude de chaque code postal. J'ai aussi une table 'EventListings' où chaque 'Event' a un champ ZipCode.

Problème

Actuellement, j'utilise la formule Haversine dans une requête Linq-to-Entities dans ma couche de services pour rechercher les événements situés dans le rayon. En ce moment, je l'utilise comme filtre dans la clause where. J'aimerais également le placer dans la clause select afin que je puisse indiquer sur le site Web "c'est à 4,6 miles de distance", etc.

Je ne peux pas déplacer ce code dans une méthode C # distincte, car Linq-to-Entities se plaindra de l'impossibilité de le convertir en sql, ce qui me permet également de dupliquer l'intégralité de la formule dans l'instruction select. C'est très moche. J'ai essayé de le réparer.

Ce que j'ai essayé

J'ai édité l'entité et ajouté une propriété scalaire spéciale de "DistanceFromOrigin". J'ai ensuite créé une procédure stockée qui a ramené toutes les données de l'entité, ainsi qu'une valeur codée en dur (à des fins de test) pour le nouveau champ "DistanceFromOrigin".

Je me suis alors rendu compte que je ne pouvais pas dire au framework d'entités d'utiliser mon sproc pour son instruction select sur l'entité EventListings ... Phil a suggéré les spatiales, c'est donc ce que j'ai choisi.

Question

Comment utiliser Spatials pour rechercher des événements dans un rayon de codes postaux?

Réponse acceptée

Donc, en utilisant la suggestion de Phil, j'ai réécrit beaucoup de cela en utilisant des spatiales. Cela a fonctionné à merveille, vous avez juste besoin de .NET4.5, EF5 + et du serveur SQL (2008 et au-dessus, je crois). J'utilise EF6 + Sql Server 2012.

Installer

La première étape a été d'ajouter une colonne Geography à la table EventListings de ma base de données (cliquez dessus avec le bouton droit de la souris -> Conception). J'ai nommé le mien

entrez la description de l'image ici

Ensuite, comme j'utilise l'EDM avec la base de données d'abord, j'ai dû mettre à jour mon modèle pour utiliser le nouveau champ que j'ai créé. Ensuite, j'ai reçu une erreur indiquant que je ne pouvais pas convertir Geography en double. J'ai donc résolu le problème en sélectionnant la propriété Location dans l'entité, en accédant à ses propriétés et en changeant son type de double en Geography :

entrez la description de l'image ici

Interrogation dans un rayon et ajout d'événements avec des emplacements

Ensuite, tout ce que vous avez à faire est d’interroger votre collection d’entités comme ceci:

 var events = EventRepository.EventListings
                             .Where(x => x.Location.Distance(originCoordinates) * 0.00062 <= radiusParam); 

La méthode d'extension Distance obtient la distance de l'objet actuel à "l'autre" objet que vous transmettez. Cet autre objet est de type DbGeography . Vous appelez juste une méthode statique et cela crée un de ces chiots, puis ajoutez simplement votre Longitude et Latitude sous forme de point:

DBGeography originCoordinates = DBGeography.fromText("Point(" + originLongitude + " " + originLatitude + ")");

Ce n'est pas comme ça que j'ai créé mon origineCoordinates. J'ai téléchargé une base de données séparée contenant une liste de tous les codes postaux et de leurs latitudes et longitudes. J'ai ajouté une colonne de type Geography à cela également. Je montrerai comment à la fin de cette réponse. J'ai ensuite interrogé le contexte de code postal pour obtenir un objet DbGeography à partir du champ Location du tableau ZipCode.

Si l'utilisateur souhaite une origine plus spécifique qu'un simple code postal, je passe un appel à l'API Google Maps (le géocodage est plus précis) et j'obtiens la latitude et la longitude de l'adresse spécifique de l'utilisateur via un service Web. les valeurs de latitude et de longitude dans la réponse.

J'utilise les API Google lorsque je crée un événement également. Je viens de définir la variable d'emplacement comme ceci avant d'ajouter mon entité à EF:

someEventEntity.Location = DBGeography.fromText("Point(" + longitudeFromGoogle+ " " + latitudeFromGoogle + ")");

Autres trucs et astuces et dépannage

Comment ai-je obtenu la méthode d'extension de distance?

Pour obtenir la méthode d'extension Distance vous devez ajouter une référence à votre projet: System.Data.Entity Ensuite, vous devez ajouter using: using System.Data.Entity.Spatial; à votre classe.

La méthode d'extension Distance renvoie la distance avec une unité de mesure de mètres (vous pouvez la changer je pense, mais c'est la valeur par défaut). Ici, mon paramètre de rayon était en miles, j'ai donc fait quelques calculs pour convertir.

Remarque : Il existe une classe DBGeography dans System.Data.Spatial . C'est le mauvais et cela ne fonctionnerait pas pour moi. Beaucoup d'exemples que j'ai trouvés sur Internet ont utilisé celui-ci.

Comment convertir Lat / Long en colonne Géographie

Donc, si vous étiez comme moi et que vous avez téléchargé une base de données de codes postaux avec toutes les colonnes Latitude et Longitude , puis que vous vous êtes rendu compte qu'il n'y avait pas de colonne Géographie ... cela pourrait aider:

1) Ajoutez une colonne Location à votre table. Définissez le type sur Géographie.

2) Exécutez le sql suivant

UPDATE [ZipCodes]
SET Location = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

Comment afficher les détails d'un élément de géographie

Ainsi, lorsque vous interrogez votre table EventListings dans Sql Server Management Studio après avoir inséré des éléments DbGeography, la colonne Location contient une valeur hexadécimale telle que: 0x1234513462346. Ce n'est pas très utile lorsque vous voulez vous assurer que les valeurs correctes ont été insérées.

Pour voir réellement la latitude et la longitude de ce champ, vous devez l'interroger comme ceci:

SELECT Location.Lat Latitude, Location.Long Longitude
FROM [EventListings]


Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi