diadia

興味があることをやってみる。自分のメモを残しておきます。

特定の月だけが入ったレコードだけを取り出すことはできるか?

例えば6月をtimestampやdateのフィールドからselectで抽出できるか?

今まで列の値をwhereを使って=かlikeを使って取得してきた。dateやtimestampでwhere句を使う方法を知らないので、まずそれを調べてみようと思う。

参考:https://stackoverflow.com/questions/8863156/selecting-by-month-in-postgresql

上記の例ではちょうどpostgresqlを使ってtimestampまたはdate型の月を指定してselectする方法について議論している。
要するにEXTRACTを使えば解決できるようだ。 WHERE句にEXTRACTを用いる。

SELECT id, name, birthday FROM employee.person 
WHERE EXTRACT(MONTH FROM birthday) > 10;

https://www.postgresql.org/docs/9.1/functions-datetime.html

select userid, date, zahyo from table_a
WHERE EXTRACT(MONTH from table_a) = 6
conn = get_connection()
cur  = conn.cursor()
#8月のデータを取得したい
SELECT_SQL = """SELECT EXTRACT(month FROM date) FROM demo"""
SELECT_SQL = """SELECT * FROM demo WHERE EXTRACT(month FROM date) = 8"""
cur.execute(SELECT_SQL)
res = cur.fetchall()
print(res)

QGISについて分かったことメモ

タイルレイヤプラグインについて

国土地理院の地図を表示するために以下の記事を参考にした。
http://ymcity.hatenablog.com/entry/2016/10/05/234431

このサイトでは国土地理院の地図を表示させるために、上部メニューバーのプラグインからTileLayerプラグインを選んで導入するとあった。しかしながらこのTileLayerプラグインが自分にはない。どうしてないのだろうと困っていた。ブログの内容はQGIS2系を扱う記事であり、自分はQGIS3系であった。ここが意外にも重要だった。
https://note.sngklab.jp/?p=627

今回はQGISでよく使う機能の一つであろう、地理院地図タイルの表示について紹介したいと思います。背景地図としてとても役立つ地理院地図タイルですが、QGIS version 2.X系ではTileLayerPluginなどを用いて呼び出していましたが、QGIS version 3.X系ではデフォルトでタイルレイヤを読み込む機能が備わっています。

postgis環境構築について

windowsで環境構築に詰まって、macでも詰まってしまった。どうすればよいのやら... 結局詰まっている場所は同じ場所だ。 CREATE EXTENSION postgis; を入力すると、windows,mac両者ともエラーが出てしまう。 windowsの場合はまずpostgresqlインストーラpostgresqlをインストールする。終わりがけにスタッグビルダが起動するので、そこでpostgisをインストールする流れを踏む。そして新たにデータベースを作成する。これは、SQLとしてCREATE DATABASE hoge;としても良いし、createdb -U postgres hogeでも良い。そのあと作成したデータベースに接続してCREATE EXTENSION postgis;と入力すればよいはずである。しかし、それに関わるファイルがないとエラーが出る。実際にそのファイルが有るにも関わらず。macもCREATE EXTENSION postgis;で詰まってしまっている。

ERROR:  could not load library "/Library/PostgreSQL/11/lib/postgresql/rtpostgis-2.5.so": dlopen(/Library/PostgreSQL/11/lib/postgresql/rtpostgis-2.5.so, 10): Library not loaded: @loader_path/../../lib/libssl.1.0.0.dylib
  Referenced from: /Library/PostgreSQL/11/lib/postgresql/libgdal.20.dylib
  Reason: image not found

上記のエラーが出てしまっている。魔境。。。

 

postgis2.5の利用要件

https://postgis.net/docs/manual-2.5/postgis_installation.html#install_requirements

postgreSQLを9.4以上を使っていること。postgispostgresqlの対応状況はこちらを確認すること。 https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

createlangコマンドについて

createlangはcreatedbコマンドの後に実行するとドキュメントに書いてある。

createdb yourdatabase
createlang plpgsql yourdatabase
psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f postgis_comments.sql
psql -d yourdatabase -f spatial_ref_sys.sql
psql -d yourdatabase -f topology.sql
psql -d yourdatabase -f topology_comments.sql

-- only if you compiled with raster (GDAL)
psql -d yourdatabase -f rtpostgis.sql
psql -d yourdatabase -f raster_comments.sql

--if you built with sfcgal support --
psql -d yourdatabase -f sfcgal.sql
psql -d yourdatabase -f sfcgal_comments.sql

しかしwindowsにインストールしたpostgresqlにはなかった。これについてはどうドキュメントに以下のように書いてある。

https://postgis.net/docs/manual-2.5/postgis_installation.html#create_new_db

Many of the PostGIS functions are written in the PL/pgSQL procedural language. As such, the next step to create a PostGIS database is to enable the PL/pgSQL language in your new database. This is accomplish by the command below command. For PostgreSQL 8.4+, this is generally already installed

要するに8.4以上のバージョンにはすでにインストールされているらしい。11を使っているのでcreatelangは使わないってことでオッケイなんだよね?

Postgresqlのコマンドライン:creatdbについてスッキリしたこと

データベースを作成するときのコマンドcreatdbについて

CREATE DATABASE MYDB;

上記のようにコマンドを叩くとデータベースを作ることができるのは当たり前として、createdb hogeと書いてもデータベースを作ることができる。このコマンドは天から急に降ってきたかのごとく突拍子もなく使っている人がいる。例えばこれ。
https://lets.postgresql.jp/documents/tutorial/PostGIS/3
でもSQLの学習書を読んでいてもcreatedbのコマンドをついに見つけることができずにいた。
しかしこの急に出てくるコマンドに終止符を打つことができた。

localでwindowsに入れてみて分かった

今までpostgresqlはさくらVPSで使うのみだったので、ディレクトリの構造やどんなファイルが入っているか詳しく知ることはなかった。見ても良くわからなかったし。。。ただ今回はGISを試すためにwindowsに入れてみてた。psqlコマンドがコマンドプロンプトやターミナルで使えるのはbinディレクトリにpsqlの実行ファイルが存在するから。実はcreatedbコマンドもbinディレクトリにcreatedb実行ファイルが存在していた。てことで、createdbコマンドはpsqlでデータベースに接続した後に使うコマンドでもないことは自明となったし、この理由は知りたかったひとつの項目であった。

 

f:id:torajirousan:20190811191515p:plain

windowsのコマンドプロンプトからPostgreSQLを触る

今回の試み

今回はwindowspostgresqlを入れて、そしてコマンドプロンプトからログインできるかを試す。今までwebアプリケーションを作成するにはpostgresqlを使っていたもののサクラVPSで行っていた。だからLinuxwindowsかの違いや、リモートやローカルといった作業場所の違いがある。

参考 :
https://qiita.com/wb773/items/c2fd0e1e0349a41b5844
https://lets.postgresql.jp/documents/tutorial/windows/

実行環境

  • windows10
  • postgresql11

インストール

https://lets.postgresql.jp/documents/tutorial/windows/ 

ログイン

コマンドプロンプトpsqlコマンドを実行してpostgresでログインする。

psql -U postgres -d postgres

上記ではパスが通っていないので、C:\Program Files\PostgreSQL\11\binで実行する。psql.exeはbinディレクトリにあるから。

ユーザを作成してデータベースの権限を帰属させる

参考:https://qiita.com/wb773/items/248e6e083b2fe12e820a
http://torajirousan.hatenadiary.jp/entry/2018/09/10/054545

windowsでもpsqlコマンド実行後の流れは特にwindowsであろうとlinuxであろうと違いがないって感想だった。手順はpostgresでログイン後、新たにデータベースを作成する。そしてデータベース操作権限のあるユーザーを作成する。

C:\Program Files\PostgreSQL\11\bin>psql -U postgres -d postgres

postgres=# CREATE DATABASE test_database;
CREATE DATABASE

postgres=# CREATE USER new_user WITH PASSWORD 'PASSW0RD';
CREATE ROLE
postgres=# ALTER ROLE new_user SET client_encoding TO 'utf8';
ALTER ROLE
postgres=# ALTER ROLE new_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE
postgres=# ALTER ROLE new_user SET timezone TO 'Asia/Tokyo';
ALTER ROLE

postgres=# GRANT ALL PRIVILEGES ON DATABASE test_database TO new_user;
GRANT

postgres=# \q

binにあるコマンドについて分かったこと

psql.exeはデータベースの接続やsqlファイルの読込みやcreate table等のSQLの実行を行うことができる。

psql -U username -d dbname
#データベースの接続
psql -U username -d dbname -f "file\path\**.sql"
#sqlファイルを読みこんで、ファイル内のsqlを実行する
psql -U username -d dbname -c "CREATE TABLE test_table (userid varchar(20), address varchar(20));"
#SQLの実行

pg_ctl.exeはpostgresqlのサービスの起動、停止などを実行する。これはservices.mscでもサービスの起動、停止をすることができるようだ。
https://www.postgresql.jp/document/9.3/html/app-pg-ctl.html

データベースのデータを複製する場合はpg_dump.exeを用いる。

時間関係のオブジェクトを扱う

時間関係のモジュール

timeとdatetimeが存在する。また時間についてはエポックタイムという概念も存在する。

time

time.time()でエポックタイムを取得できる。
http://sweng.web.fc2.com/ja/program/python/time.html

datetime

今の時間をとる。それはdatetime.datetime.now()でできる。
datetime.datetime.now()をUNIX時間に変換するにはdatetimeオブジェクトのtimestampメソッドを使えば良い。

https://note.nkmk.me/python-unix-time-datetime/

 

import datetime
now = datetime.datetime.now()
now
# datetime.datetime(2019, 8, 10, 12, 8, 12, 636212)

now.timestamp()
# 1565406492.636212

これで636212を削除するにはどうすればよいのか?そこが疑問。

今のUNIX時間をとるにはどうすればいいのか?それはまずdatetimeの今の時間を取得する。その時間をtimestamp()メソッドを使うとエポックタイム(UNIX時間)に変換することができる。

 

エポックタイム

まだ詳しくはわからないが、エポックタイムはエポックタイムオブジェクトってのはないのか?time.time()で返されるのはミリ秒を含むfloat型データだし、int(time.time())はミリ秒を除くint型データである。これらはエポックタイムを表すので、datetime.datetime.fromstamp(floatかint型のエポックタイム)で成立する。

次気になるのはdatetime型データからどうstrデータに変換するか。datetimeオブジェクト.strftime(フォーマット)を使えば良い。

軽くまとめるとエポックタイムからデイトタイム型に変換してそれを文字列化するには以下の手順を踏む。

import time , datetime
now_u = time.time() #floatで取得 
now_u = int(now_u) #ミリ秒を削除してint型にした

now = datetime.datetime.fromtimestamp(now_u)
# datetime型に変換した

format = "%Y-%m-%d %H:%M:%S"
t = datetime.datetime.strftime(now, format)
#これで文字列に変換できた

t1 = datetime.datetime.strptime(t, format)
#datetimeオブジェクトに戻す


t1.timestamp()
#エポックに戻す
#再利用コード mytools/treat.py
import datetime, time

def change2datetime(timestamp):
    """
    UTC時間からdatetimeオブジェクトに変換する関数	
    time(timestamp) → datetime
    """    
    d = datetime.datetime.fromtimestamp(timestamp)
    return d


def change2d_str(d):
    """
    datetimeオブジェクトから文字列"2019-8-11 17:15:13"のような形に変換する関数
    datetime → str
    """
    dstr_format = "%Y-%m-%d %H:%M:%S"
    d_str = datetime.datetime.strftime(d, dstr_format)
    return d_str


def timestamp2dstr(timestamp):
    """
    UTC時間から時間を表す文字列に変換する関数
    timestamp → str
    """
    d = change_datetime(timestamp)
    d_str = change_d_str(d)
    return d_str


    
def get_rowlst():
    row_list = []
    with open("demo.csv", "r", encoding="utf-8") as f:
        reader = csv.reader(f)
        for row in reader:
            row_list.append(row)
    return row_list
    
def get_staytime(lst, index):
    if index == len(lst)-1:
        return print("finish")
    timestamp      = get_timestamp(lst[index])
    next_timestamp = get_timestamp(lst[index+1]) 
    staytime = int(next_timestamp) - int(timestamp)
    return staytime



def extract_data(stay_time=None,seconds=10):
    if stay_time != None and stay_time > seconds:
        return "EXTRACT"
    else:
        return "IGNORE"
        
def output_pdata(seconds=10):
	row_list = get_rowlst()
	for index,row in enumerate(row_list):
	    userid    = get_userid(row)
	    timestamp = get_timestamp(row)
	    date      = change_datetime(timestamp)
	    stay_time = get_staytime(row_list, index)
	    file_name = get_file_name(row)

	    
	    result = extract_data(stay_time=stay_time, seconds=950)
	    if result == "EXTRACT":
	        print(userid,timestamp,date,stay_time)
	        t = (userid, date, stay_time, file_name,)

	        cur.execute("""INSERT INTO registered (userid, date, stay_time, filename) VALUES (%s,%s,%s,%s)""", t)
	        conn.commit()

    conn.close()

SQLite3で複合キーを使ってみる

参考:https://ts0818.hatenablog.com/entry/2017/02/04/162513
http://isakado.blog106.fc2.com/blog-entry-17.html

使用例

# 複合主キーを設定したテーブルを作成    
conn = sqlite3.connect("practice.sqlite")
cur  = conn.cursor()

create_table_sql ="""
CREATE TABLE Registered (USERID TEXT, DATE TEXT, FILE_NAME TEXT, PRIMARY KEY(USERID, DATE))
"""

cur.execute(create_table_sql)

どうやらクリエイトテーブルしたあとにPRIMARY KEYを記述してその引数に複合キーとしたいカラム名を渡せば良いようだ。

この記法はsqliteだけでなくpostgresqlでも同じ。
http://www.ksknet.net/postgresql/post_179.html
https://www.dbonline.jp/postgresql/table/index6.html

kml:linestringファイル作成

ele1 = ["road1", 0, 120, 60]
ele2 = ["road1", 1, 122, 65]
ele3 = ["road1", 2, 125, 63]
ele4 = ["road2", 0, 123, 61]
ele5 = ["road2", 1, 113, 41]
ele6 = ["road3", 0, 173, 69]
ele7 = ["road3", 1, 113, 23]
ele8 = ["road4", 0, 123, 67]
ele8 = ["road4", 1, 143, 97]
ele9 = ["road5", 0, 133, 61]
ele10= ["road5", 1, 173, 91]

parent_list = [ele1, ele2, ele3, ele4, ele5, ele6, ele7 ,ele8, ele9, ele10]


key_list = []
for ele in parent_list:
    if ele[0] not in key_list:
        key_list.append(ele[0])


all_data = []
for ele in key_list:
    ele_dict = {}
    ele_dict.setdefault(ele,[])
    all_data.append(ele_dict)





for e in parent_list:
    road_name = e[0]
    for dic in all_data:
        key = [key for key in dic.keys()][0]
        if key == road_name:
            
            dic[key] = dic[key] + e[1:]



kml = ""

kml_head = """<?xml version="1.0" encoding="UTF-8"?> 
<kml xmlns="http://earth.google.com/kml/2.0"> <Document>"""
kml += kml_head

string = """
<Placemark>
<LineString>
<name>{road_name}</name>
<!--altitudeMode>clampToGround</altitudeMode-->
<altitudeMode>absolute</altitudeMode>
<!--altitudeMode>clampToGround</altitudeMode>
<altitudeMode>clampToGround</altitudeMode-->
<coordinates>
{contents}
</coordinates>
</LineString>
</Placemark>"""

kodo = "0.0"
for road in all_data:
    road_name = [key for key in road.keys()][0]
    info_base = road[road_name]
    set_quantity = int(len(info_base)/3)

    contents = ""
    for n in range(0,set_quantity):
        keido = str(info_base[3*n+1])
        ido   = str(info_base[3*n+2])
        #print(keido, ido)
        info = ",".join([keido, ido, kodo])+"\n"
        
        contents = contents + info
    

    placemark = string.format(road_name=road_name,contents=contents[:-2])
    kml += placemark


kml += "\n"+"</Document></kml>" 
print(kml) 


with open("output.kml", "a", encoding="utf-8") as f:
    f.write(kml)

kmlファイルテンプレート

全般

xmlではコメントアウトは<!-- -->でできるっぽい

標高については以下に参照先がある
https://developers.google.com/kml/documentation/altitudemode?hl=ja

Point

<?xml version="1.0" encoding="UTF-8"?> 
<kml xmlns="http://earth.google.com/kml/2.2"> <Document>
<Placemark> 
 <Point>
  <coordinates>
   経度,緯度,高度 
  </coordinates>
 </Point>
</Placemark>
</Document> </kml>

 

LineString

<?xml version="1.0" encoding="UTF-8"?> 
<kml xmlns="http://earth.google.com/kml/2.2"> <Document> <Placemark>
<LineString>
<name>ポリライン名</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> </Document></kml>

複数のポリラインを表現したい場合にはPlacemarkを繰り返して挿入すると複数種のポリラインを表現することができる。

<?xml version="1.0" encoding="UTF-8"?> 
<kml xmlns="http://earth.google.com/kml/2.2"> <Document> <Placemark>
<LineString>
<name>ポリライン1</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> <Placemark>
<LineString>
<name>ポリライン2</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> <Placemark>
<LineString>
<name>ポリライン3</name>
<coordinates>
経度,緯度,高度 経度,緯度,高度 経度,緯度,高度
</coordinates>
</LineString>
</Placemark> </Document></kml>

メモ:linestringに関しては厳密にLineStringと表記しないとgoogleearthが読み込まない。例えばLinestringだと駄目。

dictの値にリストを使うときの注意点

dict[key] = dict[key].append("要素")の結果はNone

dict型データの値にリストを設ける。そして必要な情報をリストに格納しようと思い以下のコードを書いた。

sample = dict(column1=[])

key = [key for key in sample.keys()][0]
print(key)
#1 column1

value = [value for value in sample.values()][0]
print(value)
#2 []
print(type(value))
#3 <class 'list'>

'''疑問:valueの[]に要素を加えることができるか?検証する'''

print("入れる前 : ",sample[key])
#4 入れる前 :  []
sample[key] = value.append("要素1") 

print("入れた後 : ",sample[key])
#5 入れた後 :  None
print(sample)
#6 {'column1': None}

ここで注目すべきは、dictの値としてリストを設け、そのリストにappendを使うと値はNoneになってしまうことだ。期待していたことは{'column1': "要素1"}となることだったのに。。。

期待の結果を得る方法

上記では期待の結果が得られなかったのはappendに問題があるようだ。appendメソッドを実行せずにリストにリストを加えるとdictにリストの要素を加えることができる。

my_list = [1,2,3] 
test_list = [4,5]
my_list + test_list
# [1, 2, 3, 4, 5]
""" リスト同士の+は結果としてappendの形になる。解決にこれを利用する """
test_list = [1]
sample = dict(column1=[])

sample[key] = sample[key] + ["新たな要素"] 

print("入れた後 : ",sample[key])
# 入れた後 :  ['新たな要素']
print(sample)
# {'column1': ['新たな要素']}

appendについて補足情報

https://codeday.me/jp/qa/20190205/212233.html
append()は破壊的な操作であるらしく、非破壊的な操作は + であるらしい。
https://stackoverflow.com/questions/1682567/why-does-list-append-evaluate-to-false-in-a-boolean-context

Most Python methods that mutate a container in-place return None -- an application of the principle of Command-query separation. (Python's always reasonably pragmatic about things, so a few mutators do return a usable value when getting it otherwise would be expensive or a mess -- the pop method is a good example of this pragmatism -- but those are definitely the exception, not the rule, and there's no reason to make append an exception).

コンテナをその場で変更するほとんどのPythonメソッドはNoneを返します。これは、コマンドとクエリの分離の原則を適用したものです。みたいなことが書いてあり、リストの中身を変えるメソッドを使うと、Noneが返されるのが一般的らしい。これはappendに限らず、remove()も同様の結果になる。

my_list = [1,2,3,4,5]
print(my_list.append(6))
# None
r = my_list.append(7)
print(r)
# None

print(my_list.remove(1))
# None
r = my_list.remove(2)
print(r)
# None

SQLエラー:must appear in the GROUP BY clause or be used in an aggregate function

エラーコード

must appear in the GROUP BY clause or be used in an aggregate function

原因

GROUP BYを使って以上のようなエラーが出た場合には、以下の原因によるエラーの可能性がある。
それはGROUP BYを使っているにも関わらず、集約関数(SUMなど)を使っていない場合であるとき。
GROUP BY は集約関数を使う際に用いられる前提を確認したい。

SQL UNIONについて

UNION ALL とUNION

UNION ALLを使うと集合で重複行をすべて返すのに対し、UNIONは重複行を1行にまとめる。

ORDER BYとの関係

UNIONを使った場合、ORDER BYはUNIONを使ったあと最後に実行する。これは集合の内容をORDER BYによって再編集するものであり、単純に各集合を並び替えて結果を集合ごとに並べるものではない。