Geospatial using maria db

Question

I need to select all points that is within 30 KM radius as a circle from a specific latitude/longitude using maria db.

For example:

latitude = 46.8167
longitude = 6.9333

Answer 1

Nowadays you can do something like this with MariaDB:

CREATE OR REPLACE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    coordinates POINT NOT NULL,
    SPATIAL INDEX(coordinates)
);

INSERT INTO locations (name, coordinates) VALUES
('Location1', POINT(6.9333, 46.8267)),
('Location2', POINT(6.9433, 46.8167)),
('Location3', POINT(6.9633, 46.7967)),
('Location4', POINT(6.9833, 46.7767)),
('Location5', POINT(7.0233, 46.7367)),
('Location7', POINT(7.2333, 46.6167)),
('Location6', POINT(7.1333, 46.7167)),
('Location8', POINT(7.3333, 46.5167)),
('Location9', POINT(7.4333, 46.4167)),
('Location10', POINT(7.5333, 46.3167));

SELECT id, name,
    ST_Distance_Sphere(
        coordinates,
        POINT(6.9333, 46.8167)
    ) / 1000 AS distance_km
FROM locations
WHERE ST_Distance_Sphere(
    coordinates,
    POINT(6.9333, 46.8167)
) <= 30000;