あにねこ電脳覚書

ユーザ用ツール

サイト用ツール

トレース:
sanmei:database

文書の過去の版を表示しています。

目次

山名データベース

データベースの作成

Database:nurude_tozan

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

Table:geo

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

Table:sanmei

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

Table:meizan

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

Table:record

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

Table:explored

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

Table:poi

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

Table:gyosei

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__

Table:city

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__

データベースの検索

<?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;
sanmei/database.1592497744.txt.gz · 最終更新: 2020/06/19 01:29 by anineco