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

问题描述

背景

我正在编写一个应用程序，它可以在邮政编码的某个半径范围内查找事件。你可以想象这样的票主，你输入你的邮政编码，所有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表，其中每个Event都有一个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".

然后我来意识到我不能告诉实体框架来使用我的sproc在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 表（右键单击 - >设计）添加一个地理列。我命名为我的位置：

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

接下来，由于我使用数据库优先的EDM，所以我不得不更新我的模型，使用我创建的新字段。然后我收到一个关于无法将地理位置转换为双倍的错误，所以我修复它是在实体中选择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); 

距离扩展方法获取从当前对象到您传递的其他对象的距离in。另一个对象是 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上下文，从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更具体），并通过Web服务获取用户特定地址的纬度和经度，并从Latitude& Inc创建一个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.

当我创建一个事件时，我使用google 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 + ")");

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

我如何获得距离扩展方法？

要获取扩展方法距离，您必须添加对项目的引用： System.Data.Entity
这样做后，您必须使用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.

距离扩展方法以度量单位返回距离你可以改变它我想，但这是默认）。在这里，我的半径参数是英里，所以我做了一些数学转换。

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.

注意：有一个 DBGeography class System.Data.Spatial 。这是错误，对我来说不行。我在互联网上发现的很多例子使用了这个例子。

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.

如何将Lat / Long转换为地理列

所以，如果你像我一样，并且下载了所有 Latitude & c>经度列，然后意识到它没有地理列...这可能有帮助：

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 ）将位置列添加到表中。将类型设置为地理。

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

2）执行以下sql

2) Execute the following 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屋！