Postgresql – Tarumi Blog2

pg_dumpのパスワード省略

2017年4月5日

pg_dumpのパスワードの省略方法（Windows編）

%APPDATA%\postgresql\pgpass.confを作成して接続情報を記載。
[code]
*:*:*:ユーザー名:パスワード
[/code]

%APPDATA%は環境変数
テスト環境では
[code]
C:\Users\Administrator\AppData\Roaming
[/code]

これで引数に-wを与えればパスワードの省略が可能
[code]
pg_dump -U ユーザー名 -t areas -w -h localhost smapdb > areass
[/code]

空間インデックスの作成
create index インデックス名 on テーブル名 using GIST(ジオメトリ項目名);
※テーブル名に大文字があるとテーブル名の前後にダブルコーテーションが付く。
そのような時はcreate index文でもテーブル名の前後にインデックスを付与する。
例）create index インデックス名 on “テーブル名” using GIST(ジオメトリ項目名);

空間インデックスの一覧
空間インデックスの一覧機能はないので、テーブル「pg_indexes」を参照してインデックスの女王を求める。
※psqlのコマンド\diでは表示されない

PostGISをWindowsにインストールのおさらい

2016年3月22日

psql.exeにパスを通す

管理者権限でDBに接続後、下記を実行

接続ユーザー（ロール）作成

スキーマを作成して、スキーマのオーナーを設定

データベースを作成

ユーザーに権限を付与

PostgresqlのデータベースをPostGISデータベースに拡張

Postgresqlのシーケンス値設定

2014年12月10日

カレントの値を設定
NEXTVALは設定した値＋１
[code]
SELECT setval(‘シ－ケンス名’, 値);
[/code]

PostGISに空間データを持ったテーブルを作成

2014年11月28日

「CREATE TABLE」にgeometryを入れて作ってしまうことが多いので念の為に記述！
geopacificさんのサイトを参考にしました

空間データを含まないテーブルを作成
[code]
create table traffic_counter_point
(
id serial,
name varchar(128),
cmt varchar(256)
)
[/code]

次にAddGeometryColumnを使って空間データの項目を追加。
これによってメタデータも更新される。
これらのメタデータは必須ではないが、GeoServerやQGISでは照会される。

AddGeometryColumnの構文

AddGeometryColumn(スキーマ名,テーブル名,項目名,SRID,geometry_type,dimension)

※スキーマは省略可能

traffic_counter_pointテーブルにPOINTを持たせる
[code]
select AddGeometryColumn(‘traffic_counter_point’,’geometry’,4326,’POINT’,2)
[/code]

C#（Npgsql）でPostgresqlに大量データを追加

2014年2月8日

PostgresqlにNpgsqlを使用してデータを追加するには一般的にはNpgsqlCommandでINSERTを実行。
しかし大量のデータを追加するにはこれでは時間がかかりすぎる。
その為Postgresqlのコピー機能のラッパーであるNpgsqlCopyInの使い方を記載。
Postgresqlのコピー機能についてはこちら
追加対象のテーブル
[code]
CREATE TABLE tbl_test
(
rec_no decimal(6,0) NOT NULL,
rec_name varchar(60) NOT NULL,
add_dt timestamp without time zone NOT NULL,
CONSTRAINT pkey_tbl_test PRIMARY KEY
(
rec_no
)
)
WITH (
OIDS=FALSE
);
[/code]
追加用データCSVファイル
文字コードはUTF8
改行はCRLF
デリミタはタブ
[code]
1 NAME1 2013-09-26 00:00:00
2 NAME2 2013-09-26 00:00:00
3 NAME3 2013-09-26 00:00:00
[/code]

以下はコード
[code]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using Npgsql;
namespace pcopy
{
class Program
{
static void Main(string[] args)
{
NpgsqlConnection conn = new NpgsqlConnection("Server=ホスト名;User id=ID;password=パスワード;Database=DB名;SyncNotification=true;");
conn.Open();
FileStream fs = File.OpenRead(@"CSVファイルのフルパス");
NpgsqlCommand command = new NpgsqlCommand("COPY tbl_test FROM STDIN", conn);
NpgsqlCopyIn cin = new NpgsqlCopyIn(command, conn, fs);
cin.Start();
cin.End();
}
}
}
[/code]

pgadminで指導メッセージ

2014年2月4日

pgadminIIIを使用してリモートDBに接続すると以下の画面「サーバ構成はインストールされていません」が表示された

画面上の修正ボタンを押すと今後は以下の画面が表示された

ERROR：permission denied to create extension “adminpack”
HINT: Must be superuser ocreate this extention

adminpackの詳細

adminpackは、pgAdminやその他の管理・運用ツールがサーバログファイルの遠隔管理を行うなどの、
追加的な機能を提供できるようにするための数多くのサポート機能を提供します。

adminpackにより実装された機能はスーパーユーザのみが実行可能です。
以下がそれらの機能一覧です。

int8 pg_catalog.pg_file_write(fname text, data text, append bool)
bool pg_catalog.pg_file_rename(oldname text, newname text, archivename text)
bool pg_catalog.pg_file_rename(oldname text, newname text)
bool pg_catalog.pg_file_unlink(fname text)
setof record pg_catalog.pg_logdir_ls()

int8 pg_catalog.pg_file_read(fname text, data text, append bool)
bigint pg_catalog.pg_file_length(text)
int4 pg_catalog.pg_logfile_rotate()

対応方法

対象のDBに管理者で接続して以下のコマンドを実行
create extension adminpack;

以上で対応完了。
pgadminでも指導のメッセージが表示されなくなり、pgadminで確認するとadminpackの関数が実装されている

PostgreSQL（9.3 Windows）に他ホストから接続

2014年2月3日

PostgreSQLはデフォルトではローカルホストからのみ接続が可能
他ホストからの接続を許可するには以下の設定を行う

postgresql.confの編集
C:\Program Files\PostgreSQL\9.3\data\postgresql.confを編集して他のホストからの接続を許可
listen_addresses = '*'
listen_addressesがコメントにされていたり、listen_addressesにlocalhostが設定されていると他ホストからの接続ができない
実際にはpostgresql.confを直接編集せずにpgadminを使用して編集
「ツールメニュー」－「サーバ構成」－「postgresql.conf」を選択してpostgresql.confの編集画面を表示

pg_hba.confの編集
C:\Program Files\PostgreSQL\9.3\data\pg_hba.confを編集して他のホストからの接続を許可
以下の１行を追加
# TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5
METHODの設定値について

trust	パスワード認証を行わない
md5	パスワードをmd5で簡易暗号化して転送して認証
password	パスワードをテキストで転送して認証

pg_hba.confも通常は直接編集せずにpgadminを使用して編集
「ツールメニュー」－「サーバ構成」－「pg_hba.conf」を選択してバックエンド　アクセス認証エディタを表示

GeoServer上のラインをOpenLayersに色分け表示

2014年1月15日

GeoServer上に鉄道のデータをインポート

これを鉄道会社の種別（１～５）ごとに色分けしてOpenLayers上に表示

当初はOpenLayersのスタイルで対応を行おうとしたが、どうもこれはOpenLayers.Layer.WMSには使えないみたい

結局、GeoServerの機能でスタイルを作成してレイヤにスタイルを設定して解決

以下のスタイルを作成
[code]
<?xml version="1.0" encoding="ISO-8859-1"?>
<StyledLayerDescriptor version="1.0.0"
xsi:schemaLocation="http://www.opengis.net/sld StyledLayerDescriptor.xsd"
xmlns="http://www.opengis.net/sld"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:xlink="http://www.w3.org/1999/xlink"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<NamedLayer>
<Name>railroad ex</Name>
<UserStyle>
<Title>SLD Cook Book: railroad ex</Title>
<FeatureTypeStyle>
<Rule>
<Name>rail-1</Name>
<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>n02_002</ogc:PropertyName>
<ogc:Literal>1</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
<LineSymbolizer>
<Stroke>
<CssParameter name="stroke">#009933</CssParameter>
<CssParameter name="stroke-width">2</CssParameter>
</Stroke>
</LineSymbolizer>
</Rule>
</FeatureTypeStyle>
<FeatureTypeStyle>
<Rule>
<Name>rail-2</Name>
<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>n02_002</ogc:PropertyName>
<ogc:Literal>2</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
<LineSymbolizer>
<Stroke>
<CssParameter name="stroke">#0055CC</CssParameter>
<CssParameter name="stroke-width">3</CssParameter>
</Stroke>
</LineSymbolizer>
</Rule>
</FeatureTypeStyle>
<FeatureTypeStyle>
<Rule>
<Name>rail-3</Name>
<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>n02_002</ogc:PropertyName>
<ogc:Literal>3</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
<LineSymbolizer>
<Stroke>
<CssParameter name="stroke">#FF0000</CssParameter>
<CssParameter name="stroke-width">6</CssParameter>
</Stroke>
</LineSymbolizer>
</Rule>
</FeatureTypeStyle>
<FeatureTypeStyle>
<Rule>
<Name>rail-4</Name>
<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>n02_002</ogc:PropertyName>
<ogc:Literal>4</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
<LineSymbolizer>
<Stroke>
<CssParameter name="stroke">#FF0000</CssParameter>
<CssParameter name="stroke-width">6</CssParameter>
</Stroke>
</LineSymbolizer>
</Rule>
</FeatureTypeStyle>
<FeatureTypeStyle>
<Rule>
<Name>rail-5</Name>
<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>n02_002</ogc:PropertyName>
<ogc:Literal>5</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter>
<LineSymbolizer>
<Stroke>
<CssParameter name="stroke">#FF0000</CssParameter>
<CssParameter name="stroke-width">6</CssParameter>
</Stroke>
</LineSymbolizer>
</Rule>
</FeatureTypeStyle>
</UserStyle>
</NamedLayer>
</StyledLayerDescriptor>
[/code]
上記のスタイルをレイヤに関連付けて対応

念の為、OpenLayersのスタイルについて

参考サイト

[code]
new OpenLayers.StyleMap({
"default": new OpenLayers.Style({
strokeColor: "white",
strokeWidth: 1
}, {
rules: [
new OpenLayers.Rule({
filter: new OpenLayers.Filter.Comparison({
type: OpenLayers.Filter.Comparison.LESS_THAN,
property: "shape_area",
value: 3000
}),
symbolizer: {
fillColor: "olive"
}
}),
new OpenLayers.Rule({
elseFilter: true,
symbolizer: {
fillColor: "navy"
}
})
]
})
})
[/code]

スタイルの変更
スタイルはGeoServer上で予めレイヤに設定する。
以下の書式でJavaScript側からも変更が可能
※GeoServer上でrailroad2というスタイルを作成済み
[code]
railroad = new OpenLayers.Layer.WMS(
"Tokyo:railroad", "../geoserver/Tokyo/wms",
{
STYLES: ‘railroad2’,
LAYERS: ‘Tokyo:VIEW_RAILROAD’,
format: ‘image/png’,
transparent: true,
viewparams: ‘road_type:2’
},
{
singleTile: true,
ratio: 1,
isBaseLayer: false,
yx : {‘EPSG:4326’ : true}
}
);
[/code]