This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH `prefecture` AS ( | |
SELECT | |
* | |
FROM | |
UNNEST([ | |
STRUCT(1 AS id,'北海道' AS name,'ホッカイドウ' AS name_kana), | |
STRUCT(2,'青森県','アオモリケン'), | |
STRUCT(3,'岩手県','イワテケン'), | |
STRUCT(4,'宮城県','ミヤギケン'), | |
STRUCT(5,'秋田県','アキタケン'), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH `prefecture`(id, name, name_kana) AS ( | |
VALUES | |
(1,'北海道','ホッカイドウ'), | |
(2,'青森県','アオモリケン'), | |
(3,'岩手県','イワテケン'), | |
(4,'宮城県','ミヤギケン'), | |
(5,'秋田県','アキタケン'), | |
(6,'山形県','ヤマガタケン'), | |
(7,'福島県','フクシマケン'), | |
(8,'茨城県','イバラキケン'), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE `region` ( | |
`id` tinyint(3) unsigned NOT NULL, | |
`name` varchar(255) DEFAULT NULL, | |
`name_kana` varchar(255) DEFAULT NULL, | |
PRIMARY KEY (`id`) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE `prefecture` ( | |
`id` tinyint(3) unsigned NOT NULL, | |
`name` varchar(255) DEFAULT NULL, | |
`name_kana` varchar(255) DEFAULT NULL, | |
PRIMARY KEY (`id`) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 2地点間の距離(m)を求める | |
* 測地線航海算法バージョン | |
* | |
* @param float $lat1 緯度(始点) | |
* @param float $lon1 経度(始点) | |
* @param float $lat2 緯度(終点) | |
* @param float $lon2 経度(終点) | |
* @return float 距離(m) | |
*/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 2地点間の距離を求める | |
* GoogleMapAPIのgeometory.computeDistanceBetweenのロジック | |
* 浮動小数点の精度が足りないためGoogleより桁数が少ないかもしれません | |
* | |
* @param float $lat1 緯度(始点) | |
* @param float $lon1 経度(始点) | |
* @param float $lat2 緯度(終点) | |
* @param float $lon2 経度(終点) | |
* @return float 距離(m) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 2地点間の距離(m)を求める | |
* ヒュベニの公式から求めるバージョン | |
* | |
* @param float $lat1 緯度(始点) | |
* @param float $lon1 経度(始点) | |
* @param float $lat2 緯度(終点) | |
* @param float $lon2 経度(終点) | |
* @param boolean $mode 測地系 true:世界(default) false:日本 | |
* @return float 距離(m) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CASE | |
-- 日本の座標の場合のみメッシュ変換をかける | |
WHEN 20.3 <= TRY_CAST(latitude AS DOUBLE) AND TRY_CAST(latitude AS DOUBLE) <= 45.6 AND 122 <= TRY_CAST(longitude AS DOUBLE) AND TRY_CAST(longitude AS DOUBLE) <= 154 THEN | |
CONCAT( | |
-- 1次メッシュ | |
TRY_CAST(TRY_CAST(FLOOR(TRY_CAST(latitude AS DOUBLE)*1.5) AS BIGINT) AS VARCHAR), | |
TRY_CAST(TRY_CAST(FLOOR(TRY_CAST(longitude AS DOUBLE))-100 AS BIGINT) AS VARCHAR), | |
-- 2次メッシュ | |
TRY_CAST(TRY_CAST(FLOOR(MOD(TRY_CAST(latitude AS DOUBLE)*60,40)/5) AS BIGINT) AS VARCHAR), | |
TRY_CAST(TRY_CAST(FLOOR(MOD(TRY_CAST(longitude AS DOUBLE)-100,1)*60/7.5) AS BIGINT) AS VARCHAR), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
########## | |
# TDのテーブルを作成日時を見て削除していくバッチ | |
# 要jq:https://stedolan.github.io/jq/ | |
########## | |
##### 設定する場所 | |
# 残しておく期間 | |
EXPIRE_DAY=30 | |
# TD API KEY |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 小数点指定できる四捨五入 | |
* http://qiita.com/chiyoyo/items/eac6d7cb4d3d6a6ab3fb | |
*/ | |
Math.round = function (round_original) { | |
return function (number, pricision) { | |
var _pow; | |
switch (arguments.length) { | |
case 1: | |
return round_original(number); |
NewerOlder