mysql - 根据经纬度查找用户

Question

我想找到 5 英里以内且具有与我相同标签的用户。

我的数据结构:

UserTable

--------------------------------------
 userid | name | lat | long | address
--------------------------------------
  101   |  xyz |  92 |  72  | NY
  201   |  HYS |  48 |  56  | JAMAICA
  301   |  LMN |  92 |  75  | Brazil


TagTable

---------------------
 id | userid | tagid
---------------------
  1 |   101  |   5
  2 |   201  |   7
  3 |   301  |   5

查询:

SELECT vb.userid,
       vb.address,
       ( 6371 * ACOS(   COS( RADIANS( 28.684342 ) )
                          * COS( RADIANS( vb.lat ) ) 
                          * COS( RADIANS( vb.long) - RADIANS( 77.137941 ) )
                      + SIN( RADIANS( 28.684342 ) )
                          * SIN( RADIANS( vb.lat) )
                    )
       ) AS distance
  FROM UserTable vb, TagTable vk
 WHERE vk.userid = vb.userid
   AND vk.tagid = '5'
 GROUP BY vk.userid
HAVING distance < 10
 ORDER BY distance;

上面的查询要花很多时间，请帮我找到这种情况下最快的查询。

Answer 1

使用 Oracle 的 Spatial数据

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE UserTable (
  userid   NUMBER(8,0),
  name     VARCHAR2(255),
  location SDO_GEOMETRY,
  address  VARCHAR2(255)
);

INSERT INTO UserTable
          SELECT 101, 'xyz', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(92,72,NULL), NULL, NULL), 'NY' FROM DUAL
UNION ALL SELECT 201, 'HYS', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(48,56,NULL), NULL, NULL), 'JAMACA' FROM DUAL
UNION ALL SELECT 301, 'LMN', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(92,75,NULL), NULL, NULL), 'Brazil' FROM DUAL;

INSERT INTO USER_SDO_GEOM_METADATA (
  TABLE_NAME, COLUMN_NAME, DIMINFO, SRID
) VALUES (
  'USERTABLE',
  '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 UserTable_SIDX ON UserTable( location ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE TABLE TagTable (id, userid, tagid ) AS
          SELECT 1, 101, 5 FROM DUAL
UNION ALL SELECT 2, 201, 7 FROM DUAL
UNION ALL SELECT 3, 301, 5 FROM DUAL;

查询 1:

SELECT u.userid
FROM   UserTable u
       INNER JOIN
       TagTable t
       ON u.UserID = t.UserID
WHERE  sdo_within_distance (
         u.location,
         SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(92,72,NULL), NULL, NULL),
         'distance=5 unit=MILE'
       ) = 'TRUE'
AND    t.tagid = 5

Results :

| USERID |
|--------|
|    101 |