本記事ではデータベースとしてPostgreSQLを使用しますが、DuckDBもおすすめです。DuckDBは組み込み型のデータベースで、RやPythonから簡単に利用でき、大規模データの処理にも適しています。
使い方についてはこちらの記事を参照してください。
データ分析や統計処理を行う際、RやPythonといったプログラミング言語に加えて、大規模なデータを効率的に管理・処理するためのデータベースが必要になることがあります。特に、大規模なローデータがあり、そこからさまざまなデータフレームを生成・操作するような場合、PostgreSQLのような高機能なデータベースが強力な味方になります。
本記事では、Windows環境でWSL（Windows Subsystem for Linux）を活用し、R、Python、PostgreSQLを中心とした開発環境を構築する方法を解説します。WSLを使うことで、Windowsの使いやすさを保ちながら、Linux環境の恩恵を受けられます。
この記事の対象読者
環境構築の流れ
初めての方はまずWindows版から試してみて、より本格的な環境が必要になったらWSL版に移行するのがおすすめです。
結構長いページになってしまったので、目次から適宜ジャンプして読んでみてください。
また、本ページではSQLコードに関してはRを経由した実行方法を中心に解説します。
Macユーザーの方も、PostgreSQLとR、Pythonの組み合わせは同様に有効ですので、参考にしてみてください。
であることを前提とします。
PostgreSQLは、オープンソースの高機能なリレーショナルデータベース管理システム（RDBMS）です。「ポストグレス」と読みます1。
PostgreSQLの特徴
なぜPostgreSQLを使うのか
RやPythonだけでもデータ分析は可能ですが、以下のような場面でPostgreSQLが役立ちます：
特に、PostGIS拡張を導入すれば、バス停や人口メッシュといった空間データの距離計算やバッファ分析などが高速に実行でき、GIS分析の強力な基盤となります。
WSL（Windows Subsystem for Linux）は、Windows上でLinux環境をネイティブに実行できる機能です。仮想マシンよりも軽量で、WindowsとLinuxのファイルシステムを相互に利用できるのが特徴です。
aptコマンドで必要なソフトウェアを簡単にインストールWindows版が向いている場合
WSL版が向いている場合
本記事では両方の方法を紹介しますので、お好みで選んでください。まずはWindows版で試してみて、必要に応じてWSL版に移行するのもよいと思います。
Windows環境で直接PostgreSQL、Rをセットアップする方法を解説します。GUIツールが使いやすく、初めての方にもおすすめです。
C:\Program Files\PostgreSQL\18）5432のまま[DEFAULT]または Japanese, Japan
スタートメニューから「SQL Shell (psql)」を起動し、以下を入力：
Server [localhost]: # Enterキーを押す
Database [postgres]: # Enterキーを押す
Port [5432]: # Enterキーを押す
Username [postgres]: # Enterキーを押す
Client Encoding [SJIS]: # Enterキーを押す
ユーザー postgres のパスワード: # 設定したパスワードを入力パスワード以外は入力しなくて問題ありません！postgres=#と表示されれば成功です。以下のコマンドでバージョンを確認できます：
SELECT version();
終了する場合は \q と入力してEnterキーを押します。
PostgreSQLをGUIで操作できる「pgAdmin 4」も一緒にインストールされています。スタートメニューから「pgAdmin 4」を起動して、データベースの作成やテーブルの確認ができます。
左側のサイドバーにある「Servers」から「PostgreSQL 18」を選択、「Databases」→「postgres」→「Schemas」→「public」→「Tables」でテーブル一覧を確認できますが、今回はR上で操作することもあり、必ずしもpgAdminを使う必要はありません。一時的にGUIで確認したい場合に利用するとよいと思います。
RからPostgreSQLを操作するには、RPostgresパッケージを使います。また、データベース全般の操作にはDBIパッケージも必要です。
RStudioのコンソールで以下を実行：
install.packages(c("RPostgres", "DBI"))以下のコードで接続を確認します：
library(DBI)
# データベースに接続
con <- dbConnect(
RPostgres::Postgres(),
dbname = "postgres",
host = "localhost",
port = 5432,
user = "postgres",
password = "あなたのパスワード"
)
# 接続確認
dbGetQuery(con, "SELECT version();")
# 切断
dbDisconnect(con)エラーが出ずに結果が表示されれば成功です。
プロジェクトが個人用であれば問題ありませんが、チーム開発や公開リポジトリでコードを共有する場合、パスワードを直接コードに書くのは避けた方がよいです。
Gitを使用している場合、.Renvironファイルをワーキングディレクトリに作成し、以下のように環境変数を設定する方法がおすすめです：
PG_PASSWORD=あなたのパスワードRコード内では以下のように取得します：
con <- dbConnect(
RPostgres::Postgres(),
dbname = "postgres",
host = "localhost",
port = 5432,
user = "postgres",
password = Sys.getenv("PG_PASSWORD")
).RenvironファイルはGitで管理しないよう、.gitignoreに追加しておいてください。公開しては意味がありませんので…。
もしくはrstudioapi::askForPassword()関数を使って、実行時にパスワードを入力させる方法もあります。
con <- dbConnect(
RPostgres::Postgres(),
dbname = "postgres",
host = "localhost",
port = 5432,
user = "postgres",
password = rstudioapi::askForPassword()
)この場合は、コードを共有してもパスワードは含まれません。
実際にデータベースとテーブルを作成してみます。
RではdbExecute()関数を使ってSQLコマンドを実行できます。SQLコマンドに詳しくない場合は、Rの場合のコードを書いてAIに変換してもらうのがおすすめです（笑）
library(DBI)
con <- dbConnect(
RPostgres::Postgres(),
dbname = "postgres",
host = "localhost",
port = 5432,
user = "postgres",
password = "your_password"
)
# 新しいデータベースを作成
dbExecute(con, "CREATE DATABASE mydb;")
# 切断
dbDisconnect(con)先ほどはdbnameにpostgresを指定しましたが、今回は新しく作成したmydbに接続します。
con <- dbConnect(
RPostgres::Postgres(),
dbname = "mydb",
host = "localhost",
port = 5432,
user = "postgres",
password = "あなたのパスワード"
)
# テーブルを作成
dbExecute(con, "
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
")
# データを挿入
dbExecute(con, "
INSERT INTO users (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 35);
")
# データを確認
dbGetQuery(con, "SELECT * FROM users;")
# 切断
dbDisconnect(con)内容は次の通りです。
usersという名前のテーブルを作成id, name, age, created_atの4つのカラムを定義
ここまでで、Windows上でPostgreSQL、R、Pythonの基本的な環境が整いました。
セットアップした内容
次のセクションでは、より本格的な開発環境を構築するため、WSL上でのセットアップ方法を解説します。
次にWSL（Windows Subsystem for Linux）を使って、より本格的な開発環境を構築します。Linux環境の恩恵を受けながら、Windowsとシームレスに連携できます。
PowerShellを管理者として実行し3、以下のコマンドを入力：
wsl --installインストールが終わると、Linuxディストリビューションのユーザー名とパスワードの作成を求められます。ユーザー名は何でも大丈夫です。パスワードもめちゃくちゃ簡単で問題ありません。
また、パスワードは画面上には表示されませんが、ちゃんと入力されています。慌てて何回も入力しないようにしてください。
このコマンドで以下が自動的にインストールされます：
Ubuntu起動後、まずシステムを最新の状態に更新します：
sudo apt update
sudo apt upgrade -ysudoコマンドは管理者権限でコマンドを実行するためのものです。パスワードの入力を求められたら、先ほど設定したパスワードを入力してください。
Ubuntu標準のリポジトリではPostgreSQLの古いバージョン（現在は16）がインストールされるため、PostgreSQL公式のAptリポジトリを使用して最新版（PostgreSQL 18）をインストールします。
最も簡単な方法は、公式の自動設定スクリプトを使用することです：
# PostgreSQL公式リポジトリを自動設定
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.shこのスクリプトが自動的にリポジトリ設定とGPGキーのインストールを行います。
リポジトリの設定が完了したら、PostgreSQL 18をインストールします：
# PostgreSQL 18をインストール
sudo apt install -y postgresql-18 postgresql-client-18インストールされる主なパッケージ: - postgresql-18: PostgreSQLサーバー本体 - postgresql-client-18: クライアントツール（psqlなど）
# PostgreSQLサービスの起動
sudo service postgresql start# PostgreSQLのバージョンとクラスター状態を確認
pg_lsclusters出力例：
Ver Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.logPostgreSQLに接続：
# PostgreSQLユーザーに切り替えて接続
sudo -u postgres psqlpostgres=# プロンプトが表示されたら成功です。
バージョンを確認するには続けて以下を実行します：
SELECT version();出力例：
PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit終了する場合は \q と入力します。
PostgreSQLのスーパーユーザー（postgres）にパスワードを設定します：
sudo -u postgres psqlpostgres=# プロンプトで以下を実行：
ALTER USER postgres WITH PASSWORD '設定するパスワード';
\q最新版のRをインストールするため、CRANのリポジトリを追加します：
sudo apt update -qq
# 必要なパッケージをインストール
sudo apt install --no-install-recommends software-properties-common dirmngr
# CRANのGPGキーを追加
wget -qO- https://cloud.r-project.org/bin/linux/ubuntu/marutter_pubkey.asc | sudo tee -a /etc/apt/trusted.gpg.d/cran_ubuntu_key.asc
# CRANのリポジトリを追加
sudo add-apt-repository "deb https://cloud.r-project.org/bin/linux/ubuntu $(lsb_release -cs)-cran40/"
# パッケージリストを更新してRをインストール
sudo apt install --no-install-recommends r-base -yR --versionバージョン情報が表示されればインストール成功です。
RからPostgreSQLに接続するために必要なライブラリをインストール：
sudo apt install -y libpq-dev libssl-dev libcurl4-openssl-dev libxml2-devRStudio Serverの最新バージョンをRStudio公式サイトから確認し、以下のコマンドでダウンロードとインストールを行います：
sudo apt-get install gdebi-core
wget https://download2.rstudio.org/server/jammy/amd64/rstudio-server-2025.09.2-418-amd64.deb
sudo gdebi rstudio-server-2025.09.2-418-amd64.debsudo rstudio-server startブラウザでlocalhost:8787にアクセスし、WSLのユーザー名とパスワードでログインします。
すると、RStudioのウェブインターフェースが表示され、Rの開発環境が利用可能になります。
RStudio ServerあるいはVSCodeでWSLに接続4の上Rコンソールを開き、以下を実行：
install.packages(c("DBI", "RPostgres"))library(DBI)
# データベースに接続
con <- dbConnect(
RPostgres::Postgres(),
dbname = "postgres",
host = "localhost",
port = 5432,
user = "postgres",
password = "あなたのパスワード"
)
# 接続確認
dbGetQuery(con, "SELECT version();")
# 切断
dbDisconnect(con)Windows版と同じ手順でデータベースとテーブルを作成できます。
library(DBI)
con <- dbConnect(
RPostgres::Postgres(),
dbname = "postgres",
host = "localhost",
port = 5432,
user = "postgres",
password = "あなたのパスワード"
)
# 新しいデータベースを作成
dbExecute(con, "CREATE DATABASE mydb;")
# 切断
dbDisconnect(con)Windows版と同様に、usersテーブルを作成してみます：
con <- dbConnect(
RPostgres::Postgres(),
dbname = "mydb",
host = "localhost",
port = 5432,
user = "postgres",
password = "あなたのパスワード"
)
# テーブルを作成
dbExecute(con, "
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
")
# データを挿入
dbExecute(con, "
INSERT INTO users (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 35);
")
# データを確認
dbGetQuery(con, "SELECT * FROM users;")
# 切断
dbDisconnect(con)エクスプローラーのアドレスバーに以下を入力：
\\wsl$\Ubuntu\home\あなたのユーザー名これでWSL内のファイルをWindows側から操作できます。
エクスプローラーのサイドバーの最下部の「Linux」からもアクセス可能です。
PostgreSQLが動かなくなった場合などは、WSLを再起動してみてください：
# PowerShellで実行
wsl --shutdownその後、Ubuntuを再起動してPostgreSQLを起動：
sudo service postgresql startRの操作自体はRStudio Serverで行ってもVSCodeでも行ってもどちらでもよいのですが、VSCodeにはPostgreSQL拡張機能があり、データベースの管理が便利です。WindowsのようにpgAdminを使えない代わりに、VSCodeのPostgreSQL拡張機能を利用してテーブルリストなどを確認できます。
VSCodeサイドバーの拡張機能から「ms-ossdata.vscode-pgsql」を検索し、インストールしてください。
インストール終了後、サイドバーにPostgreSQLのアイコンが表示されるのでクリックし、「接続」の右にある「新しい接続を追加する」のアイコンから接続情報を入力します。
記入したらテスト接続をクリックし、成功すれば保存して接続を選択します。
接続後、サイドバーのPostgreSQLアイコンをクリックすると、データベースやテーブルの一覧が表示されます。「サーバー」→「localhost」→「Dataases」→「mydb」→「Schemas」→「public」→「Tables」でテーブル一覧を確認できます。
新しくテーブルを追加した場合などは、右クリックして「最新の情報に更新」を選択して更新を反映してください。
ここまでで、WSL上でPostgreSQL、Rを使った本格的な開発環境が整いました。
セットアップした内容
ここまでで基本的な設定は完了です。次に実際にデータを取り込んで分析を始めてみましょう。
PostgreSQLにデータを投入する方法はいくつかありますが、Rから直接データフレームをテーブルに書き込む方法が最も簡単です。
dbWriteTable()関数を使ってデータフレームをPostgreSQLに書き込むlibrary(DBI)
# データベースに接続
con <- dbConnect(
RPostgres::Postgres(),
dbname = "mydb",
host = "localhost",
port = 5432,
user = "postgres",
password = "あなたのパスワード"
)
library(tidyverse)
# CSVファイルを読み込む
data <- read_csv("path/to/your/data.csv")
# データフレームをPostgreSQLに書き込む
dbWriteTable(con, "my_table", data, overwrite = TRUE)
# データを確認
dbGetQuery(con, "SELECT * FROM my_table LIMIT 5;")
# 切断
dbDisconnect(con)dbWriteTable()がテーブルを作成する関数で、overwrite = TRUEオプションを指定すると、既存のテーブルがあれば上書きされます。
dbGetQuery()では、my_tableから最初の5行を取得して表示しています。
Rのデータハンドリングではおなじみのdplyrパッケージを使って、PostgreSQL上のデータを操作することもできます。データベースを操作するためのdbplyrパッケージが動いて通常のdplyrの文法でSQLクエリを生成してくれます。
dbplyrはdplyrの依存パッケージであるため、dplyrを読み込めば自動的に利用可能です。
データベース上のテーブルはtbl()関数で参照し、collect()関数でRのデータフレームとして取得します。
library(DBI)
library(dplyr)
# データベースに接続
con <- dbConnect(
RPostgres::Postgres(),
dbname = "mydb",
host = "localhost",
port = 5432,
user = "postgres",
password = "あなたのパスワード"
)# テーブルを参照
my_table <- tbl(con, "my_table") |>
filter(column_name > 100) |> # 条件でフィルタリング
select(column1, column2) # 必要なカラムを選択
# Rのデータフレームとして取得
result <- my_table |> collect()
# 結果を表示
print(result)# A tibble: 2 × 2
column1 column2
<chr> <dbl>
1 B 20
2 C 30# 切断
dbDisconnect(con)このコードでは、my_tableテーブルからcolumn_nameが100より大きい行をフィルタリングし、column1とcolumn2のカラムだけを選択しています。最後にcollect()でRのデータフレームとして取得し、表示しています。
このように通常のdplyrの文法でPostgreSQL上のデータを操作できるため、SQLを直接書かなくても高度なデータ操作が可能です。1からSQLを学ばなくてもよいのは大きいですよね！
重要な点は、collect()を呼び出すまでは実際のデータはRに取り込まれず、SQLクエリ5がデータベース側で実行されるということです。データベース側で実行されることで大規模データを効率的に処理できます。
また、注意点として、collect()を呼び出すまではdplyrに含まれる関数しか使うことができず、他のパッケージの関数は使えないことが挙げられます。例えばcollect()前にpivot_longer()を使おうとするとエラーになります。これはtidyrパッケージの関数だからです。
PostGISは、PostgreSQLの拡張機能で、地理情報システム（GIS）データを扱うための強力なツールです。PostGISを利用することで、空間データの格納、クエリ、解析が可能になります。
ここからは、WSL/Ubuntu環境でPostGISをインストールする手順を解説します。
PostgreSQLにPostGIS拡張を追加するには、以下のコマンドを実行します：
sudo apt install postgresql-18-postgis-3 postgresql-18-postgis-3-scripts -yPostGISをインストールしたら、使用するデータベースでPostGIS拡張を有効にします：
# PostgreSQLに接続
sudo -u postgres psql -d mydbデータベース内で以下のSQLコマンドを実行：
-- PostGIS拡張を有効化
CREATE EXTENSION postgis;
-- インストールされた拡張を確認
\dx
-- PostGISのバージョンを確認
SELECT PostGIS_version();
-- 終了
\q\dxコマンドでpostgisが表示されれば、拡張が正常にインストールされています。
記事を書いている時点での最新バージョンは3.6.1です。
mydb=# SELECT PostGIS_version();
postgis_version
---------------------------------------
3.6 USE_GEOS=1 USE_PROJ=1 USE_STATS=1RからPostGIS機能を使うには、sfパッケージが便利です。
install.packages("sf")library(DBI)
library(sf)
library(dplyr)
# データベースに接続
con <- dbConnect(
RPostgres::Postgres(),
dbname = "mydb",
host = "localhost",
port = 5432,
user = "postgres",
password = "設定したパスワード"
)
# サンプルの空間データを作成（東京の主要地点）
points <- data.frame(
name = c("東京駅", "渋谷駅", "新宿駅"),
lon = c(139.7671, 139.7016, 139.7006),
lat = c(35.6812, 35.6580, 35.6938)
)
# sfオブジェクトに変換（EPSG:4326はWGS84座標系）
points_sf <- st_as_sf(points, coords = c("lon", "lat"), crs = 4326)
# PostGISテーブルとして書き込み
st_write(points_sf, con, "stations", delete_layer = TRUE)
# データを読み取り
stations <- st_read(con, "stations")
print(stations)
# 切断
dbDisconnect(con)Simple feature collection with 3 features and 1 field
Geometry type: POINT
Dimension: XY
Bounding box: xmin: 139.7006 ymin: 35.658 xmax: 139.7671 ymax: 35.6938
Geodetic CRS: WGS 84
name geometry
1 東京駅 POINT (139.7671 35.6812)
2 渋谷駅 POINT (139.7016 35.658)
3 新宿駅 POINT (139.7006 35.6938)PostGISには様々な空間分析関数があります。例えば、2点間の距離を計算：
library(DBI)
con <- dbConnect(
RPostgres::Postgres(),
dbname = "mydb",
host = "localhost",
port = 5432,
user = "postgres",
password = "設定したパスワード"
)
# 東京駅と渋谷駅の距離を計算（メートル単位）
# ST_Distance()の前に地理座標系に変換（::geography）
query <- "
SELECT
a.name AS from_station,
b.name AS to_station,
ST_Distance(
a.geometry::geography,
b.geometry::geography
) AS distance_meters
FROM stations a, stations b
WHERE a.name = '東京駅' AND b.name = '渋谷駅';
"
result <- dbGetQuery(con, query)
print(result)
dbDisconnect(con) from_station to_station distance_meters
1 東京駅 渋谷駅 6464.826Query内ではSQL文を書き、ST_Distance()関数を使って2つの地点間の距離を計算しています。::geographyキャストを使うことで、地理座標系での正確な距離計算が可能になります。
PostGISには多数の空間関数があります。代表的なものを紹介します：
ST_Distance(): 2つのジオメトリ間の距離を計算ST_Buffer(): 指定した距離のバッファ（緩衝地帯）を作成ST_Intersects(): 2つのジオメトリが交差するかを判定ST_Within(): あるジオメトリが別のジオメトリ内に含まれるかを判定ST_Area(): ポリゴンの面積を計算ST_Length(): ラインの長さを計算これらの関数を組み合わせることで、複雑な空間分析が可能になります。
PostGISを使うことで、PostgreSQL上で以下のような空間データ分析が可能になります：
特に、メモリに収まりきらない大規模な空間データを扱う場合、PostGISは非常に強力なツールとなります。
本記事では、Windows環境でRとPostgreSQLを中心とした開発環境を構築する方法を、Windows版とWSL版の2つのアプローチでまとめました。
Windows版の特徴
WSL版の特徴
どちらの環境でも、RからPostgreSQLを操作する基本的な手順は同じです。まずはWindows版で試してみて、必要に応じてWSL版に移行するのがおすすめです。
PostgreSQLとPostGISを活用することで、大規模データの管理や空間データの高度な分析が可能になります。ぜひ実際のプロジェクトで活用してみてください。