CREATE DATABASE `nurude_tozan`
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
/*!80016 DEFAULT ENCRYPTION='N' */

CREATE TABLE `geo` (
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `act` tinyint(1) NOT NULL COMMENT '0:無効，1:有効',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  `alt` SMALLINT NOT NULL COMMENT '標高[m]',
  `lat` mediumint NOT NULL COMMENT '緯度（dms）',
  `lon` mediumint NOT NULL COMMENT '経度（dms）',
  `auth` tinyint UNSIGNED DEFAULT '0' COMMENT '出典',
  `note` text COMMENT '記事',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `sanmei` (
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `type` tinyint NOT NULL COMMENT '0:総称，1:山名，2:別名',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  UNIQUE KEY `idx_sanmei` (`id`,`kana`,`name`),
  KEY `name` (`name`) USING BTREE,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `meizan` (
  `cat` tinyint UNSIGNED NOT NULL COMMENT 'カテゴリ',
  `seqno` SMALLINT UNSIGNED NOT NULL COMMENT 'カテゴリ内の順序',
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  UNIQUE KEY `idx_meizan` (`cat`,`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `record` (
  `rec` SMALLINT UNSIGNED NOT NULL COMMENT '山行記録ID',
  `start` DATE NOT NULL COMMENT '開始日',
  `end` DATE NOT NULL COMMENT '終了日',
  `issue` DATE DEFAULT NULL COMMENT '公開日',
  `title` VARCHAR(255) NOT NULL COMMENT 'タイトル',
  `summary` VARCHAR(255) DEFAULT NULL COMMENT '概略',
  `link` VARCHAR(255) DEFAULT NULL COMMENT '山行記録URL',
  `image` VARCHAR(255) DEFAULT NULL COMMENT '画像URL',
  PRIMARY KEY (`rec`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `explored` (
  `rec` SMALLINT UNSIGNED NOT NULL COMMENT '山行記録ID',
  `start` DATE NOT NULL COMMENT '開始日',
  `summit` DATE NOT NULL COMMENT '登頂日',
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  UNIQUE KEY `idx_explored` (`rec`,`id`),
  KEY `id` (`id`) USING BTREE,
  KEY `rec` (`rec`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `poi` (
  `ptid` mediumint UNSIGNED NOT NULL COMMENT 'PTID',
  `act` tinyint(1) NOT NULL COMMENT '0:無効, 1:有効',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  `alt` SMALLINT NOT NULL COMMENT '標高[m]',
  `lat` mediumint NOT NULL COMMENT '緯度（dms)',
  `lon` mediumint NOT NULL COMMENT '経度（dms）',
  `id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'ID',
  `c` tinyint NOT NULL DEFAULT '-1' COMMENT '一致度',
  PRIMARY KEY (`ptid`),
  KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `gyosei` (
  `code` SMALLINT UNSIGNED NOT NULL,
  `area` geometry NOT NULL /*!80003 SRID 4326 */,
  SPATIAL KEY `area` (`area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SET global max_allowed_packet = 16777216;
SHOW VARIABLES LIKE 'max_allowed_packet';

#!/usr/bin/env perl
use strict;
use warnings;
use utf8;
use open ':utf8';
use open ':std';
use JSON;
use DBI;
use DBD::mysql;
 
my $dbh = DBI->connect(
  'DBI:mysql:database=nurude_tozan;mysql_socket=/opt/local/var/run/mysql8/mysqld.sock',
  'nurude', '_PASSWORD_',
  {mysql_enable_utf8mb4 => 1, mysql_server_prepare => 1}
) or die $DBI::errstr;
 
open(my $in, '<', 'N03/N03-19_190101.geojson') or die($!);
 
while (my $json_text = <$in>) {
  next if ($json_text !~ /"Feature"/);
  chomp($json_text);
  chop($json_text) if ($json_text =~ /,$/);
  my $data = from_json($json_text); # croaks on error
  my $code = $data->{properties}->{N03_007} || 0; # 行政区域コード（null: 所属未定地）
  print 'code=', $code, "\n";
  my $area = '{"type":"Polygon","coordinates":' . to_json($data->{geometry}->{coordinates}) . '}';
  my $sth = $dbh->prepare(q{SET @area=?});
  $sth->execute(($area));
  $sth->finish;
  $sth = $dbh->prepare(q{INSERT INTO gyosei VALUES (?,ST_GeomFromGeoJSON(@area,1,4326))});
  $sth->execute(($code));
  $sth->finish;
}
 
close($in);
$dbh->disconnect;
__END__

CREATE TABLE `city` (
  `code` SMALLINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

#!/usr/bin/env perl
use strict;
use warnings;
use utf8;
use open ':utf8';
use open ':std';
use URI;
use Web::Scraper;
 
my $codes = scraper {
  process 'table tr', 'codes[]' => scraper {
    process 'td:nth-child(1)', 'code' => 'TEXT';
    process 'td:nth-child(2)', 'name' => 'TEXT';
  };
};
 
my $uri = URI->new('http://nlftp.mlit.go.jp/ksj/gml/codelist/AdminAreaCd.html');
my $res = $codes->scrape($uri);
 
for my $code (@{$res->{codes}}) {
  print $code->{code}, ',', $code->{name}, "\n";
}
__END__