从给定经度和纬度的oracle数据库中获取酒店详细信息 [英] Get Hotel Details from oracle database for given longitude and latitude

问题描述

我有一个旅馆表，其列为ID，NAME，DESCRIPTION，LONGITUDE，LATITUDE. 现在，我想获取给定半径(4英里)内所有酒店，并以经纬度为长值作为中心.我已经实现了如下查询:

I have a table Hotel with columns as ID, NAME, DESCRIPTION, LONGITUDE, LATITUDE. Now I want to get all the hotels for a given radius(4 miles) and center as long lat value. I have implemented it as below query :

select NAME from (SELECT
  NAME , (
    6371 * acos (
      cos (3.14*(52.047348)/180)
      * cos(3.14*(latitude)/180)
      * cos(3.14*(longitude)/180 - 3.14*(1.15542)/180)
      + sin (3.14*(52.047348)/180) 
      * sin(3.14*(latitude)/180)
    )
  ) AS distance
FROM hotel where latitude is not null and longitude is not null) where distance < 4;

给出正确的评分. 但是，对于大型记录而言，这非常慢. 谁能建议我另一种方法.我已经对其进行了研究，并且知道oracle数据库中存在一个几何.借助于它，我们可以通过在商店表中创建一个几何列来实现它. 但是我不知道该怎么做. 请建议我该怎么做.

It's giving correct recored. But It's very slow for large records. Can any one please suggest me alternative way to do it. I have done research on it and came to know that there is a geometry in oracle database. With the help of it we can acheive it by creating a geometry column in store table. But I don't know how to do it. Please suggest me how to do it.

推荐答案

使用Oracle的

Use Oracle's Spatial Data:

Oracle设置:

CREATE TABLE Hotels (
  id       NUMBER(8,0),
  name     VARCHAR2(255),
  location SDO_GEOMETRY
);

INSERT INTO Hotels
          SELECT 1, 'Hotel1', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(92,72,NULL), NULL, NULL) FROM DUAL
UNION ALL SELECT 2, 'Hotel2', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(48,56,NULL), NULL, NULL) FROM DUAL
UNION ALL SELECT 3, 'Hotel3', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(92,75,NULL), NULL, NULL) FROM DUAL;

INSERT INTO USER_SDO_GEOM_METADATA (
  TABLE_NAME, COLUMN_NAME, DIMINFO, SRID
) VALUES (
  'HOTELS',
  'LOCATION', 
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), 
    SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)
  ), 
  8307
);

CREATE INDEX HotelsTable_SIDX ON Hotels( location ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

查询:

SELECT *
FROM   Hotels
WHERE  sdo_within_distance (
         location,
         SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(92,72,NULL), NULL, NULL),
         'distance=4 unit=MILE'
       ) = 'TRUE'

这篇关于从给定经度和纬度的oracle数据库中获取酒店详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！