差分

このページの2つのバージョン間の差分を表示します。

--- sanmei:database [2020/06/19 01:13] – [Table:city] anineco
+++ sanmei:database [2020/06/19 03:50] (現在) – 削除 anineco
@@ 行 1: / 行 1: @@
-====== 山名データベース ======
-===== データベースの作成 =====
-==== Database:nurude_tozan ====
-<code sql>
-CREATE DATABASE `nurude_tozan`
-/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
-/*!80016 DEFAULT ENCRYPTION='N' */
-</code>
-==== Table:geo ====
-<code sql>
-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
-</code>
-==== Table:sanmei ====
-<code sql>
-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
-</code>
-==== Table:meizan ====
-<code sql>
-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
-</code>
-==== Table:record ====
-<code sql>
-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
-</code>
-==== Table:explored ====
-<code sql>
-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
-</code>
-==== Table:poi ====
-<code sql>
-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
-</code>
-==== Table:gyosei ====
-<code sql>
-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
-</code>
-<code sql>
-set global max_allowed_packet = 16777216;
-show variables like 'max_allowed_packet';
-</code>
-<code perl>
-#!/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__
-</code>
-==== Table:city ====
-<code sql>
-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
-</code>
-<code perl>
-#!/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__
-</code>