MySQL과 Ruby를 사용하여 간단한 지도 탐색 기능을 구현하는 방법

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    latitude DECIMAL(9, 6) NOT NULL,
    longitude DECIMAL(9, 6) NOT NULL
);

CREATE TABLE routes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    start_location_id INT NOT NULL,
    end_location_id INT NOT NULL,
    distance DECIMAL(9, 2) NOT NULL,
    FOREIGN KEY (start_location_id) REFERENCES locations(id),
    FOREIGN KEY (end_location_id) REFERENCES locations(id)
);

gem install mysql2

require 'mysql2'

client = Mysql2::Client.new(
    host: 'localhost',
    username: 'root',
    password: 'password',
    database: 'maps'
)

results = client.query('SELECT * FROM locations')

results.each do |row|
    puts "ID: #{row['id']}, Name: #{row['name']}, Latitude: #{row['latitude']}, Longitude: #{row['longitude']}"
end

client.close

require 'mysql2'
require 'dijkstra'

client = Mysql2::Client.new(
    host: 'localhost',
    username: 'root',
    password: 'password',
    database: 'maps'
)

routes = client.query('SELECT * FROM routes')
locations = Hash.new

routes.each do |row|
    start_location_id = row['start_location_id']
    end_location_id = row['end_location_id']
    distance = row['distance']

    locations[start_location_id] ||= Hash.new
    locations[start_location_id][end_location_id] = distance
end

graph = Dijkstra::Graph.new(locations)

shortest_path = graph.shortest_path(start_location_id, end_location_id)

shortest_distance = shortest_path.distance
shortest_path.each do |location_id|
    location = client.query("SELECT * FROM locations WHERE id = #{location_id}").first
    puts "#{location['name']}: #{location['latitude']}, #{location['longitude']}"
end

puts "Shortest Distance: #{shortest_distance}"

client.close