[SOLVED] Get closest points of a point from a DB

Get closest points of a point from a DB

I use a MariaDB database where I store a huge amount of points (latitude and longitude) using Hibernate jts.geom.Point type. In the database is stored as geometry type.

I want to know how could I get the closest 10 points given a certain point. How could I do that using a SQL query? Or maybe a query and post-code in the backend.

Thanks!

Solution

For example you can do:

create table t (loc point);

insert into t (loc) values
  (POINT(10, 0)), 
  (POINT(15, 20)),
  (POINT(50, 50));

Then you can find the closest two points to (49, 49):

select *,
  st_distance_sphere(loc, POINT(49, 49)) as dist
from t
order by st_distance_sphere(loc, POINT(49, 49)) 
limit 2 -- only the closest two points

Result:

loc              dist
---------------  -----------------
{"x":50,"y":50}  132584.0664606239
{"x":15,"y":20}  4416195.256674154

See running example at DB Fiddle.

Now, this works, but with millions of rows it won't be very efficient. You'll need to prefilter using "rectangles" or "areas", so an index can be of use.

EDIT for MariaDB

MariaDB's syntax is slightly different. See below:

select ST_AsText(t.loc) as point, 
  ST_Distance(loc, POINT(49, 49)) as dist
from t
order by ST_Distance(loc, POINT(49, 49))
limit 2;

Result:

point         dist
------------  ------------------
POINT(50 50)  1.4142135623730951
POINT(15 20)  44.68780594300866

See running example at db<>fiddle.