差分

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

--- sanmei:database [2020/06/19 01:29] – [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>
-===== データベースの検索 =====
-<code php>
-<?php
-require_once './init.php';
-$cf = set_init();
-function deg($s) {
-  preg_match('/^(\d+)(\d\d)(\d\d)$/', $s, $m);
-  return sprintf('%.6f', $m[1] + $m[2] / 60 + $m[3] / 3600);
-}
-$dsn = "mysql:dbname=$cf[database];unix_socket=$cf[socket];charset=utf8mb4";
-$dbh = new PDO($dsn, $cf['user'], $cf['password']);
-$type = !empty($_POST) ? INPUT_POST : INPUT_GET;
-$mode = 'end';
-$val = null;
-foreach (array('cat', 'id', 'rec', 'rgc', 'q') as $i) {
-  $val = filter_input($type, $i);
-  if (isset($val)) {
-    $mode = $i;
-    break;
-  }
-}
-#
-# 総称名
-#
-$g_kana = array();
-$g_name = array();
-$sth = $dbh->prepare('SELECT id,kana,name FROM sanmei WHERE type=0');
-$sth->execute();
-while ($row = $sth->fetch(PDO::FETCH_OBJ)) {
-  $g_kana[$row->id] = $row->kana;
-  $g_name[$row->id] = $row->name;
-}
-$sth = null;
-if ($mode === 'cat') {
-#
-# GeoJSON出力
-#
-  $v = filter_input($type, 'v');
-  if ($val == 0) {
-    if ($v == 0) {
-      $sql = <<<'EOS'
-SELECT id,name,lat,lon,1 AS c
-FROM geo
-WHERE act>0
-EOS;
-    } else if ($v == 1) {
-#
-# 山行記録のある山を抽出
-#
-      $sql = <<<'EOS'
-SELECT id,name,lat,lon,1 AS c
-FROM geo
-JOIN (
- SELECT DISTINCT id
- FROM explored
- JOIN (
-  SELECT *
-  FROM record
-  WHERE link IS NOT NULL
- ) AS r USING (rec)
-) AS e USING (id)
-EOS;
-    } else if ($v == 2) {
-#
-# 山+山行記録数を抽出 ※0ではなくNULLが返る
-#
-      $sql = <<<'EOS'
-SELECT id,name,lat,lon,c
-FROM geo
-LEFT JOIN (
- SELECT id,COUNT(rec) AS c
- FROM explored
- JOIN (
-  SELECT *
-  FROM record
-  WHERE link IS NOT NULL
- ) AS r USING (rec) GROUP BY id
-) AS e USING (id)
-WHERE act>0
-EOS;
-    }
-  } else {
-    if ($v == 0) {
-#
-# 名山カテゴリを指定して抽出
-#
-      $sql = <<<'EOS'
-SELECT id,m.name,lat,lon,1 AS c
-FROM geo
-JOIN (
- SELECT *
- FROM meizan
- WHERE cat=?
-) AS m USING (id)
-EOS;
-    } else if ($v == 1) {
-#
-# 名山カテゴリを指定して山行記録のある山を抽出
-#
-      $sql = <<<'EOS'
-SELECT id,m.name,lat,lon,1 AS c
-FROM geo
-JOIN (
- SELECT *
- FROM meizan
- WHERE cat=?
-) AS m USING (id)
-JOIN (
- SELECT DISTINCT id
- FROM explored
- JOIN (
-  SELECT *
-  FROM record
-  WHERE link IS NOT NULL
- ) AS r USING (rec)
-) AS e USING (id)
-EOS;
-    } else if ($v == 2) {
-#
-# 名山カテゴリを指定して山＋山行記録数を抽出 ※0ではなくNULLが返る
-#
-      $sql = <<<'EOS'
-SELECT id,m.name,lat,lon,c
-FROM geo
-JOIN (
- SELECT *
- FROM meizan
- WHERE cat=?
-) AS m USING (id)
-LEFT JOIN (
-  SELECT id,COUNT(rec) AS c
-  FROM explored
-  JOIN (
-   SELECT *
-   FROM record
-   WHERE link IS NOT NULL
-  ) AS r USING (rec) GROUP BY id
-) AS e USING (id)
-EOS;
-    }
-  }
-  $sth = $dbh->prepare($sql);
-  if ($val != 0) {
-    $sth->bindValue(1, $val, PDO::PARAM_INT);
-  }
-  $sth->execute();
-  header('Content-type: application/geo+json');
-  echo '{"type":"FeatureCollection","features":[', PHP_EOL;
-  $count = 0;
-  while ($row = $sth->fetch(PDO::FETCH_OBJ)) {
-    if ($count > 0) {
-      echo ',', PHP_EOL;
-    }
-    $id = $row->id;
-    $name = $row->name;
-    if ($val == 0 && isset($g_name[$id])) {
-      $name = $g_name[$id] . '・' . $name;
-    }
-    $lat = deg($row->lat);
-    $lon = deg($row->lon);
-    $c = $row->c ? 1 : 0;
-    echo <<<EOS
-{"id":"$id","type":"Feature","properties":{"name":"$name","c":"$c"},
-"geometry":{"type":"Point","coordinates":[$lon,$lat]}}
-EOS;
-    $count++;
-  }
-  $sth = null;
-  echo PHP_EOL, ']}', PHP_EOL;
-} elseif ($mode === 'rgc') {
-#
-# JSON出力（逆ジオコーディング）
-#
-  $lon = filter_input($type, 'lon');
-  $lat = filter_input($type, 'lat');
-  $sql = <<<'EOS'
-SET @pt=ST_GeomFromText(CONCAT('POINT(',?,' ',?,')'),4326)
-EOS;
-  $sth = $dbh->prepare($sql);
-# MariaDB は 'POINT(lon lat)'、MySQL は 'POINT(lat lon)'
-  $sth->bindValue(1, $lat, PDO::PARAM_STR);
-  $sth->bindValue(2, $lon, PDO::PARAM_STR);
-  $sth->execute();
-  $sth = null;
-  $sql = <<<'EOS'
-SELECT code,name FROM gyosei LEFT JOIN city USING (code) WHERE ST_Contains(area,@pt) LIMIT 1
-EOS;
-  $sth = $dbh->prepare($sql);
-  $sth->execute();
-  $code = 0;
-  $name = 'unknown';
-  while ($row = $sth->fetch(PDO::FETCH_OBJ)) {
-    $code = $row->code;
-    $name = $row->name;
-  }
-  $sth = null;
-  $output = array( 'code' => $code, 'name' => $name );
-  header('Content-type: application/json');
-  echo json_encode($output, JSON_UNESCAPED_UNICODE), PHP_EOL;
-} elseif ($mode != 'end') {
-#
-# JSON出力
-#
-  $c = filter_input($type, 'c');
-  $geo = array();
-  $rec = array();
-  if ($mode === 'rec' && $c > 0) {
-    $sql = <<<'EOS'
-SELECT id,m.kana,m.name,alt,lat,lon,auth,note
-FROM geo
-JOIN (
- SELECT *
- FROM meizan
- WHERE cat=?
-) AS m USING(id)
-WHERE id=?
-EOS;
-    $sth = $dbh->prepare($sql);
-    $sth->bindValue(1, $c, PDO::PARAM_INT);
-    $sth->bindValue(2, $val, PDO::PARAM_INT);
-  } elseif ($mode === 'id' || $mode === 'rec' || preg_match('/^[0-9]+$/', $val)) {
-    if ($val > 0) {
-      $sql = <<<'EOS'
-SELECT id,kana,name,alt,lat,lon,auth,note
-FROM geo
-WHERE id=?
-EOS;
-      $sth = $dbh->prepare($sql);
-      $sth->bindValue(1, $val, PDO::PARAM_INT);
-    } else {
-      $sql = <<<'EOS'
-SELECT id,kana,name,alt,lat,lon,auth,note
-FROM geo
-WHERE act>0 ORDER BY id DESC LIMIT 20
-EOS;
-      $sth = $dbh->prepare($sql);
-    }
-  } else {
-    if (substr($val, 0, 1) === '%' || substr($val, -1, 1) === '%') {
-      $eq = ' LIKE ';
-    } else {
-      $eq = '=';
-    }
-    $sql = <<<EOS
-SELECT id,geo.kana,geo.name,alt,lat,lon,auth,note
-FROM geo
-JOIN (
- SELECT DISTINCT id
- FROM sanmei
- WHERE name$eq?
-) AS s USING(id)
-WHERE act>0
-ORDER BY alt DESC LIMIT 400
-EOS;
-    $sth = $dbh->prepare($sql);
-    $sth->bindValue(1, $val, PDO::PARAM_STR);
-  }
-  $sth->execute();
-  while ($row = $sth->fetch(PDO::FETCH_OBJ)) {
-    $id = $row->id;
-    $name = $row->name;
-    $kana = $row->kana;
-    if (!($mode === 'rec' && $c > 0) && isset($g_name[$id])) {
-      $name = $g_name[$id] . '・' . $name;
-      $kana = $g_kana[$id] . '・' . $kana;
-    }
-    $geo[] = array(
-      'id' => $id,
-      'kana' => $kana,
-      'name' => $name,
-      'alt' => $row->alt,
-      'lat' => $row->lat,
-      'lon' => $row->lon,
-      'auth' => $row->auth,
-      'note' => $row->note
-    );
-  }
-  $sth = null;
-  if ($mode === 'id') {
-    $alias = array();
-    $sql = <<<'EOS'
-SELECT kana,name
-FROM sanmei
-WHERE id=? AND type>1
-EOS;
-    $sth = $dbh->prepare($sql);
-    $sth->bindValue(1, $val, PDO::PARAM_INT);
-    $sth->execute();
-    while ($row = $sth->fetch(PDO::FETCH_OBJ)) {
-      $alias[] = array('kana' => $row->kana, 'name' => $row->name);
-    }
-    $sth = null;
-    $geo[0]['alias'] = $alias;
-  } elseif ($mode === 'rec') {
-    $sql = <<<'EOS'
-SELECT summit,link,record.start,end,title,summary,image
-FROM record
-JOIN (
- SELECT *
- FROM explored
- WHERE id=?
-) AS e USING (rec)
-WHERE link IS NOT NULL
-EOS;
-    $sth = $dbh->prepare($sql);
-    $sth->bindValue(1, $val, PDO::PARAM_INT);
-    $sth->execute();
-    while ($row = $sth->fetch(PDO::FETCH_OBJ)) {
-      $rec[] = array(
-        'summit' => $row->summit,
-        'link' => $row->link,
-        'start' => $row->start,
-        'end' => $row->end,
-        'title' => $row->title,
-        'summary' => $row->summary,
-        'image' => $row->image
-      );
-    }
-    $sth = null;
-  }
-  $output = array('geo' => $geo, 'rec' => $rec);
-  header('Content-type: application/json');
-  echo json_encode($output, JSON_UNESCAPED_UNICODE), PHP_EOL;
-}
-$dbh = null;
-</code>