GooglePoiCrawler/utility/googlemapsearch.sql

CREATE TABLE `shop_list` (
   `id` int NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(100),
   `lon` DOUBLE,
   `lat` DOUBLE,
   `city` VARCHAR(10),
   `area` VARCHAR(10),
   `rating` DOUBLE,
   `user_ratings_total` DOUBLE,
   `category` VARCHAR(20),
   `price_level` VARCHAR(10),
   `addr` VARCHAR(100),
   `tel` VARCHAR(20),
   
   `services` JSON ,
   `products` JSON ,
   `choices` JSON ,
   `facilities` JSON ,
   `groups` JSON ,
   `plans` JSON ,
   `payments` JSON ,
   `safeties` JSON ,
   `specials` JSON ,
   `barrierlevels` JSON ,
   `items` JSON ,

   `open_now` VARCHAR(10),
   `periods` JSON,
   `weekday_text` JSON,
   
   `reviews` JSON,

   `shop_photo` JSON,
   `menu_photo` JSON,

   `google_url` VARCHAR(200),
   `query_name` VARCHAR(200),
   `crawler_date` char(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY (`query_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `shop_item_list` (
   `id` int NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(100),
   `lon` DOUBLE,
   `lat` DOUBLE,
   `keyword` VARCHAR(20),
   `item_url` VARCHAR(200),
   `crawler_date` char(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY (`item_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `shop_time_list` (
   `id` int NOT NULL AUTO_INCREMENT,
   `google_url` VARCHAR(200),
   `open_now` VARCHAR(20),
   `day` int,
   `open` VARCHAR(20),
   `close` VARCHAR(20),
   `crawler_date` char(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY (`google_url`,`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `shop_reviews_list` (
   `id` VARCHAR(30),
   `google_url` VARCHAR(200),
   `author_name` VARCHAR(20),
   `profile_photo_url` VARCHAR(200),
   `rating` int,
   `text` VARCHAR(500),
   `created_at` VARCHAR(20),
   `crawler_date` char(20) NOT NULL,
   UNIQUE KEY (`id`,`google_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `shop_reviews_photo_list` (
   `id` VARCHAR(30),
   `google_url` VARCHAR(200),
   `path` VARCHAR(200),
   `crawler_date` char(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY (`id`,`google_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;