5

Here is the problem,

Already made several researches about it, but did not find any clear and well explained solution about that. Some people are talking about functions like st_distance or st_within which refer to mysql/mariadb versions but these functions seem to have no real documentation and do not seem to fit my problem.

So what I'm tryin to do is :

Get ALL spatial POINTS (lat,ln) which are within a circle with a defined RADIUS in KILOMETRES.

User will give a radius, contained between 1 and 100 km, and will receive all database records that are within that radius.

"points" table looks like this : 

ID -- POINT -- SOMETEXT

Point is the spatial type point used in mySql(>5.6) / mariaDb(5.5.40) (lat,lng)

so, the query should look like this :

"SELECT * FROM points WHERE (function... to get distance) <= definedDistance"

Thanks for help

Sergiy Seletskyy
  • 16,236
  • 7
  • 69
  • 80
Julo0sS
  • 2,096
  • 5
  • 28
  • 52

1 Answers1

-1

I disagree with what you wrote here:

Some people are talking about functions like st_distance or st_within which refer to mysql/mariadb versions but these functions seem to have no real documentation and do not seem to fit my problem.

st_distance is documented and seems to fit your problem very nicely.

See my example, where I'm looking for points inside a circle of radius 1 and center in (2, 2):

CREATE TABLE geom (g GEOMETRY);

INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));
INSERT INTO geom VALUES (GeomFromText('POINT(1 2)'));
INSERT INTO geom VALUES (GeomFromText('POINT(1 3)'));
INSERT INTO geom VALUES (GeomFromText('POINT(1 4)'));
INSERT INTO geom VALUES (GeomFromText('POINT(2 2)'));
INSERT INTO geom VALUES (GeomFromText('POINT(3 3)'));
INSERT INTO geom VALUES (GeomFromText('POINT(4 4)'));

SELECT AsText(g) from geom where ST_Distance(point(2,2), g) <= 1

Output:

POINT(1 2)
POINT(2 2)
Grzegorz Adam Kowalski
  • 5,243
  • 3
  • 29
  • 40
  • 6
    I do not think this answer is correct. MagiaDB ST_Distance function looks like it calculates distance on a cartesian plane (ie. simple X, Y calculations, not latitude, longitude). In this case the function cannot be used for distances between GIS points. See the following; – Rory Mapstone Jul 13 '18 at 08:40
  • 5
    `set @p1 = point (90,0); set @p2 = point (90,50); select ST_Distance(@p1, @p2);` Distance should be 0 as both points are at the north pole. Function gives an answer of 50. PS sorry about the multiple comments, I could not figure how to add code block. – Rory Mapstone Jul 13 '18 at 08:55