Suite

Trouver la lat long la plus proche d'une lat long d'entrée (SQL Server 2008)


J'ai un nuage de points dans ma base de données (SQL Server 2008 spatial). Cela représente environ 6 millions d'enregistrements. Il y a 3 colonnes : id, value, geom. Quelle est la manière optimale d'obtenir la "valeur" à l'entrée lat long ??

Je suis nouveau dans les requêtes spatiales dans SQL Server 2008. Quelqu'un peut-il publier un exemple simple de recherche du point dans la colonne geom, correspondant ou le plus proche de la latitude d'entrée ?


Ce que vous recherchez est la requête du voisin le plus proche. Regardez les liens suivants, je pense que vous trouverez ce que vous cherchez.

Requête du voisin le plus proche

Les voisins les plus proches

L'optimisation du voisin le plus proche dans SQL Server Denali


Cela utilise la géographie et non la géométrie (si les données sont Lat/Lng, vos données doivent être de type géographie et non de géométrie)

"Le type de données géographiques SQL Server stocke des données ellipsoïdales (terre ronde), telles que les coordonnées GPS de latitude et de longitude."

Pour sélectionner les 5 premiers enregistrements les plus proches à partir d'un point lat/lng (-122,0 37,0), vous pouvez utiliser.

SELECT TOP 5 géographie::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p) FROM marqueurs WHERE géographie::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p) < 25 ORDER BY géographie::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p);

Types spatiaux - géographie

Le type de données géographiques géographiques, la géographie, est implémenté en tant que type de données .NET Common Language Runtime (CLR) dans SQL Server. Ce type représente des données dans un système de coordonnées de la Terre ronde. Le serveur SQL la géographie Le type de données stocke les données ellipsoïdales (terre ronde), telles que les coordonnées GPS de latitude et de longitude.

SQL Server prend en charge un ensemble de méthodes pour le la géographie type de données spatiales. Cela inclut des méthodes sur la géographie qui sont définis par la norme Open Geospatial Consortium (OGC) et un ensemble d'extensions Microsoft de cette norme.

La tolérance d'erreur pour le la géographie les méthodes peuvent être aussi grandes que 1.0e-7 * étendues. Les étendues se réfèrent à la distance maximale approximative entre les points de la la géographieobjet.


Trouver la lat long la plus proche d'une lat long d'entrée (SQL Server 2008) - Systèmes d'Information Géographique

J'ai donc essayé de penser à quelques exemples qui utilisent les données de POI Garmin de mon dernier message. Étant donné que les données de POI se prêtent naturellement aux requêtes du plus proche voisin, j'ai pensé écrire un peu à ce sujet.

La forme générale de la requête du voisin le plus proche est : "Montrez-moi les x restaurants/pubs/gares les plus proches de cet endroit” il s'agit du modèle de requête utilisé par les applications de localisation pour trouver des POI proches de l'emplacement actuel de l'utilisateur, par exemple. Cependant, un autre modèle dont j'ai vu beaucoup moins d'exemples est de savoir comment mettre à jour une table entière d'enregistrements afin de déterminer et de remplir le voisin le plus proche pour chaque ligne de la table (en effet, cette même question s'est posée sur le forum spatial MSDN la semaine dernière seulement).

Dans cet esprit, j'ai pensé montrer un scénario pratique : disons que vous prévoyez d'aller nager dans la piscine locale et, après avoir nagé 50 longueurs, vous aurez probablement un petit creux - peut-être en le besoin de fish 'n' chips. Ainsi, dans cet exemple, je vais d'abord importer les données de POI Garmin pour les piscines et les magasins de poisson et de frites au Royaume-Uni, chacune étant représentée par une instance de point géographique. J'ajouterai ensuite une colonne supplémentaire à la table de la piscine et la remplirai avec le fish and chips le plus proche de chaque piscine.

Pour commencer, créez des tableaux contenant les données des fish and chips et des piscines (téléchargez les données ici). Notez que j'ai inclus un champ de clé primaire IDENTITY dans chaque table, dont j'aurai besoin pour ajouter un index spatial plus tard :

Nous allons maintenant ajouter des colonnes supplémentaires à la table Swimming Pools, que nous remplirons avec des informations sur le fish 'n' chips shop le plus proche :

Maintenant, vous ne besoin pour ajouter un index spatial pour effectuer la requête de mise à jour, mais ce sera beaucoup plus rapide avec un que sans. SQL Server ne peut utiliser qu'un seul index spatial dans une jointure entre des tables, il n'est donc pas nécessaire d'ajouter un index aux deux tables. Nous allons simplement en ajouter un à la table Fish And Chips, comme suit :

Ensuite, exécutez une requête UPDATE pour remplir le Le plus procheFishBar et Distance colonnes, comme suit :

Cette requête a probablement besoin d'explications :

  • En règle générale, vous pouvez vous attendre à utiliser une sous-requête corrélée pour obtenir le fish and chips le plus proche de chaque piscine. Cependant, les sous-requêtes corrélées ne peuvent renvoyer qu'une seule valeur, et je souhaite récupérer à la fois le nom et la distance jusqu'à la barre de poisson la plus proche, j'ai donc utilisé un CROSS APPLY à la place.
  • Dans la fonction en cours d'application, je trie le tableau des fish and chips par ordre croissant de leur distance à la piscine actuelle (ORDER BY FishAndChips.Location.STDistance(SwimmingPools.Location) ASC), puis en sélectionnant le TOP 1 Nom et emplacement (c'est-à-dire le plus proche).
  • Pour rendre la requête efficace, j'ai également ajouté un indice d'index pour utiliser l'index spatial sur la table Fish and Chips - AVEC(index(sidx_FishAndChips)), et j'ai inclus un prédicat supplémentaire, O FishAndChips.Location.STDistance(SwimmingPools.Location) N'EST PAS NULL. Ce prédicat supplémentaire aidera l'optimiseur de requêtes à choisir le plan de requête dédié au voisin le plus proche introduit dans SQL Server 2012 et SQL Azure.
  • Notez que le plan de requête du voisin le plus proche optimisé pour l'index spatial est uniquement disponible dans SQL Server 2012/SQL Azure. Si vous essayez d'exécuter la requête UPDATE comme indiqué ci-dessus dans SQL Server 2008/R2, vous obtiendrez une erreur :

Le processeur de requêtes n'a pas pu produire de plan de requête pour une requête avec une indication d'index spatial. Raison : les index spatiaux ne prennent pas en charge le comparateur fourni dans le prédicat. Essayez de supprimer les indices d'index ou de supprimer SET FORCEPLAN.

Comme indiqué, pour résoudre ce problème, vous devrez supprimer le AVEC(index(sidx_FishAndChips)) indice d'index (et attendez-vous également à ce que votre requête s'exécute beaucoup plus lentement !). Au lieu de cela, vous devrez peut-être essayer l'une des approches alternatives pour trouver efficacement les voisins les plus proches dans SQL Server 2008/R2.

L'exécution de cette requête devrait prendre quelques secondes (ou, si vous ne créez/utilisez pas l'index spatial, quelques minutes), après quoi vous pouvez vérifier les résultats comme suit :

Et là, vous avez le tableau complet des piscines, chacune répertoriée avec le nom et la distance de son fish and chips le plus proche :

Vu qu'il y a moins de 100 mètres entre sortir de l'eau et un sac de chips, je vais penser que je vais faire mon prochain plongeon à la piscine de Farnworth….


Système de référence spatiale

La latitude et la longitude sont considérées comme des valeurs absolues universellement acceptées, mais ce n'est pas le cas. Par exemple, les premiers méridiens, ou zéro degré de longitude, sont de nature assez arbitraire. Il est communément appelé une ligne nord-sud qui passe par Greenwich, Londres, Angleterre, mais en vérité, il y a quatre premiers méridiens différents qui ont été utilisés historiquement et qui passent en fait par Greenwich. Même le premier méridien actuel utilisé par le système GPS (et la plupart des sites de cartographie Internet) est en fait à 102,5 mètres à l'est de la ligne « officielle » qui marque le premier méridien par ailleurs universellement accepté.

Une fois que vous connaissez les points de départ exacts du système de coordonnées qu'un ensemble de données utilise, nous nous retrouvons avec un autre dilemme lorsque nous essayons de calculer la distance entre deux points. En raison de l'action de la rotation de la Terre sur son axe, elle n'est pas réellement de forme sphérique, mais plutôt ellipsoïdale (elle est plus grosse au milieu qu'aux pôles). Cela complique la capacité de calculer avec précision la distance et la surface de manière uniforme, mais ne le rend pas impossible.

Pour mesurer la distance entre deux points du globe, nous devons connaître le rayon de la Terre (c'est-à-dire à quelle distance du centre de la Terre se trouve chaque point). Le problème est qu'en raison des masses terrestres, la Terre n'a pas qu'un seul rayon uniforme qui peut être appliqué à l'échelle mondiale. Si vous mesurez la distance entre deux points distants d'un degré au niveau de la mer, vous obtiendrez un résultat différent de celui de deux points distants d'un degré dans les montagnes, qui peuvent se trouver à 5 000 pieds au-dessus du niveau de la mer.

Ainsi, lorsqu'un emplacement est levé et que des points sont identifiés en termes de coordonnées globales, ceux-ci sont basés sur un modèle de la Terre qui est assez précis pour cette zone localisée, mais peut-être pas aussi précis ailleurs. Les paramètres qui définissent le modèle particulier de la Terre sont connus sous le nom de système de référence spatiale. Il est important de savoir quel système de référence spatiale est utilisé pour un ensemble de données, car vous ne pouvez pas toujours mélanger les données d'un ensemble avec un autre et obtenir des résultats précis.

Avec l'omniprésence des données GPS, je dirais que le système de référence spatiale le plus couramment utilisé aujourd'hui est le WGS84.


Tâches associées

Créer, construire et interroger des instances de géométrie
Décrit les méthodes que vous pouvez utiliser avec des instances du type de données de géométrie.

Créer, construire et interroger des instances géographiques
Décrit les méthodes que vous pouvez utiliser avec des instances du type de données géographie.

Interroger les données spatiales pour le voisin le plus proche
Décrit le modèle de requête commun utilisé pour rechercher les objets spatiaux les plus proches d'un objet spatial spécifique.

Créer, modifier et supprimer des index spatiaux
Fournit des informations sur la création, la modification et la suppression d'un index spatial.


Le rendre plus facile pour les yeux

L'équipe marketing est très heureuse de pouvoir désormais voir où sont réalisées toutes ses ventes, et donc de prendre de meilleures décisions commerciales. Il y a cependant un problème car certaines zones se portent mieux que d'autres et les punaises sont toutes superposées, ce qui rend la carte très difficile à lire. Pour résoudre ce problème et le rendre plus agréable à l'œil, nous allons tirer parti de la fonctionnalité de clustering de l'API. Lorsque le regroupement est activé, Bing Maps regroupera toutes les punaises très proches les unes des autres et affichera une icône spéciale. Cela permet à l'utilisateur de savoir qu'il y a plusieurs punaises à cet emplacement. Si l'utilisateur effectue un zoom avant, l'API commence à dissocier automatiquement toutes les punaises. Dans cet exemple, nous allons utiliser la même requête que le dernier exemple et l'abaisser automatiquement lors du chargement de la page. La seule différence est que nous allons regrouper automatiquement les résultats et laisser les utilisateurs décider s'ils souhaitent voir des punaises groupées ou non groupées. La figure 17 montre la fonction javascript GetMap qui diffère un peu de la précédente.

map.SetCenterAndZoom( nouveau VELatLong(43.79488907226601, -121.014), 6)

Figure 17 : La fonction javascript GetMap qui regroupe automatiquement tous les résultats.

Pour activer le clustering, nous devons ajouter une couche au-dessus de la carte, définissez la configuration de clustering layer&rsquos sur VEClusteringType.Grid et ajoutez toutes les punaises au calque au lieu de la carte. Pour activer ou désactiver le clustering, il suffit de modifier la configuration de clustering de la couche de Grille à Rien. La figure 18 montre comment procéder et les figures 19 et 20 montrent le résultat final.

Figure 18 : Javascript pour le clustering et le dégroupage

Figure 20 : Zoom sur la vue des broches groupées


Trouver la lat long la plus proche d'une lat long d'entrée (SQL Server 2008) - Systèmes d'Information Géographique

Je pense que Microsoft doit montrer plus de preuves à l'appui de cette interprétation de la spécification. Cette interprétation aura un coût très élevé pour les clients en termes d'interopérabilité et de barrière à l'adoption des technologies Microsoft.

Le reste de l'industrie a interprété la norme comme lon/lat si Microsoft veut interpréter cela différemment, ils devraient fournir une explication à toute épreuve justifiant pourquoi ils sont l'une des seules implémentations utilisant l'ordre lat/lon dans SQL.

Je crois comprendre que la majorité des membres de l'OGC ont interprété cette norme comme lon/lat. donc même si Microsoft a raison dans son interprétation, le comité a voté sur la signification de la norme avec leurs claviers. Je pense qu'il serait déraisonnable pour Microsoft de mener cette bataille de conformité en utilisant sa clientèle - cela devrait se produire dans les arrière-salles enfumées des réunions de l'OGC.

Pouvez-vous montrer où ce document, cette page, ce paragraphe et cette phrase, où il est indiqué que la norme s'applique uniquement aux systèmes planaires ou exclut autrement les systèmes géographiques ? J'ai lu le document plusieurs fois et mes collègues l'ont également examiné. rien n'a amené aucun d'entre nous à croire que les crs géographiques soient exclus.

6.1 décrit l'architecture du modèle Géométrie, qui indique clairement que chaque objet géométrique est associé à un "Système de Référence Spatial". cela ne se limite pas aux systèmes de coordonnées du projet.

La section 9 décrit la représentation WKT des systèmes de référence spatiale. Cela couvre à la fois les systèmes géographiques et les systèmes projetés. Nulle part il ne limite la spécification aux seuls systèmes planaires.

L'annexe B.8 de la partie 1 de la SFA indique que la "latitude_d'origine" est "la latitude choisie comme origine des coordonnées y". false-easting/northing ont une définition similaire. Cette section contredit toute notion d'utilisation de l'ordre CRS pour X et Y, car elle définit explicitement X/Y dans un système de coordonnées projetées.

Comment Microsoft résout-il le conflit où un CRS peut projeter la latitude en X au lieu de la longitude ? Vous référez-vous à la définition fournie dans la norme, ou vous référez-vous aux meilleures pratiques ?

Comme cela a été souligné, WKT tel que défini par la spécification SFA n'utilise pas la notation appropriée pour les systèmes non planaires. Donc, si nous devions supposer que Microsoft a raison et que le SFA ne s'applique qu'au planaire, il serait alors non standard de représenter un système de coordonnées géographiques utilisant WKT selon SFA. Dans ce cas, Microsoft devrait s'en remettre aux normes de facto.

Si Microsoft choisit de faire cavalier seul dans cette implémentation, alors je suggérerais de le faire absolument clairque SQL Server implémente la norme OGC 05-126 en utilisant les meilleures pratiques de juin 2005/06-135r1 et que cette norme est nettement différente de l'OGC 99-049.

Dans ce cas, il doit y avoir un mode de compatibilité OGC 99-049 qui implémente la convention lon/lat. Lors de l'utilisation de la norme OGC 99-049, le lon/lat est le correct l'interprétation de la norme, comme en témoignent les certifications OGC sur d'autres produits spatiaux.

Ne pas inclure une implémentation conforme à l'OGC 99-049 créerait un grave problème d'interopérabilité qui empêcherait mon organisation d'adopter SQL Server.

Nous comprenons le problème d'interopérabilité et nous cherchons comment combler ce fossé plus facilement. Permettez-moi d'essayer d'expliquer pourquoi nous avons pris cette décision. Comme point de départ :

Ces éléments pris ensemble semblent jeter le doute sur la bonne façon de gérer cela. Les normes entrent cependant en jeu :

Lors de la séance plénière de clôture du CT pour la réunion de juin 2005, les membres présents ont convenu que :

1. À l'avenir, pour les nouvelles spécifications, les valeurs de coordonnées doivent être répertoriées dans l'ordre des axes tel que spécifié par le système de référence de coordonnées (CRS) référencé.

2. À l'avenir, lorsqu'un RWG travaille sur des modifications d'une spécification adoptée existante liée au CRS et à l'ordre des axes, les valeurs des coordonnées doivent être répertoriées dans l'ordre des axes spécifié par le système de référence de coordonnées (CRS) référencé.

  • Enfin, la liste standard EPSG des SIR, que nous adoptons pour le type géographie, uniformément utilise l'ordre latitude/longitude pour tous les systèmes de coordonnées géographiques.

Nous réalisons que cela causera des problèmes aux gens et nous cherchons des moyens de les atténuer, mais j'espère que cela vous aidera à comprendre notre raisonnement.

Permettez-moi d'abord de répéter que je me rends compte que nous faisons quelque chose d'un peu différent du reste des joueurs et que cela cause de la douleur. Nous écoutons les commentaires que nous recevons à ce sujet, et nous cherchons comment nous pouvons faciliter l'import/export de données entre nous et d'autres systèmes.

Cela dit, je ne suis pas d'accord sur les normes. Je pense qu'ils disent très peu sur les coordonnées géographiques, et le peu qu'ils disent semble nous soutenir.

Tout d'abord, jetons un coup d'œil à l'OGC 99-049. Bien que je convienne qu'ils n'indiquent pas explicitement qu'ils excluent les systèmes géographiques, ils disent que "les caractéristiques sont basées sur la géométrie 2D avec interpolation linéaire entre les sommets" [début de la section 1]. Cela m'indique qu'ils travaillent dans l'avion.

De plus, si nous supposons que la spécification couvre les coordonnées géographiques, nous constatons que la spécification est terriblement inadéquate. Par exemple.:

De plus, gardez à l'esprit que même si nous essayons de nous en tenir à 99-049 autant que possible avec notre type de géographie, nous ne visons pas la conformité qui n'est qu'un objectif difficile avec notre type de géométrie. Nous dirions que la conformité à 99-049 pour les systèmes géographiques n'a pas beaucoup de sens, et nous aimerions voir une norme qui traite des systèmes géographiques.

Enfin, concernant 05-126, bien que la citation que vous tirez soit exacte, je ne la considère pas comme pertinente : je ne pense pas que quiconque prétende que dans (la plupart) des projections cartographiques, la latitude est représentée le long de l'axe y de la terrain. Je trouve intéressant que la section 6.4.2 déclare, "A Spatial Reference System, également appelé système de coordonnées, est un système géographique (latitude Longitude), un système de coordonnées projeté (X,Y) ou géocentrique (X,Y,Z)." [c'est moi qui souligne]

Avec l'OGC 99-049, l'OGC a certifié d'autres systèmes pour une utilisation avec des systèmes de coordonnées géographiques utilisant l'ordre des axes lon/lat pour les systèmes géographiques. Même si votre interprétation est correcte à la lettre de la norme, elle est incorrecte sur la base de la pratique, de l'application et des tests de la norme. Le comité a démontré son intention d'appliquer cette norme aux systèmes géographiques.

Oui, vous avez tout à fait raison de dire que 99-049 est terriblement inadéquat pour les coordonnées géographiques. Si j'ai donné l'impression que je croyais le contraire, je m'excuse car cela n'a jamais été mon intention.

Cependant, l'inadéquation ou l'ambiguïté n'est pas une intention. Je pense que les actions de l'OGC concernant 99-049 montrent l'intention de l'OGC lors de la mise en œuvre de cette norme - et rien de ce que vous avez dit ne montre le contraire (vous avez souligné ce que je soupçonnais - les normes de l'OGC sont à moitié cuites et besoin de beaucoup de travail pour être "bien"). À moins que vous ne puissiez montrer que l'OGC a certifié de manière incorrecte d'autres systèmes ou que leur certification a exclu les portions géographiques des implémentations, alors je pense que la position de Microsoft est toujours fausse.

La citation est assez pertinente. Je signale qu'il y a une ambiguïté dans la norme sur les systèmes géométriques--pas seulement dans les systèmes géographiques. Comment allez-vous résoudre ces ambiguïtés et/ou conflits ? Ou, plus important encore, choisirez-vous une implémentation différente du reste de l'industrie ?

Si vous travaillez en fait vers la conformité 99-049 (Spécification des fonctionnalités simples pour SQL), alors la réunion de juin 2005 ne s'appliquerait pas (car c'est une norme qui précède cette réunion - les mises à jour ne comptent pas comme nouvelles). Si vous travaillez vers 05-126/06-104r3 (Implementation Spec for Geo Info - Simple Feature Access), alors la réunion de juin 2005 pourrait s'appliquer car il s'agit d'un Nouveau norme qui a remplacé 99-049.

Je pense toujours que la meilleure solution est de permettre aux clients de choisir s'ils souhaitent utiliser le 99-049 obsolète tel qu'il est mis en œuvre par le reste de l'industrie ou 06-104r3 tel qu'il est mis en œuvre dans Katmai à partir du CTP de novembre.

Veuillez vous assurer de refléter la norme que vous soutenez dans vos documents d'aide, car ils revendiquent 06-104r3.

Je ne peux pas ajouter grand-chose à la discussion sur les normes. Au fil des ans, en tant que responsable SIG, j'ai gardé un œil sur eux pour voir s'ils seraient utiles dans l'organisation pour laquelle je travaillais, mais je ne les ai trouvés utiles que dans le domaine de l'interopérabilité de base (je veux dire de base).

Maintenant, ce que j'attends de Katmai, c'est la fonctionnalité et la flexibilité du point de vue d'une base de données autonome. Le problème que j'ai sur la base de mon expérience limitée avec l'ordre des coordonnées lat/long dans le type de géographie est moins l'ordre (Remarque : Google Maps utilise Lat/Long - qu'utilise Virtual Earth ?) mais qu'il n'y a pas de fonctions disponibles pour cartographier entre la géométrie et la géographie ou une API flexible pour accéder aux objets géométrie/géographie. Par exemple, je ne peux actuellement pas mettre à jour les coordonnées d'une géométrie/géographie sauf via le piratage des chaînes WKT (qui sont limitées en 2D et n'incluent pas les SRIDS). Notez que j'ai dit MISE À JOUR et non LIRE. Oui, nous pouvons LIRE les coordonnées via STPointN mais nous ne pouvons pas facilement faire quelque chose comme mettre à niveau un objet 2D en 3D (ou inverser) sauf en utilisant WKT. Notez que certains traitements d'objets ne nécessitent pas de savoir si l'on traite l'anneau extérieur ou intérieur d'un polygone et une partie d'une chaîne de lignes en plusieurs parties.

Mon ami Anand Kannan de MapInfo m'a envoyé du T-SQL pour inverser le XY, Lat/Long qui repose sur le traitement des chaînes WKT. J'ai envisagé d'écrire une fonction similaire et j'en suis arrivé à la même conclusion : cela nécessiterait le piratage de chaînes (le script d'Anand est arrivé le lendemain où j'avais décidé que je n'allais pas m'engager dans cette voie). C'est laid, lent et a d'autres difficultés (par exemple l'internationalisation et l'utilisation de virgules comme points décimaux en Europe, etc.).

J'ai écrit de nombreuses fonctions supplémentaires dans le PL/SQL d'Oracle précisément parce que je peux accéder à tous les aspects du format de stockage. Je ne veux pas avoir à écrire C# (pour lire/écrire WKB ou WKT) et déployer le code dans la base de données si je peux l'aider.

Donc, si nous sommes coincés avec l'ordre lat/long, mon principal plaidoyer est de voir beaucoup plus de puissance déployée dans la base de données pour le traitement T-SQL via une API beaucoup plus riche qui est actuellement couverte par n'importe quelle norme OGC ou SQL/MM. Pour ma part, je ne veux pas avoir à coordonner un progiciel SIG de gros client (le gros client n'est pas censé être péjoratif) et un processus de base de données afin de traiter les données.

C'est bon de te voir ici! :-) Mais je ne suis pas d'accord avec ce qui précède : l'ordre des coordonnées pour la projection "Latitude / Longitude" (également appelé "lat/lon") en KML défini par Google est lon / lat.

Permettez-moi de changer de vitesse pour défendre Microsoft, qui, je pense, est injustement distrait dans ce fil avec une discussion sur l'analyse de la bouillie OGC. Ce n'est pas le propos. Il s'agit d'un nouveau type, la GÉOGRAPHIE, qui peut faciliter la vie de certains utilisateurs. C'est une mission différente de quelque chose comme GEOMETRY fourni pour une utilisation avec des données SIG existantes par des personnes SIG expérimentées.

J'ai l'impression que certaines personnes qui lisent ce fil sont novices en matière de données géospatiales et ne réalisent pas que l'expression anglaise courante "Latitude / Longitude" data est exactement à l'opposé de la façon dont les données sont réellement ordonnées dans le monde des données SIG. Vous n'êtes pas seul si c'est ce que vous attendiez.

Ce n'est pas destiné à être une critique, car c'est plutôt un argument très solide pour avoir l'ordre des coordonnées tel qu'il est dans le type GEOGRAPHY si cela est effectivement destiné aux non-SIG. Si le but de la GEOGRAPHIE est d'avoir quelque chose pour les types non SIG, l'ordre (lat, lon) est parfaitement logique.

La chose la plus naturelle au monde pour quelqu'un qui découvre les données géospatiales est de penser, non, de * s'attendre * que lorsque quelqu'un dit qu'il a des données "Latitude / Longitude" que l'ordre des coordonnées pour ces données est en effet dans l'ordre (lat, lon) juste comme l'expression anglaise couramment utilisée pour le décrire le dit. Les personnes familières avec les données géospatiales comprennent que la phrase anglaise n'est qu'une phrase, pas une description mathématique de l'ordre des axes de coordonnées, mais les débutants ne le savent généralement pas. Manifold a vendu plus de SIG robustes à de nouveaux utilisateurs que tous les autres fournisseurs de SIG réunis, alors j'assure aux lecteurs qu'il s'agit d'un phénomène bien réel.

Une partie de ce que SQL Server peut faire en ajoutant des extensions spatiales consiste à élargir les avantages de la technologie géospatiale à un public beaucoup plus large. Microsoft a la capacité de le faire, contrairement aux autres fournisseurs. Concilier les attentes des débutants géospatiaux (même s'ils peuvent être des experts en SGBD et pas du tout des débutants en programmation) avec la pratique accumulée au sein des téraoctets de données SIG n'est pas facile, mais avoir deux types est une innovation utile qui peut y arriver.

Si Microsoft peut faciliter la vie des personnes qui découvrent les données géospatiales avec un classement des coordonnées dans GEOGRAPHY différent des données SIG héritées, eh bien, c'est parfaitement correct et une raison suffisante pour moi. Quoi qu'il en soit, nous le soutiendrons. Microsoft a également implémenté GEOMETRY pour ceux qui utilisent des données SIG, il n'y a donc aucun inconvénient pour ceux qui travaillent avec des données SIG.

Comme je l'ai déjà dit, si vous utilisez des données SIG, vous feriez mieux de les stocker dans GEOMETRY afin de ne pas perdre d'informations essentielles, telles que les données, nécessaires à la précision. Utilisez GEOMETRY avec les données SIG, qu'il s'agisse de projection "Latitude / Longitude" ou non, et il n'y a aucun problème avec l'ordre des coordonnées. Par conséquent, mon conseil à Simon serait d'utiliser la GÉOMÉTRIE et d'être heureux que vous l'ayez.

Un post-scriptum pour ceux qui aiment les discussions sur les détails techniques :

Juste pour que tout le monde sache que je ne prétends pas capricieusement que l'ordre dans GEOGRAPHY est OK parce que je ne comprends pas que l'ordre (lon, lat) est universel, permettez-moi de citer quelques exemples et de corriger certaines idées fausses qui peuvent s'être glissées dans ce fil, à commencer par l'idée que n'importe qui devrait se soucier de ce que fait l'OGC ou une idée fausse de ce que GML ou EPSG spécifie.

Premièrement, les "standards" de l'OGC sont presque inutiles dans la vraie vie. Isaac a mis le doigt sur la tête quand il a écrit " De plus, si nous supposons que la spécification couvre les coordonnées géographiques, nous trouvons que la spécification est terriblement inadéquate." Bien dit !

En parlant de "normes" OGC presque inutiles, cela fait apparaître GML, qui a été décrit comme "peut-être le pire format pour les données géospatiales, juste après le braille". Étant donné que GML représente peut-être moins d'un millionième des données géospatiales disponibles, je ne Je ne pense pas qu'il importe du tout dans un sens pratique quel ordre de coordonnées GML utilise. Mais, si nous fendons les cheveux en quatre et prétendons que GML est important, je dois respectueusement être en désaccord avec la suggestion selon laquelle GML spécifie l'ordre (lat, lon).

La norme GML stipule que l'ordre des coordonnées doit suivre celui de la projection. Pas très utile d'un point de vue pratique. Ainsi, bien que GML inclue des moyens de spécifier explicitement l'ordre des axes de coordonnées, il ne choisit pas explicitement l'ordre (lat, lon) plutôt que l'ordre (lon, lat). Dans tous les cas, l'utilisation de GML pour les données lat/lon est si rare que nous n'avons pas encore vu de fichier GML réel avec des données lat/lon quel que soit l'ordre des coordonnées.

En ce qui concerne EPSG, je ne suis pas d'accord non plus avec ce qui est normalement traité comme un ordre (lat, lon): Les gars d'EPSG ont conçu leur système de sorte que les axes utilisés par un système de coordonnées soient numérotés. Je n'ai jamais entendu parler de personne (Oracle, etc.) reflétant l'ordre de ces axes sur des valeurs de coordonnées réelles. Je peux voir l'intérêt de SQL Server comme il le fait dans GEOGRAPHY et je respecte cela à 100%, mais je pense que toute la justification dont on a besoin est de savoir comment c'est fait dans SQL Server, car la façon dont c'est fait là-bas devient le de norme de facto que nous suivrons.

J'ai été mis au défi dans le passé pour avoir déclaré que la commande (lon, lat) était une pratique "universelle" dans les données SIG, alors permettez-moi de préciser pourquoi j'affirme que c'est le cas : appelées projections "Latitude / Longitude" :

1. Les coordonnées dans les ensembles de données TIGER/Line produits par le US Census Bureau sont en lat/lon. L'ordre des coordonnées est lon/lat.

2. Les coordonnées dans les ensembles de données NTAD produites par le US Bureau of Transportation Statistics sont en lat/lon. L'ordre des coordonnées est lon/lat.

3. Les coordonnées en GDF et autres ensembles de données produits par TeleAtlas, une grande société européenne de cartographie, sont en lat/lon. L'ordre des coordonnées est lon/lat.

4. Les coordonnées dans les ensembles de données VMap produits par le département américain de la Défense peuvent être dans plusieurs systèmes de coordonnées différents. Pour les systèmes de coordonnées lat/lon, l'ordre des coordonnées est lon/lat.

5. Les coordonnées dans SHP (ESRI "shapefiles"), MIF (MapInfo) et d'autres fichiers produits par les packages SIG actuels peuvent être dans de nombreux systèmes de coordonnées différents. Pour les systèmes de coordonnées lat/lon, l'ordre des coordonnées est lon/lat dans ces systèmes.

6. Les coordonnées au format DGN (Intergraph, Bentley), DWG (Autodesk et bien d'autres), DXF et les fichiers similaires produits par les progiciels de CAO actuels peuvent se trouver dans de nombreux systèmes de coordonnées différents. Pour les systèmes de coordonnées lat/lon, l'ordre des coordonnées est lon/lat.

7. Les coordonnées en valeurs WKB / WKT stockées dans les bases de données traditionnelles telles que Oracle, DB/2, PostgreSQL et MySQL (qui n'ont pas d'équivalents au type GEOGRAPHY mais sont néanmoins utilisées pour stocker beaucoup de données en circulation) peuvent être dans de nombreux différents systèmes de coordonnées. Pour les systèmes de coordonnées lat/lon, l'ordre des coordonnées est lon/lat.

8. La représentation des coordonnées dans un raster (les écrans de Terre virtuelle étant un exemple) suit presque toujours la notation XY, qui pour les rasters lat/lon signifie presque toujours lon/lat.

9. Lorsque les gens commencent à utiliser SQL Server 2008 pour stocker des données spatiales, beaucoup d'entre eux utiliseront le type GEOMETRY même si leurs données sont en lat/lon, pour diverses raisons. Pour ces personnes, l'ordre des coordonnées sera à nouveau lon/lat et cet ordre sera préservé lorsque ces données seront utilisées dans les produits SIG.

Ouf! . c'est ce que j'entends par utilisation "universelle", comme les formats ci-dessus comprennent, quoi ? 99,99 % ? des données là-bas. Si quelqu'un peut pointer ne serait-ce qu'un seul référentiel de données "Latitude / Longitude" qui utilise un ordre (lat, lon) au lieu d'un ordre (lon, lat), je serais très reconnaissant de voir l'URL de ce référentiel.

Pour tout ce qui précède, le choix de Microsoft de commander pour la GÉOGRAPHIE est parfait si, en effet, cela peut aider les personnes novices en données géospatiales qui prennent l'expression "Latitude / Longitude" au pied de la lettre, comme elles le font habituellement. Aussi fier que quiconque puisse être à propos de l'utilisation du SIG à ce jour, même 100 % de toutes ces données ne représentent pas 1 % de ce qui arrive à mesure que la technologie géospatiale se généralise, donc ce que les gens qui découvrent l'art s'attendent à ne pas négliger à la légère .

Si c'était moi, j'aurais probablement été conventionnel et m'en serais tenu à la commande (lon, lat). Mais alors, il ne fait aucun doute que j'aurais pris la tâche d'expliquer encore et encore à des centaines de millions de personnes à l'avenir (dont aucun ne lira de documentation) que par "Latitude / Longitude" je voulais vraiment qu'ils comprennent qu'ils doivent mettre leurs coordonnées dans l'ordre "Longitude / Latitude". Quiconque critique Microsoft dans ce choix devrait réfléchir à deux fois à la façon dont il gérerait cela.


En supposant que la colonne est indexée, ce qui suit devrait être raisonnablement efficace.

Avec deux recherches de 10 lignes puis une sorte de (jusqu'à) 20 retours.

(c'est-à-dire potentiellement quelque chose comme ci-dessous)

Ou une autre possibilité (qui réduit le nombre de lignes triées à max 10)

NB : Le plan d'exécution ci-dessus concernait la définition de table simple

Techniquement, le tri sur la branche du bas ne devrait pas non plus être nécessaire car il est également ordonné par Diff, et il serait possible de fusionner les deux résultats ordonnés. Mais je n'ai pas pu obtenir ce plan.

The query has ORDER BY Diff ASC, YourCol ASC and not just ORDER BY YourCol ASC , because that was what ended up working to get rid of the Sort in the top branch of the plan. I needed to add the secondary column in (even though it won't ever change the result as YourCol will be the same for all values with the same Diff) so it would go through the merge join (concatenation) without adding a Sort.

SQL Server seems able to infer that an index on X seeked in ascending order will deliver rows ordered by X + Y and no sort is necessary. But it is not able to infer that travelling the index in descending order will deliver rows in the same order as Y-X (or even just unary minus X). Both branches of the plan use an index to avoid a sort but the TOP 10 in the bottom branch are then sorted by Diff (even though they are already in that order) to get them in the desired order for the merge.

For other queries/table definitions it may be trickier or not possible to get the merge plan with just a sort of one branch - as it relies on finding an ordering expression that SQL Server:


How to Best Implement nearest neighbour search in mysql?

I have 100k biz record each with lattitude and longitude. I see that MySQL actually support a data type called point. Should I use that instead?

Is it best to use point data type rather than regular float data type to store latitutude and longitude?

Eventually I want to find things like the first 100 restaurants closest to points 105,6 for example and my databases contains a lot of biz and points. Obviously computing the distance one by one for every records and for every points would be O(n) and hence sucks.

Notice that I am aware of a simpler solution described in How do Application Like Yelp Retrieve distance information from data base efficiently and will implement that my self too for a start. That's a good answer.

However, I think there is one creme of the crop answer that should outperform that right? In fact, storing location based on latitude and longitude and finding stuffs nearest to it is a very common problem I expect mysql to have a special design pattern for that. Does it have that?


Voir la vidéo: 13- Find closest lat, long to your location using SQL server. البحث عن أداة قرب موقعي (Octobre 2021).