mysql: "내 근처의 태국 음식" 또는: 데이터베이스에서 지리적 거리 계산을 수행합니다.

우리 모두는 '내 근처 태국 음식'에 대해 잘 알고 있습니다. 휴대전화에 해당 문구를 입력하면 가까운에 있는 태국 레스토랑 목록이 응답됩니다. 그리고 우리는 그것이 내부적으로 어떻게 작동하는지에 대해 어느 정도 이해하고 있습니다. 구글이나 위도와 경도가 포함된 태국 레스토랑의 데이터베이스를 가지고 있고 휴대폰으로 우리의 위치를 ​​알고 있는 사람은 누구나 '일부 프로세스'를 수행하여 어떤 태국 장소인지 알아냅니다. 근처에 있습니다.

이 게시물에서는 '일부 프로세스' 부분을 살펴보고 mysql을 사용하여 몇 가지 표준 위치 작업을 수행하는 방법을 살펴보겠습니다. 우리는 mysql의 POINT 및 POLYGON 유형을 다루고, 구(인터넷에서 읽은 것과는 달리 지구)의 두 점 사이의 거리를 찾고, 점이 점으로 정의된 다각형 내부에 있는지 확인합니다. , 지구 표면에 좌표가 표시되는 방식을 정의하는 '공간 참조 시스템' 등을 살펴보세요.

한 음식점에서 SQL 인젝션 공격을 시도합니다.

mysql에서 POINT 만들기

mysql에는 공간 데이터 전용 기능과 데이터 유형이 모두 포함되어 있습니다. 그 수는 어지러울 정도로 많고 공식 문서는 범죄 수준에 가깝습니다. 다행스럽게도 우리는 작은 하위 집합만을 사용하여 원하는 작업을 수행할 수 있습니다. POINT부터 시작하겠습니다.

POINT는 데이터 유형이자 해당 데이터 유형을 반환하는 함수입니다. 좋은 구식 x/y 그래프에서 점을 정의하려면 다음과 같이 할 수 있습니다.

SELECT POINT(3, 7);

해당 쿼리의 결과는 POINT 유형 값의 x/y 지점입니다. mysql은 POINT를 바이너리 형식으로 저장하므로 선택 결과는 특별히 유용하지 않습니다.

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

mysql은 한 지점에서 x 및 y 값을 추출하는 두 가지 편의 함수를 제공하여 이 문제를 해결합니다.

• 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로 정의되었습니다. 가로가 먼저, 세로가 두 번째. 이것은 영원히 그래왔고 모두가 동의합니다.

지도를 만드는 사람들은 제외

지도를 만드는 사람들은 지점을 위도/경도로 정의합니다. 물론 위도는 지도에서 수직인 남북으로 이어집니다. 동서 축인 경도는 수평입니다. 본질적으로 지도 사람들은 y/x를 사용하기로 결정했습니다.

분명히 이로 인해 문제가 발생합니다. 앨버타 주 캘거리 중심부에 있는 Ship & Anchor Pub의 위치를 ​​나타내는 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()를 대체하는 두 가지 함수를 제공하여 이 문제를 해결합니다.

• ST_위도()
• 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 유형의 값을 선택했습니다. 현재로서는 잘 작동하지만 상황이 더 복잡해지기 시작할 때 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

위 내용은 mysql: "내 근처의 태국 음식" 또는: 데이터베이스에서 지리적 거리 계산을 수행합니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!