距离内的Oracle空间搜索 [英] Oracle spatial search within distance

问题描述

我有下表城市:

ID(int),City(char),latitude(float),longitude(float).

现在，根据用户的经度(例如:44.8)和纬度(例如:46.3)，我想搜索位于100英里/公里以内的他附近的所有城市.

Now based on a user`s longitude(ex:44.8) and latitude(ex:46.3) I want to search for all the cities near him within 100 miles/KM.

我找到了一些例子，但不知道如何使它们适应我的情况

I have found some examples but don`t know how to adapt them to my case

select *
from GEO.Cities a
where SDO_WITHIN_DISTANCE([I don`t know],
MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(44.8,46.3, NULL) ,NULL, NULL), 
'distance = 1000') = 'TRUE';

任何帮助将不胜感激.

P.S:如果可以确定距离并进行排序

P.S: If it is possible to have the distance and to be sorted

PPS:由于性能问题，我想以此方式进行操作，我已经以这种方式进行操作

P.P.S: I want to do it in this way due to performance issues, I have done this in this way http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL but it takes too long...

推荐答案

您在那里有很多有关mySQL距离搜索的参考.

You have a pretty good reference there for mySQL distance search.

忘记Oracle Spatial的内容.代码过多，复杂性过多，附加值不足.

Forget about the Oracle Spatial stuff. Too much code, too much complexity, not enough value-add.

这是一个可以解决问题的查询.这使用以法定英里为单位的距离. 编辑:如果您尝试将mdarwin用于北极或南极的位置，则以分开检查为代价来修复mdarwin提到的错误.

Here's a query that will do the trick. This uses distances in statute miles. EDIT This fixes the bug mentioned by mdarwin, at the cost of divide-checking if you try to use it for a location at the north or south pole.

  SELECT id, city, LATITUDE, LONGITUDE, distance
    FROM
  (
    SELECT id, 
           city, 
           LATITUDE, LONGITUDE,
           (3959 * ACOS(COS(RADIANS(LATITUDE)) 
                 * COS(RADIANS(mylat)) 
                 * COS(RADIANS(LONGITUDE) - RADIANS(mylng)) 
                 + SIN(RADIANS(LATITUDE)) 
                 * SIN(RADIANS(mylat)) 
               ))
           AS distance,
           b.mydst
      FROM Cities
      JOIN (
        SELECT :LAT AS mylat,
               :LONG AS mylng,
               :RADIUS_LIMIT AS mydst
          FROM DUAL
      )b ON (1 = 1)
     WHERE LATITUDE >=  mylat -(mydst/69)
       AND LATITUDE <=  mylat +(mydst/69)
       AND LONGITUDE >= mylng -(mydst/(69 * COS(RADIANS(mylat))))
       AND LONGITUDE <= mylng +(mydst/(69 * COS(RADIANS(mylat))))
  )a
   WHERE distance <= mydst
   ORDER BY distance

如果您以千米为单位工作，请将mydst/69更改为mydst/111.045，然后将3959更改为6371.4. (1/69将英里转换为度； 3959是行星半径的值.)

If you're working in kilometers, change mydst/69 to mydst/111.045, and change 3959 to 6371.4. (1/69 converts miles to degrees; 3959 is a value for the radius of the planet.)

现在，您可能会很想将这个大查询用作魔术黑匣子".不要做！这不是很难理解，而且如果您确实理解它，那么您将能够做得更好.这是怎么回事.

Now, you'll probably be tempted to use this large query as a "magic black box." Don't do it! It's not very hard to understand, and if you do understand it you'll be able to do a better job. Here's what's going on.

此子句是使查询快速进行的核心.它会在城市"表中搜索到您指定地点附近的城市.

This clause is the heart of what makes the query fast. It searches your Cities table for nearby cities to the point you specified.

     WHERE LATITUDE >=  mylat -(mydst/69)
       AND LATITUDE <=  mylat +(mydst/69)
       AND LONGITUDE >= mylng -(mydst/(69 * COS(RADIANS(mylat))))
       AND LONGITUDE <= mylng +(mydst/(69 * COS(RADIANS(mylat))))

要使其正常工作，您肯定在LATITUDE列上需要一个索引. LONGITUDE列上的索引也会有所帮助.它进行近似搜索，在靠近您的点的地球表面上寻找在准矩形斑块内的行.它选择了太多城市，但选择的城市并不太多.

For it to work, you definitely need an index on your LATITUDE column. An index on your LONGITUDE column will also help a bit. It does an approximate search, looking for rows that are within a quasi-rectangular patch on the surface of the earth near your point. It selects too many cities, but not far too many.

此子句可让您从结果集中消除多余的城市:

This clause here lets you eliminate the extra cities from your result set:

   WHERE distance <= mydst

此子句是haversine公式，用于计算每个城市与您的点之间的大圆距离.

This clause is the haversine formula which calculates the great-circle distance between each city and your point.

           (3959 * ACOS(COS(RADIANS(LATITUDE)) 
                 * COS(RADIANS(mylat)) 
                 * COS(RADIANS(LONGITUDE) - RADIANS(mylng)) 
                 + SIN(RADIANS(LATITUDE)) 
                 * SIN(RADIANS(mylat)) 

此子句可让您输入点和半径限制(仅一次)作为查询的绑定变量.这很有用，因为各种公式会多次使用这些变量.

This clause lets you enter your point, and your radius-limit, just once as bound variables to your query. It's helpful because the various formulas use those variables multiple times.

        SELECT :LAT AS mylat,
               :LONG AS mylng,
               :RADIUS_LIMIT AS mydst
          FROM DUAL

查询的其余部分仅组织事物，因此您可以按距离进行选择和排序.

The rest of the query simply organizes things so you select and order by distance.

这里是更完整的说明: http://www.plumislandmedia. net/mysql/haversine-mysql-nearest-loc/

Here is a more complete explanation: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

这篇关于距离内的Oracle空间搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！