CODEWITHSUNDEEP
mariadbgeospatialmariasqlmariadb-connect-engine
midhun k
midhun k

Reputation: 1068

Geospatial using maria db

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

Upvotes: 3

Views: 914

Answers (1)

Alejandro Duarte
Alejandro Duarte

Reputation: 1479

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;

Upvotes: 2

Related Questions