Advertisement
krot

geo point

Mar 29th, 2019
397
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 3.40 KB | None | 0 0
  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
  73. //https://stackoverflow.com/questions/2411528/query-points-within-a-given-radius-in-mysql
  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/
  93. https://mariadb.com/kb/en/library/st_centroid/
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement