mysql: "makanan thai berdekatan saya", atau: melakukan pengiraan jarak geo dalam pangkalan data anda.

私たちは皆、「近くのタイ料理」ということについてよく知っています。そのフレーズを携帯電話に入力すると、あなたの近くにあるタイ料理レストランのリストが表示されます。そして、私たちはそれが内部でどのように機能するかを一種の理解しています。グーグルか誰かが緯度と経度を含むタイ料理レストランのデータベースを持っていて、携帯電話から私たちの位置を知っていて、どのタイ料理店を把握するために「何らかのプロセス」を実行します。近くにいます。

この投稿では、「一部のプロセス」の部分について説明し、mysql を使用して標準的な位置情報処理を行う方法を見ていきます。 mysql の POINT 型と POLYGON 型について説明し、球 (インターネットで読んだ内容とは異なり地球です) 上の 2 点間の距離を求め、点が点で定義された多角形の内側にあるかどうかを判断します。 、地球の表面に座標がどのようにプロットされるかを定義する「空間参照系」などにも注目してください。

レストランが SQL インジェクション攻撃を試みています。

mysqlでポイントを作成する

mysql には、空間データ専用の一連の関数とデータ型があります。その数は目もくらむほどで、公式文書は犯罪的なほど膨大です。幸いなことに、小さなサブセットのみを使用して、やりたいことを達成できます。まずはポイントから始めましょう。

POINT はデータ型であると同時に、そのデータ型を返す関数でもあります。古き良き x/y グラフ上に点を定義したい場合は、次のように行うことができます:

SELECT POINT(3, 7);

そのクエリの結果は、POINT 型の値の x/y 点です。 mysql は POINT をバイナリ形式で保存するため、選択の結果は特に役に立ちません:

SELECT POINT(3, 7);
+------------------------------------------------------+
| POINT(3, 7)                                          |
+------------------------------------------------------+
| 0x00000000010100000000000000000008400000000000001C40 |
+------------------------------------------------------+

mysql は、点から x 値と y 値を抽出する 2 つの便利な関数を提供することでこの問題に対処します。

• ST_X()
• ST_Y()

どちらも引数として POINT 値を受け入れます。例:

SELECT ST_X(POINT(3,7)) AS x, ST_Y(POINT(3,7)) AS y;
+------+------+
| x    | y    |
+------+------+
|    3 |    7 |
+------+------+

POINT はデータ型であるため、INT や VARCHAR と同じようにテーブル定義で使用できます。

CREATE TABLE `some_coords` (
  `coords` POINT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

POINT 型の列がある場合、そこに入力できるのは POINT データのみです。これについては後ほど詳しく説明します。

X、Y、マップと標準の欠如についての短い余談

私たちは皆、水平方向の x 軸と垂直方向の y 軸を使用して、青い線が入った方眼紙に点をプロットする方法を学校で学びました。点は x/y として定義されました。最初に水平、2番目に垂直。これは永遠のやり方であり、誰もがそれに同意します。

地図を作成する人々を除きます。

地図を作成する人々は、地点を緯度/経度として定義します。もちろん、緯度は南北に伸びており、地図上では垂直になります。経度、つまり東西軸は水平です。本質的に、地図担当者は y/x を使用することに決めました。

これは明らかに問題を引き起こします。アルバータ州カルガリー中央にあるシップ & アンカー パブ (私が時々ブログを書いていることで知られる場所です) の場所を表す POINT を作成すると何が起こるかを見てみましょう

SELECT ST_X(POINT(51.037913, -114.073277)) as longitude, ST_Y(POINT(51.037913, -114.073277)) as latitude;
+-----------+-------------+
| longitude | latitude    |
+-----------+-------------+
| 51.037913 | -114.073277 |
+-----------+-------------+

緯度と経度が混同されています。私たちのパブは間違った場所にあります。さらに悪いことに、緯度の最大値は 90 であるため、船と錨を宇宙のどこかに置いたことになります。良くないです。

mysql は、地図または地球上の点を使用するときに ST_X() と ST_Y() を置き換える 2 つの関数を提供することで、この問題に対処しています。

• ST_Latitude()
• ST_経度()

これは良いことですが、上記のクエリで使用しようとすると、次のエラー メッセージが表示されます。

ERROR 3726 (22S00): Function st_latitude is only defined for geographic spatial reference systems, but one of its arguments is in SRID 0, which is not geographic.

このエラーは難しそうに見えますが (SRID 0 とは一体何ですか?)、ここで mysql が伝えているのは、使用している POINT が マップ ポイント として定義されていないということだけです。それらは、X と Y が入った普通の古いバッグです。

SRID と SRS については後ほど説明します。

緯度と経度は常に y/x です。

ポイントを作るためのより良い方法: よく知られたテキスト

これまで、関数 POINT() を使用して POINT 型の値を選択してきました。今のところこれで問題なく動作しますが、物事がより複雑になり始めたときに、POINT と POLYGON の操作を簡単にする、より優れた、より柔軟な方法があります。

ST_GeomFromText() 関数は、作成する幾何学オブジェクト (この場合は POINT) のテキスト式 (文字列) を引数として受け取り、正しいタイプの値を返します。

these text expressions are formatted using a syntax called "well-known text". the format is, basically, the name of the geometric object you want to create (ie. POINT) and the coordinates that define it. let's look:

SELECT ST_GeomFromText('POINT(51.037913 -114.073277)');

this looks very straightforward, but there's a glaring question: where is the comma separating the arguments in our POINT call?

the answer is that the well-known text here isn't a call to the function POINT(), it's a definition of the data type POINT.

back at the beginning of this discussion, we went over how POINT is both a function and a datatype. when we use POINT() as a function, the coordinates are arguments that are separated by a comma. when we define a value using POINT as a type, the coordinates do not take a comma.

we can use ST_GeomFromText() to create any sort of geometric object that's defined in the well-known text. there aren't many of these, and we'll be sticking in this post to POINTs and POLYGONs (which include things like squares and triangles).

spatial reference systems: not all points are the same

on my desk i have a small chess board where i occasionally work through annotated games. it's my idea of "fun". that chess board is a coordinates system. i also have a large, widescreen computer monitor on my desk. it's a coordinate system as well.

however, just because my chess board and monitor are both coordinate systems doesn't mean that the coordinates from one can be transferred to the other. the x/y position of my white bishop is meaningless on my monitor; that x/y point only has meaning in the context of the chess board.

a context defines things like the origin points, axes, units of measurement and the like. useful stuff that helps us make sense of what a coordinate actually means.

when it comes to plotting points and lines and polygons on the surface of the earth, that context is called a 'spatial reference system', or SRS.

there are a lot of different SRSs. a lot. some of them treat the earth as a sphere, others as a projected flat map. some cover the entire planet, many more only deal with a sub region, like a country. some include a z axis from the center of the earth, most don't.

if we want to peruse all the different SRSs that mysql has, we can run this select:

SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS;

there are about five thousand of them.

fortunately, we don't need to read through all of these to choose one. we're just going to use 4326, a global, unprojected coordinate system that (just about) everybody uses.

that 4326 number is the id of the SRS. it's called, unsurprisingly, an SRID. if we remember back to when we tried to call the ST_Latitude() function on the POINT we made, we got the error:

ERROR 3726 (22S00): Function st_latitude is only defined for geographic spatial reference systems, but one of its arguments is in SRID 0, which is not geographic.

now that we have more of an understanding about SRSs, we can see that here mysql is complaining that we are asking for the latitude, but the SRS of our POINT isn't one that uses latitude and longitude. the SRS we are using, according to the error message, is SRID 0.

SRID 0 is just a 'flat, cartesian plane' with no units. think of it as a sheet of that blue-lined graph paper from math class stretching off into infinity in all directions. this is a great SRS for some applications, but is not very meaningful for using latitude and longitude to map places on a spherical earth. SRID 0 is the default SRS that mysql assigns to POINTs (and other shapes) when one is not specified.

by comparison, the 4326 SRS is specifically designed for global mapping. it treats the surface of the earth as an ellipsoid, uses degrees for measurement and defines the axes as the equator and prime meridian. exactly what we want. 4326 is, in turn, based on a big set of data about the earth called the world geodetic system 1984, or WSG84, that was compiled in that year in an effort to unify and standardize the mishmash of national mapping data. if you're one of those 'further reading' types, you can read over a detailed explainer on SRID 4326 here or peruse the surprisingly-entertaining wikipedia entry on WSG84.

a developer accidentally uses SRID 0 for their geolocation select.

actually using SRID 4326

using SRID 4326 as our SRS when creating a POINT is pretty straightforward; we just add the SRID as a second argument to ST_GeomFromText().

SELECT ST_GeomFromText('POINT(51.037913 -114.073277)', 4326);

and, just like that, our x/y values are now treated as longitude and latitude coordinates on earth. let's try ST_Latitude() again:

SELECT ST_Latitude(ST_GeomFromText('POINT(51.037913 -114.073277)', 4326)) AS latitude;
+-----------+
| latitude  |
+-----------+
| 51.037913 |
+-----------+

exactly what we wanted.

creating a table for our POINTs

selecting geometric data like POINTs (or POLYGONs or LINESTRINGs) created using literal data is fine, but what we probably want to do is persist that data in a table so we can use it later. let's do that. we'll start with creating our table.

CREATE TABLE `calgary` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `coords` POINT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

here, we've defined a pretty standard-looking table of notable locations in the city of calgary, alberta. the interesting column here is coords, which is defined as a POINT.

that POINT doesn't have an SRS associated with it. this means that on every insert, we will have to define the SRID we are using for our point. this is very flexible, but if we want to we can add the SRS to the column definition.

CREATE TABLE `calgary` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `coords` POINT SRID 4326 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

by defining our coords column as POINT SRID 4326 we are enforcing that any POINT in that column must be of SRID 4326. if we try to insert a point that has a different SRID, mysql will complain with an error like:

ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'coords'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.

for all the examples going forward, we will be using a table with a coords column that does not define the SRID.

now that we have a table, we can insert some rows. we'll add a list of calgary landmarks.

INSERT INTO calgary VALUES (null, 'calgary tower', ST_GeomFromText('POINT(51.044270 -114.062019)', 4326));
INSERT INTO calgary VALUES (null, 'peace bridge', ST_GeomFromText('POINT(51.0542 -114.0793)', 4326));
INSERT INTO calgary VALUES (null, 'saddledome', ST_GeomFromText('POINT(51.0374 -114.0519)', 4326));
INSERT INTO calgary VALUES (null, 'national music centre', ST_GeomFromText('POINT(51.04250 -114.06083)', 4326));
INSERT INTO calgary VALUES (null, 'baitun nur mosque', ST_GeomFromText('POINT(51.101743 -113.972039)', 4326));
INSERT INTO calgary VALUES (null, 'olympic oval', ST_GeomFromText('POINT(51.07694 -114.13556)', 4326));
INSERT INTO calgary VALUES (null, 'heritage park', ST_GeomFromText('POINT(50.98528 -114.10833)', 4326));
INSERT INTO calgary VALUES (null, 'international avenue', ST_GeomFromText('POINT(51.03778 -113.98167)', 4326));
INSERT INTO calgary VALUES (null, 'fort calgary', ST_GeomFromText('POINT(51.045139 -114.045778)', 4326));

there's a lot of things to see in calgary!

in these insert statements, we create our point using ST_GeomFromText() and set the SRID as 4326 like so:

ST_GeomFromText('POINT(51.0542 -114.0793)', 4326)

we can then select this data back, getting the latitude and longitude of each location with ST_latitude() and ST_longitude().

SELECT  id,
        name,
        ST_Latitude(coords) AS latitude,
        ST_Longitude(coords) AS longitude
FROM    calgary;
+----+-----------------------+-----------+-------------+
| id | name                  | latitude  | longitude   |
+----+-----------------------+-----------+-------------+
|  1 | calgary tower         |  51.04427 | -114.062019 |
|  2 | peace bridge          |   51.0542 |   -114.0793 |
|  3 | saddledome            |   51.0374 |   -114.0519 |
|  4 | national music centre |   51.0425 |  -114.06083 |
|  5 | baitun nur mosque     | 51.101743 | -113.972039 |
|  6 | olympic oval          |  51.07694 |  -114.13556 |
|  7 | heritage park         |  50.98528 |  -114.10833 |
|  8 | international avenue  |  51.03778 |  -113.98167 |
|  9 | fort calgary          | 51.045139 | -114.045778 |
+----+-----------------------+-----------+-------------+

at last, calculating distance

so far, we've made some spatial POINTs and assigned them to SRID 4326 so we can actually make sense of them as latitude and longitude. it's finally time to focus on what we really want to do: getting the distance between two points.

to do this, we're going to use mysql's ST_Distance_Sphere() function.

as one would expect, ST_Distance_Sphere() calculates the distance between two points, provided as arguments to the function, on a sphere. the distance returned will always be the shortest one (since, on a sphere, we can always go the opposite direction and travel further to get to the same place). the unit of measurement is meters.

ST_Distance_Sphere() takes an optional third argument: the radius of the sphere. if we do not set this argument, the value 6,370,986 meters is used. that's the radius of the earth, and is the value we almost certainly want to use.

knowing all that, an example select would look like:

SELECT  name,
        ST_Distance_Sphere(ST_GeomFromText('POINT(51.037913 -114.073277)', 4326), coords) AS distance_meters
FROM calgary;
+-----------------------+--------------------+
| name                  | distance_meters    |
+-----------------------+--------------------+
| calgary tower         | 1057.9217149476015 |
| peace bridge          |  1859.336539883446 |
| saddledome            | 1495.7790780297603 |
| national music centre | 1008.7085120625501 |
| baitun nur mosque     |  10020.62038333001 |
| olympic oval          | 6146.6116509785015 |
| heritage park         |  6345.541637300453 |
| international avenue  |  6405.199613693066 |
| fort calgary          |  2083.730747912871 |
+-----------------------+--------------------+

here we can see that we passed two POINT arguments to ST_Distance_Sphere(). The first is one we constructed from literal values using ST_GeomFromText(). it's the location of the ship & anchor pub in central calgary, where i promise i am not writing this post. the second argument is our coords column.

the result is the distance from our starting POINT, the ship & anchor, to all the POINTs in our table, in meters.

from here, building 'near me' functionality is just a matter of applying a WHERE or ORDER BY clause.

going regional: finding points inside a square (or any shape)

perhaps, instead of a basic 'near me' feature, we want our users to be able to draw a square on a map and say "show me all the calgary landmarks in here."

to do this, the fist step we need to take is defining a square.

creating a square

a square is a type of polygon, and mysql provides a POLYGON data type that we can use to describe a square (or any shape). POLYGONs are defined by a set of coordinates that identify the corners of the shape. this means, to create a square, we provide POLYGON with five coordinate sets.

wait, five? don't we mean four? a square has four corners, after all.

the important thing to note here is that a polygon must be closed. this means that the first coordinate set and the last coordinate set must be the same. it completes the shape by going back to the beginning. the result is that a square is defined has having five sets of coordinates. to illustrate, let's look at this glorious ascii diagram that shows the five coordinates that create a square.

1/5 ---- 4
  |      |    
  |      |    
  2 ---- 3

with that in mind, we can create a square of latitude and longitude values. the example we'll be using is this square covering most of downtown calgary.

a square covering most of downtown calgary.

to select this as a POLYGON in mysql, we would do:

SELECT ST_GeomFromText('POLYGON( (  51.053913 -114.094391, 51.028008 -114.094391, 51.028008 -114.037743, 51.053913 -114.037743, 51.053913 -114.094391) )', 4326);

given our experience creating a POINT, this should be fairly straightforward. the only difference is that instead of passing one coordinate set to POINT, we pass five to POLYGON. the result is a geometric shape, stored in a binary format, that we can use for comparisons against POINTS or, even, other POLYGONs.

finding POINTs 'within' a square

we now have a POLYGON defined from some literal values, and a table full of POINTs, all that's left is to find out which POINTs in our table are inside our POLYGON. we can do this with the mysql function ST_Within(). here's an example:

SELECT  name,
        ST_Latitude(coords) AS latitude,
        ST_Longitude(coords) AS longitude
FROM    calgary
WHERE   ST_Within(
            coords,
            ST_GeomFromText('POLYGON( (  51.053913 -114.094391, 51.028008 -114.094391, 51.028008 -114.037743, 51.053913 -114.037743, 51.053913 -114.094391) )', 4326)
         )

we can see that ST_Within() takes two arguments: a POINT, and a POLYGON. if the POINT is 'within' the POLYGON, ST_Within() returns 1. if it isn't, we get a 0.

conclusion

once we have an understanding of how to create POINTs and POLYGONs and use ST_Distance_Sphere() and ST_Within() we can combine and extrapolate them to get more complex data, like "the closest daycare in a given school district" or "all the burrito busses on this side of the river" or, even, answer the question that has driven so many of the great minds in computer science: "where is a thai restaurant near me"?

? this post originally appeared in the grant horwood technical blog

Atas ialah kandungan terperinci mysql: "makanan thai berdekatan saya", atau: melakukan pengiraan jarak geo dalam pangkalan data anda.. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!