MySQLの地理的ポイント間の距離を決定する

最新のサイトを開発する場合、多くの場合、近くの地理的ポイントを出力する機能を実装する必要があります。 この問題を解決する最も最適な方法は、ポイント定義を実装する作業をMySQLの肩に移すことです。 具体的には、MySQL空間拡張機能が必要です（バージョン5.0.16より前は、これらの拡張機能はMyISAMでのみ使用可能でした。MySQLの以降のバージョンでは、InnoDB、NDB、BDB、ARCHIVEによる空間拡張がサポートされています）。



ポイント間の距離は、 Haversine式を使用して計算されます。 この式を使用すると、非常に低いエラーでポイント間の距離を取得できます（エラー値はポイント間の距離に直接比例し、カリフォルニアのGoogle本社（37.422045、-122.084347）とOpera Houseオーストラリア、シドニー（-33.856553、151.214696））。





距離を使用して近くのポイントを正しく計算するには、次のストアド関数とプロシージャを作成する必要があります。

ジオディスト関数

geodist（）は、座標によってポイント間の距離を決定します。

--  6371 -     ,       ,         DELIMITER $$ DROP FUNCTION IF EXISTS geodist $$ CREATE FUNCTION geodist ( src_lat DECIMAL(9,6), src_lon DECIMAL(9,6), dst_lat DECIMAL(9,6), dst_lon DECIMAL(9,6) ) RETURNS DECIMAL(6,2) DETERMINISTIC BEGIN SET @dist := 6371 * 2 * ASIN(SQRT( POWER(SIN((src_lat - ABS(dst_lat)) * PI()/180 / 2), 2) + COS(src_lat * PI()/180) * COS(ABS(dst_lat) * PI()/180) * POWER(SIN((src_lon - dst_lon) * PI()/180 / 2), 2) )); RETURN @dist; END $$ DELIMITER ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

geodist_pt関数

geodist_pt（）はgeodist（）のラッパーであり、POINTタイプのオブジェクトの形式でポイントの座標を処理します。

 DELIMITER $$ DROP FUNCTION IF EXISTS geodist_pt $$ CREATE FUNCTION geodist_pt (src POINT, dst POINT) RETURNS DECIMAL(6,2) DETERMINISTIC BEGIN RETURN geodist(X(src), Y(src), X(dst), Y(dst)); END $$ DELIMITER ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

geobox_ptプロシージャ

geobox（）プロシージャを使用して、検索領域の左上隅と右下隅の座標を計算し、結果の座標をPOINTオブジェクトに変換します。

 -- pt ->     -- dist ->      -- top_lft ->      (  POINT) -- bot_rgt ->      (  POINT) DELIMITER $$ DROP PROCEDURE IF EXISTS geobox_pt $$ CREATE PROCEDURE geobox_pt ( IN pt POINT, IN dist DECIMAL(6,2), OUT top_lft POINT, OUT bot_rgt POINT ) DETERMINISTIC BEGIN CALL geobox(X(pt), Y(pt), dist, @lat_top, @lon_lft, @lat_bot, @lon_rgt); SET top_lft := POINT(@lat_top, @lon_lft); SET bot_rgt := POINT(@lat_bot, @lon_rgt); END $$ DELIMITER ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ジオボックス手順

検索領域の座標を計算します。

 -- src_lat, src_lon ->     -- dist ->      -- lat_top, lon_lft ->       -- lat_bot, lon_rgt ->       DELIMITER $$ DROP PROCEDURE IF EXISTS geobox $$ CREATE PROCEDURE geobox ( IN src_lat DECIMAL(9,6), IN src_lon DECIMAL(9,6), IN dist DECIMAL(6,2), OUT lat_top DECIMAL(9,6), OUT lon_lft DECIMAL(9,6), OUT lat_bot DECIMAL(9,6), OUT lon_rgt DECIMAL(9,6) ) DETERMINISTIC BEGIN SET lat_top := src_lat + (dist / 69); SET lon_lft := src_lon - (dist / ABS(COS(RADIANS(src_lat)) * 69)); SET lat_bot := src_lat - (dist / 69); SET lon_rgt := src_lon + (dist / ABS(COS(RADIANS(src_lat)) * 69)); END $$ DELIMITER ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

使用例：

地理データが保存されているテーブルがあるとします。 テーブルの構造は次のとおりです。

 CREATE TABLE geo ( id INT, name VARCHAR(100), x DECIMAL(9,6), y DECIMAL (9,6) );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、開始点から200キロメートルの距離にあるすべての集落を取得するには、次の手順を実行する必要があります。

 --   src        POINT (  Point()    POINT    ) SELECT @src := Point(x,y) FROM geo WHERE name = ''; --  " "    CALL geobox_pt(@src, 200.0, @top_lft, @bot_rgt); --   SELECT g.name, geodist(X(@src), Y(@src), x, y) AS dist FROM geo g WHERE x BETWEEN X(@top_lft) AND X(@bot_rgt) AND y BETWEEN Y(@top_lft) AND Y(@bot_rgt) HAVING dist < 200.0 ORDER BY dist desc;