按邮政编码或其他基于位置的数据检索策略过滤
我的小网站应该使用活动用户的位置作为过滤器来汇集表中的项目列表。 想想 Craigslist，您在其中搜索“dvd”，但结果并非来自所有数据库，它们是按位置过滤的我的问题有两个级别：
- 我应该去 a-la-craigslist，并要求用户使用城市级别的位置吗？我的问题是，你需要生成一个硬编码的、手工制作的列表。 a-la-zipCode的
- 想法，只是要求用户输入他的邮政编码，然后汇集与他的邮政编码相同或相距一定距离的所有项目。
我应该选择 这似乎是更优雅的解决方案，但是到底如何创建所有邮政编码的数据库并实现给定邮政编码 12345 的功能，获取 1 英里距离内的所有邮政编码，
这应该是相当常见的“任务”，因为很多网站都有和我类似的需求，所以我希望不要在这里重新发明轮子。
My little site should be pooling list of items from a table using the active user's location as a filter. Think Craigslist, where you search for "dvd' but the results are not from all the DB, they are filtered by a location you select. My question has 2 levels:
- should I go a-la-craigslist, and ask users to use a city level location? My problem with this is that you need to generate what seems to me a hard coded, hand made list of locations.
- should I go a-la-zipCode. The idea of just asking the user to type his zipcode, and then pool all items that are in the same or in a certain distance from his zip code.
I seem to prefer the zip code way as it seems more elegant solution, but how on earth do one goes about creating a DB of all zip codes and implement the function that given zip code 12345, gets all zipcodes in 1 mile distance?
this should be fairly common "task" as many sites have a need similar to mine, so I am hoping not to re-invent the wheel here.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
仅找出 1 英里半径内的城市并将该信息存储在表中不是更有效吗？ 那么就不用一直在数据库中进行计算了。
Wouldn't it be more efficient to just figure out which cities are within a 1 mile radius and store that information in a table? Then you don't have to do calculations in the database all the time.
只是为了技术性...PostGIS 不是 Postgres 社区的项目...它是一个构建在 Postgres 之上的独立项目。 如果您需要 PostGIS 的帮助或支持，您需要访问它的社区而不是 Postgres。
Just to be technical... PostGIS isn't a project of the Postgres community... it's a stand-alone project that is built on top of Postgres. If you want help or support with PostGIS, you'll want to go to it's community instead of Postgres.
您可以使用 PostGIS。 此外，我还使用了 deCarta 的映射库。 他们拥有允许您对任何任意数据类型进行地理加密的技术。 然后您可以在空间上查询这些。
免责声明：我为 deCarta 工作
You can use PostGIS. Additionally, I've used deCarta's mapping libraries. They have technology which allows you to geokey any arbitrary data type. Then you can query these spatially.
disclaimer: I work for deCarta
获取邮政编码数据库没有问题。 您可以尝试这个免费的：
http://zips.sourceforge.net/
虽然我不知道它有多新，或者你可以使用众多提供商之一。 我们每年订阅 ZipCodeDownload.com，每月只需 100 美元即可获得最新完整邮政编码数据的更新与邮政编码质心的纬度/经度。
至于查询特定半径内的所有邮政编码，您将需要某种空间库。 如果您只有一个带有纬度/经度的邮政编码表，您将需要一个面向数据库的机制。 SQL Server 2008 具有内置功能，并且有开源库以及将此类功能添加到 SQL Server 2005 的商业库。开源数据库PostgreSQL 有一个名为 PostGIS 的项目，可以将此功能添加到该数据库中。 它在这里： http://postgis.refractions.net/
其他数据库平台可能有类似的项目，但那些是我所知道的。 使用这些基于数据库的库之一，您应该能够直接查询给定半径内的任何邮政编码（或具有纬度/经度列的任何类型的任何行）。
如果您想走不同的路线，您可以使用带有地图库的空间工具。 这里也有开源选项，例如 SharpMap 和 许多其他 ( Google 可以提供帮助），它可以使用美国的免费 Tiger 地图作为数据源。 然而，如果您只需要半径搜索，则此路线会稍微复杂一些并且性能可能较差。
最后，您可能想研究一下网络服务。 正如您所说，这是一种常见的需求，我想您可以订阅任何数量的ob网络服务，这些服务可以提供所提供的邮政编码给定半径内的所有邮政编码。 谷歌快速搜索发现了这个：
http://www.zip-codes.com/free-zip -code-tools.asp#radius
但是有很多资源可供搜索关于这个话题。
Getting a Zip Code database is no problem. You can try this free one:
http://zips.sourceforge.net/
Although I don't know how current it is, or you can use one of many providers. We have an annual subscription to ZipCodeDownload.com, and for maybe $100 we get monthly updates with the latest Zip Code data complete with Lat/Longs of the centroid of the zip code.
As for querying for all zips within a certain radius, you are going to need a spatial library of some sort. If you just have a table of zips with lats/longs, you will need a database-oriented mechanism. SQL Server 2008 has the capability built in, and there are open source libraries and commercial libraries that will add such capabilities to SQL Server 2005. The open source database PostgreSQL has a project, PostGIS that adds this capability to that database. It is here: http://postgis.refractions.net/
Other database platforms probably have similar projects, but those are the ones I am aware of. With one of these DB based libraries you should be able to directly query for any zip codes (or any rows of any kind that have lat/long columns) within a given radius.
If you want to go a different route you can use spatial tools with a mapping library. There are open source options here as well, such as SharpMap and many others (Google can help out) that can use the free Tiger maps for the united states as the data source. However, this route is somewhat more complicated and possibly less performant if all you need is a radius search.
Finally, you may want to look into a web service. This, as you say, is a common need, and I imagine there are any number ob web services that you can subscribe to that can provide all zip codes in a given radius from a provided zip code. A quick Google search turned up this:
http://www.zip-codes.com/free-zip-code-tools.asp#radius
But there are MANY resources to be had for the searching on this subject.
以下是有关如何执行此操作的示例：
http://www.codeproject.com/ KB/cs/zipcodeutil.aspx
Here is a sample on how to do that:
http://www.codeproject.com/KB/cs/zipcodeutil.aspx