• API
• FAQ
• Tools
• Archive
SHARE
TWEET

geo point

krot Mar 29th, 2019 (edited) 156 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
1. For MySQL 5.7+
2.
3. Given we have the following simple table,
4.
5. create table example (
6.   id bigint not null auto_increment primary key,
7.   lnglat point not null
8. );
9.
10. create spatial index example_lnglat
11.     on example (lnglat);
12.
13. With the following simple data,
14. insert into example (lnglat)
15. values
16. (point(-2.990435, 53.409246)),
17. (point(-2.990037, 53.409471)),
18. (point(-2.989736, 53.409676)),
19. (point(-2.989554, 53.409797)),
20. (point(-2.989350, 53.409906)),
21. (point(-2.989178, 53.410085)),
22. (point(-2.988739, 53.410309)),
23. (point(-2.985874, 53.412656)),
24. (point(-2.758019, 53.635928));
25.
26. POINT(lng, lat)
27. You would get the points within a given range of another point (note: we have to search inside a polygon) with the following combination of st functions:
28.
29. set @px = -2.990497;
30. set @py = 53.410943;
31. set @range = 150; -- meters
32. set @rangeKm = @range / 1000;
33.
34. set @search_area = st_makeEnvelope (
35.   point((@px + @rangeKm / 111), (@py + @rangeKm / 111)),
36.   point((@px - @rangeKm / 111), (@py - @rangeKm / 111))
37. );
38.
39. select id,
40.        st_x(lnglat) lng,
41.        st_y(lnglat) lat,
42.        st_distance_sphere(point(@px, @py), lnglat) as distance
43.   from example
44.  where st_contains(@search_area, lnglat);
45.
46.
47. You should see something like this as a result:
48.
49. 3   -2.989736   53.409676   149.64084252776277
50. 4   -2.989554   53.409797   141.93232714661812
51. 5   -2.98935    53.409906   138.11516275402533
52. 6   -2.989178   53.410085   129.40289289527473
53.
54. For reference on distance, if we remove the constraint the result for the test point looks like this:
55.
56. 1   -2.990435   53.409246   188.7421181457556
57. 2   -2.990037   53.409471   166.49406509160158
58. 3   -2.989736   53.409676   149.64084252776277
59. 4   -2.989554   53.409797   141.93232714661812
60. 5   -2.98935    53.409906   138.11516275402533
61. 6   -2.989178   53.410085   129.40289289527473
62. 7   -2.988739   53.410309   136.1875540498202
63. 8   -2.985874   53.412656   360.78532732013963
64. 9   -2.758019   53.635928   29360.27797292756
65.
66. Note 1: the field is called lnglat since that's the correct order if you think of points as (x, y) and is also the order most functions (like point) accept the parameter
67.
68. Note 2: you can't actually take advantage of spatial indexes if you were to use circles; also note that the point field can be set to accept null but spatial indexes can't index it if it's nullable (all fields in the index are required to be non-null).
69.
70. Note 3: st_buffer is considered (by the documentation) to be bad for this use case
71.
72. Note 4: the functions above (in particular st_distance_sphere) are documented as fast but not necessarily super accurate; if your data is super sensitive to that add a bit of wiggle room to the search and do some fine tuning to the result set
74.
75. SELECT ST_Buffer( point(1.967817, 2.043222), 1)
76. SELECT
77. contains(
78.     ST_Buffer( point(1.967817, 2.043222), 1),
79.           point(1.967817, 2.043222)
80.          )
81.          SELECT
82.   contains(
83.      st_makeEnvelope (
84.   point((1.967817 + 100 / 111), (2.043222 + 100 / 111)),
85.   point((1.967817 - 100 / 111), (2.043222 - 100 / 111))
86.     )
87.       ,
88.           point(1.967817, 2.043222)
89.          )
90.
91. https://dev.mysql.com/doc/refman/5.6/en/spatial-operator-functions.html
92. https://www.percona.com/blog/2013/10/21/using-the-new-mysql-spatial-functions-5-6-for-geo-enabled-applications/