database_table_schemas.sql

-- Description: This file contains the SQL statements to create the tables to store two geosocial datasets into a MySQL/MariaDB database
-- The datasets are available at http://snap.stanford.edu/data/loc-gowalla.html
-- and http://snap.stanford.edu/data/loc-brightkite.html

-- Create database and tables to store the Gowalla and Brightkite datasets
DROP DATABASE IF EXISTS geosocial_db; -- Drop the database if it exists (comment this line if you want to keep the old database
CREATE DATABASE geosocial_db; -- Create the geosocial_db database (comment this line if you want to keep the old database

-- Switch the geosocial_db database
USE geosocial_db;

-- Create the gowalla_graph table
CREATE TABLE gowalla_graph (
src_user_id int not null,
dst_user_id int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create indexes on the gowalla_graph table to speed up the queries
CREATE INDEX gowalla_graph_index_0 ON gowalla_graph (src_user_id) USING HASH;
CREATE INDEX gowalla_graph_index_1 ON gowalla_graph (dst_user_id) USING HASH;

-- Create the gowalla_checkins table
CREATE TABLE gowalla_checkins (
user_id int not null,
checkin_timestamp timestamp not null,
latitude float not null,
longitude float not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create indexes on the gowalla_checkins table to speed up the queries
CREATE INDEX gowalla_checkins_index_0 ON gowalla_checkins (user_id) USING HASH;

-- Create the brightkite_graph table
CREATE TABLE brightkite_graph (
src_user_id int not null,
dst_user_id int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create indexes on the brightkite_graph table to speed up the queries
CREATE INDEX brightkite_graph_index_0 ON brightkite_graph (src_user_id) USING HASH;
CREATE INDEX brightkite_graph_index_1 ON brightkite_graph (dst_user_id) USING HASH;

 -- Create the brightkite_checkins table
CREATE TABLE brightkite_checkins (
user_id int not null,
checkin_timestamp timestamp not null,
latitude float not null,
longitude float not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create indexes on the brightkite_checkins table to speed up the queries
CREATE INDEX brightkite_checkins_index_0 ON brightkite_checkins (user_id) USING HASH