远距离内的 Oracle 空间搜索

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9061770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 00:34:52  来源:igfitidea点击:

Oracle spatial search within distance

oraclesearchgeooracle-spatial

提问by ka_lin

I have the following table Cities:

我有下表城市:

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

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.

现在基于用户的经度(例如:44.8)和纬度(例如:46.3)我想搜索他附近 100 英里/公里范围内的所有城市。

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';

Any help would be appreciated.

任何帮助,将不胜感激。

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

PS:如果可以有距离并被排序

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-MySQLbut it takes too long...

PPS:由于性能问题,我想这样做,我已经这样做了http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL但它需要太长时间...

回答by O. Jones

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

你有一个很好的关于 mySQL 距离搜索的参考。

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

忘记 Oracle Spatial 的东西。代码太多,太复杂,没有足够的增值。

Here's a query that will do the trick. This uses distances in statute miles. EDITThis 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.

这是一个可以解决问题的查询。这使用以法定英里为单位的距离。 编辑这修复了 mdarwin 提到的错误,代价是如果您尝试将其用于北极或南极的位置,则会进行除法检查。

  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

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.)

如果您以公里为单位工作,请将 mydst/69 更改为 mydst/111.045,并将 3959 更改为 6371.4。(1/69 将英里转换为度;3959 是行星半径的值。)

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.

这个子句是使查询快速的核心。它会在您的 Cities 表中搜索您指定的附近城市。

     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))))

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.

要使其正常工作,您肯定需要在 LATITUDE 列上建立索引。LONGITUDE 列上的索引也会有所帮助。它会进行近似搜索,查找您点附近地球表面准矩形块内的​​行。它选择了太多的城市,但不是太多。

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

此处的此子句可让您从结果集中消除额外的城市:

   WHERE distance <= mydst

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

这个子句是haversine公式,它计算每个城市和你的点之间的大圆距离。

           (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.

查询的其余部分只是组织事物,因此您可以按距离进行选择和排序。

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

这里有更完整的解释:http: //www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

回答by Arturo Hernandez

If you decide to make your own formula, I think this function could be very useful for oracle users and could be modified slightly for other DB's. This is the flat earth formula which is a lot less computationally expensive than the more accurate haversine formula.

如果您决定制作自己的公式,我认为此功能对于 oracle 用户可能非常有用,并且可以为其他 DB 稍作修改。这是平坦地球公式,与更准确的半正弦公式相比,它的计算成本要低得多。

CREATE OR REPLACE Function CIC3.F_FLATEARTHRAD
   ( latoriginrad IN number,
     longoriginrad IN number,
     latdestrad IN number,
     longdestrad IN number)

RETURN  number IS
   a number;
   b number;
   c number;
   u number;
   v number;

   HalfPi number:=1.5707963;
   R number:=3956;
BEGIN
   if latoriginrad is null or latdestrad is null or 
   longdestrad  is null or  longoriginrad is null then
         return null;
   end if; 
   a := HalfPi - latoriginrad;
   b := HalfPi - latdestrad;
   u := a * a + b * b;
   v := - 2 * a * b * cos(longdestrad - longoriginrad);
   c := sqrt(abs(u + v));

   return R * c;
END;

Then your query becomes

然后你的查询变成

select * from GEO.Cities a
where F_FLATEARTHRAD(44.8*0.0174,46.3*0.0174,
               latitude_radians,longitude_radians)<1000 

The 0.0174 factor is needed because the formula uses radians not degrees. So you would need to either store radians (maybe with a trigger). Or you would need to modify the formula to accept degrees. For query purposes you may be querying thousands of records and even a single extra multiplication can make a difference in response time. In our case some queries compare distances between two tables 4k records on one and 200k so we have in the order of billions of function calls.

需要 0.0174 因子,因为公式使用弧度而不是度数。因此,您需要存储弧度(可能带有触发器)。或者您需要修改公式以接受度数。出于查询目的,您可能会查询数千条记录,即使是一次额外的乘法也会对响应时间产生影响。在我们的例子中,一些查询比较了两个表之间的距离,一个和 200k 的 4k 记录,所以我们有数十亿次函数调用。

Below is the haversine equivalent for people not needing to worry about time.

以下是不需要担心时间的人的等值半正弦。

CREATE OR REPLACE Function CIC3.F_HAVERSINE 
  ( latorigin IN number,
    longorigin IN number,
    latdest IN number,
    longdest IN number)

  RETURN  number IS
    v_longoriginrad number;
    v_latoriginrad number;
    v_longdestrad number;
    v_latdestrad number;
    v_difflat number;
    v_difflong number;
    a number;
    c number;
    d number;
    z number;
    x number;
    e number;
    f number;
    g number;
    h number;
    i number;
    j number;
    k number;
    l number;
    m number;
    n number;
    o number;
    p number;
    q number;
    y number;
BEGIN
    z := .017453293;
    x := 3956;
    y := 57.295780;
    v_longoriginrad:=longorigin*z;
    v_latoriginrad:=latorigin*z;
    v_longdestrad:=longdest*z;
    v_latdestrad:=latdest*z;
    v_difflong:=v_longdestrad-v_longoriginrad;
    v_difflat:=v_latdestrad-v_latoriginrad;

    j:=(v_difflat/2);
    k:=sin(j);
    l:=power(k,2);

    m:=cos(v_latoriginrad);

    n:=cos(v_latdestrad);

    o:=v_difflong/2;
    p:=sin(o);
    q:=power(p,2);

    a:=l+m*n*q;

    c := 2 * asin(sqrt(a));

    d := x * c;

    return d;
END; 

回答by DimaA6_ABC

If you really want to use SDO_WITHIN_DISTANCE, you need to create a column of type SDO_GEOMETRYin your Cities table, fill spatial index metadata and create spatial index:

如果你真的想使用SDO_WITHIN_DISTANCE,你需要SDO_GEOMETRY在你的 Cities 表中创建一个 type 列,填充空间索引元数据并创建空间索引:

  1. SDO_GEOMETRYcolumn:

    CREATE TABLE MYTABLE(
    ...,
    GEOLOC MDSYS.SDO_GEOMETRY,
    ...
    );
    
  2. Spatial Index Metadata:

    INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
    VALUES ('MYTABLE' /*your table name*/, 'GEOLOC', /*your spatial column name*/
        SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 1),
                  SDO_DIM_ELEMENT('Y', -90, 90, 1)),
                  8307);
    
  3. Create Spatial Index:

    CREATE INDEX MY_SPATIAL_IDX ON MYTABLE (GEOLOC)
    tablespace SomeTablespace; -- optional
    
  4. Now substitute GEOLOC where you said [I don't know].

  1. SDO_GEOMETRY柱子:

    CREATE TABLE MYTABLE(
    ...,
    GEOLOC MDSYS.SDO_GEOMETRY,
    ...
    );
    
  2. 空间索引元数据:

    INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
    VALUES ('MYTABLE' /*your table name*/, 'GEOLOC', /*your spatial column name*/
        SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 1),
                  SDO_DIM_ELEMENT('Y', -90, 90, 1)),
                  8307);
    
  3. 创建空间索引:

    CREATE INDEX MY_SPATIAL_IDX ON MYTABLE (GEOLOC)
    tablespace SomeTablespace; -- optional
    
  4. 现在替换你所说的 GEOLOC [我不知道]。

This was to answer your question. Others gave you a hint that using Oracle spatial for such a simple task is on overkill. In this case, I tend to agree, because you can do a simple boxing in WHERE clause to cut out cities not in rectangular box with center of your starting point and size of your search distance; however sometimes you will need an intelligence of R-tree index. Anyway, their solutions have 2 major problems:

这是为了回答你的问题。其他人暗示您将 Oracle 空间用于如此简单的任务是过度的。在这种情况下,我倾向于同意,因为您可以在 WHERE 子句中进行简单的装箱,以切出不在以起点为中心和搜索距离大小的矩形框中的城市;但是有时您将需要 R 树索引的智能。无论如何,他们的解决方案有两个主要问题:

a. They use Great Circle approach to calculate distance between the points. It is too rough, you need to use ellipsoid approach to get more accurate results. Googling provides answer immediately like this.

一种。他们使用大圆方法来计算点之间的距离。太粗糙了,你需要使用椭球方法来获得更准确的结果。谷歌搜索提供答案马上像这样

b. If you would program the ellipsoid distance algorithm in PL/SQL, you will find it prohibitely slow. Solution is to move this logic to Java or C++ and make it callable from Oracle (there is standard way of doing that).

湾 如果您在 PL/SQL 中编写椭球距离算法,您会发现它非常慢。解决方案是将此逻辑移动到 Java 或 C++ 并使其可从 Oracle 调用(有标准的方法可以做到这一点)。

回答by fl4l

After some years from the accepted answer, it is possible to add some enhancements to the query: Oracle database in version 11.1 added the function calc_distance (http://psoug.org/reference/functions.html), useful to calculate accurately the distance.
About the clauses to make the query faster, uses the conversion constant from distance to radians that varies with latitude (http://www.longitudestore.com/how-big-is-one-gps-degree.html) and adds an error that increases with search radius.

Here my changes that uses an average value of earth radius, in my tests it seems to be more accurate for large radius searches, in europe latitudes:

从接受的答案开始几年后,可以对查询添加一些增强功能:11.1 版中的 Oracle 数据库添加了函数 calc_distance ( http://psoug.org/reference/functions.html),有助于准确计算距离.
关于使查询更快的子句,使用从距离到随纬度变化的弧度的转换常数(http://www.longitudestore.com/how-big-is-one-gps-degree.html)并添加错误随着搜索半径的增加而增加。

这里我的更改使用地球半径的平均值,在我的测试中,在欧洲纬度的大半径搜索中似乎更准确:

SELECT id, city, LATITUDE, LONGITUDE, distance FROM
  (
    SELECT id, 
           city, 
           LATITUDE, LONGITUDE,
           calc_distance(LATITUDE, LONGITUDE, mylat, mylng) AS distance,
           b.mydst
      FROM Cities
      JOIN (
        SELECT :LAT AS mylat,
               :LONG AS mylng,
               :RADIUS_LIMIT AS mydst,
                3.1415926 AS pi, -- or use pi() function if available
                6371.4 earthradius
          FROM DUAL
      )b ON (1 = 1)
     WHERE LATITUDE >=  mylat - ((mydst / earthradius) * (180 / pi))
       AND LATITUDE <=  mylat + ((mydst / earthradius) * (180 / pi))
       AND LONGITUDE >= mylng - ((mydst / earthradius) * (180 / pi) / cos(mylat * pi/180))
       AND LONGITUDE <= mylng + ((mydst / earthradius) * (180 / pi) / cos(mylat * pi/180))
  )a
WHERE distance <= mydst
ORDER BY distance