如何使用空间来搜索邮政编码的半径? [英] How do I use spatials to search a radius of zip codes?

问题描述

背景

我正在编写一个应用程序，它可以在邮政编码的某个半径范围内查找事件.你可以把它想象成ticketmaster，你输入你的邮政编码，x 半径内的所有音乐会都会显示出来.

I'm writing an application which finds events within a certain radius of a zip code. You can think of this like ticketmaster, where you type in your zip code and all of the concerts in the radius of x show up.

我有一个包含邮政编码的数据库表，以及每个邮政编码的纬度和经度.我还有一个EventListings"表，其中每个事件"都有一个 ZipCode 字段.

I have a database table which has the zip code, and each zip codes' Latitude and Longitude. I also have an 'EventListings' table where each 'Event' has a ZipCode field.

问题

目前，我在服务层的 Linq-to-Entities 查询中使用 Haversine 公式来查找半径内的事件.现在，我将它用作 where 子句中的过滤器.我还想把它放在 select 子句中，这样我就可以在网站上显示这是 4.6 英里外"等.

Currently, I'm using the Haversine formula in a Linq-to-Entities query in my service layer to find which events are within the radius. Right now, I'm using it as a filter in the where clause. I'd also like to place it in the select clause so on the website I can show "this is 4.6 miles away", etc.

我无法将此代码移动到单独的 C# 方法中，因为 Linq-to-Entities 会抱怨它无法将其转换为 sql，因此我不得不在 select 语句中复制整个公式.这是非常丑陋的.我试图修复它.

I can't move this code into a separate C# method because Linq-to-Entities will complain that it can't convert it to sql, so that leaves me with duplicating the entire formula in the select statement too. This is very ugly. I tried to fix it.

我的尝试

我编辑了实体，并添加了DistanceFromOrigin"的特殊标量属性.然后我创建了一个存储过程，它带回了所有实体数据，以及新字段DistanceFromOrigin"的硬编码值(用于测试目的).

I edited the Entity, and added a special scalar property of "DistanceFromOrigin". I then created a stored procedure which brought back all the entity data, plus a hard coded value (for testing purposes) for the new field "DistanceFromOrigin".

然后我意识到我不能告诉实体框架将我的存储过程用于它在 EventListings 实体上的 select 语句...... Phil 建议使用空间，所以我就这么做了.

I then came to realize that I can't tell entity framework to use my sproc for its select statement on the EventListings entity... Phil suggested spatials, so thats what I went with.

问题

如何使用 Spatials 搜索邮政编码半径范围内的事件?

How can I use Spatials to search for events within a radius of zip codes?

推荐答案

所以，根据 Phil 的建议，我使用空间重新编写了很多内容.效果很好，您只需要 .NET4.5、EF5+ 和 sql server(我相信 2008 及更高版本).我正在使用 EF6 + Sql Server 2012.

So, Using Phil's suggestion, I re-wrote a lot of this using spatials. This worked out great, you just need .NET4.5, EF5+ and sql server (2008 and above I believe). I'm using EF6 + Sql Server 2012.

第一步是向我的数据库EventListings 表中添加一个Geography 列(右键单击它-> 设计).我命名了我的位置:

The first step was to add a Geography column to my database EventListings table (Right click on it -> Design). I named mine Location:

接下来，由于我使用 EDM 和数据库优先，我必须更新我的模型以使用我创建的新字段.然后我收到一个关于无法将 Geography 转换为 double 的错误，所以我要修复它是在实体中选择 Location 属性，转到它的属性并将其类型从 double 更改为 Geography:

Next, since I am using the EDM with database-first, I had to update my model to use the new field that I created. Then I received an error about not being able to convert Geography to double, so what I did to fix it was select the Location property in the entity, go to its properties and change its type from double to Geography:

那么你所要做的就是像这样查询你的实体集合:

Then all you have to do is query your entity collection like this:

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

Distance 扩展方法获取从当前对象到您传入的其他"对象的距离.该其他对象的类型为 DbGeography.你只需调用一个静态方法，它会创建其中一只小狗，然后扔掉你的经度和以纬度为一个点:

The Distance extension method gets the distance from the current object, to the "other" object that you pass in. This other object is of type DbGeography. You just call a static method and it creates one of these puppies, then just throw your Longitude & Latitude in it as a point:

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

这不是我创建 originCoordinates 的方式.我下载了一个单独的数据库，其中包含所有邮政编码及其纬度和纬度的列表.经度.我还添加了一列 Geography 类型的列.我将在这个答案的最后展示如何.然后，我查询邮政编码上下文以从 ZipCode 表的 Location 字段中获取 DbGeography 对象.

This isn't how I created my originCoordinates. I downloaded a separate database that had a list of all zip codes and their Latitudes & Longitudes. I added a column of type Geography to that as well. I'll show how at the end of this answer. I then queried the zipcode context to get a DbGeography object from the Location field in the ZipCode table.

如果用户想要更具体的来源而不仅仅是邮政编码，我会调用 Google Maps API(更具体的 GeoCode)并通过网络服务获取用户特定地址的纬度和经度，然后创建一个来自 Latitude & 的 DBGeography 对象响应中的经度值.

If the user wants a more specific origin than just a zipcode, I make a call to the Google Maps API (GeoCode to be more specific) and get the latitude and longitude for the users specific address via a webservice, and create a DBGeography object from the Latitude & Longitude values in the response.

我在创建活动时也使用谷歌 API.在将我的实体添加到 EF 之前，我只是像这样设置位置变量:

I use google APIs when I create an event also. I just set the location variable like this before adding my entity to EF:

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

其他提示和技巧&疑难解答

我是如何得到距离扩展法的?

要获取扩展方法Distance，您必须添加对项目的引用:System.Data.Entity完成此操作后，您必须将 using: using System.Data.Entity.Spatial; 添加到您的课程中.

To get the extension method Distance you must add a reference to your project: System.Data.Entity After you do that you must add the using: using System.Data.Entity.Spatial; to your class.

Distance 扩展方法以米的测量单位返回距离(我认为您可以更改它，但这是默认设置).在这里，我的半径参数以英里为单位，所以我做了一些数学转换.

The Distance extension method returns the distance with a unit of measure of meters (You can change it I think, but this is default). Here, my radius parameter was in miles so I did some math to convert.

注意:System.Data.Spatial中有一个DBGeography类.这是错误的，它对我不起作用.我在网上找到的很多例子都使用了这个.

Note: There is a DBGeography class in System.Data.Spatial. This is the wrong one and it would not work for me. A lot of examples I found on the internet used this one.

如何将纬度/经度转换为地理列

所以，如果你像我一样下载了一个包含所有 Latitude 的邮政编码数据库 &Longitude 列，然后意识到它没有 Geography 列...这可能会有所帮助:

So, if you were like me and downloaded a zipcode database with all the Latitude & Longitude columns, and then realized it didn't have a Geography column... this might help:

1) 将 Location 列添加到您的表中.将类型设置为地理.

1) Add a Location column to your table. Set the type to Geography.

2) 执行以下sql

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

如何查看地理项的详细信息

因此，当您在插入一些 DbGeography 项目后在 Sql Server Management Studio 中查询 EventListings 表时，您会看到 Location 列包含一个十六进制值，例如:0x1234513462346.当您想确保插入正确的值时，这不是很有帮助.

So, when you query your EventListings table in Sql Server Management Studio after you've inserted some DbGeography items, you'll see the Location column holds a hex value like: 0x1234513462346. This isn't very helpful when you want to make sure the correct values got inserted.

实际查看纬度 &您必须像这样查询该字段的经度:

To actually view the latitude & longitude off of this field you must query it like so:

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

这篇关于如何使用空间来搜索邮政编码的半径?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！