[付録B] 地図の上に100人ほど配置してみる(メモ)
$ psql -U postgres -p 15432
Password for user postgres:
psql (13.4, server 12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.
- データベースのリストを表示
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
hiro_db | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
- データベース"hiro_db"とのコネクション確立
postgres=# \c hiro_db
psql (13.4, server 12.5 (Debian 12.5-1.pgdg100+1))
You are now connected to database "hiro_db" as user "postgres".
- テーブルの一覧表示
hiro_db=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+----------
public | configuration | table | postgres
public | pointsofinterest | table | postgres
public | spatial_ref_sys | table | postgres
public | ways | table | postgres
public | ways_vertices_pgr | table | postgres
(5 rows)
- テーブルの属性の調査(テーブルに含まれるカラムの一覧を取得する)
hiro_db=# \d ways_vertices_pgr
Table "public.ways_vertices_pgr"
Column | Type | Collation | Nullable | Default
----------+----------------------+-----------+----------+-----------------------------------------------
id | bigint | | not null | nextval('ways_vertices_pgr_id_seq'::regclass)
osm_id | bigint | | |
eout | integer | | |
lon | numeric(11,8) | | |
lat | numeric(11,8) | | |
cnt | integer | | |
chk | integer | | |
ein | integer | | |
the_geom | geometry(Point,4326) | | |
Indexes:
"ways_vertices_pgr_pkey" PRIMARY KEY, btree (id)
"ways_vertices_pgr_osm_id_key" UNIQUE CONSTRAINT, btree (osm_id)
"ways_vertices_pgr_the_geom_idx" gist (the_geom)
Referenced by:
TABLE "ways" CONSTRAINT "ways_source_fkey" FOREIGN KEY (source) REFERENCES ways_vertices_pgr(id)
TABLE "ways" CONSTRAINT "ways_source_osm_fkey" FOREIGN KEY (source_osm) REFERENCES ways_vertices_pgr(osm_id)
TABLE "ways" CONSTRAINT "ways_target_fkey" FOREIGN KEY (target) REFERENCES ways_vertices_pgr(id)
TABLE "ways" CONSTRAINT "ways_target_osm_fkey" FOREIGN KEY (target_osm) REFERENCES ways_vertices_pgr(osm_id)
hiro_db=# CREATE TABLE "point_table" (
hiro_db(# id integer,
hiro_db(# longitude float,
hiro_db(# latitude float
hiro_db(# );
CREATE TABLE
hiro_db=# \copy point_table from 'c:\\Users\\ebata\\hirohakama\\100.csv' with csv
COPY 100
hiro_db=# select addGeometryColumn('point_table','the_geom',4326,'POINT',2);
addgeometrycolumn
----------------------------------------------------------
public.point_table.the_geom SRID:4326 TYPE:POINT DIMS:2
(1 row)
hiro_db=# \d point_table
Table "public.point_table"
Column | Type | Collation | Nullable | Default
-----------+----------------------+-----------+----------+---------
id | integer | | |
longitude | double precision | | |
latitude | double precision | | |
the_geom | geometry(Point,4326) | | |\
hiro_db=# UPDATE point_table SET the_geom = ST_GeomFromText('POINT(' || longitude || ' ' || latitude || ')',4326);
UPDATE 100
hiro_db=# select * from point_table;
id | longitude | latitude | the_geom
----+------------+-----------+----------------------------------------------------
0 | 139.460025 | 35.595017 | 0101000020E6100000F54A5986B86E614032C85D8429CC4140
1 | 139.463866 | 35.59894 | 0101000020E61000003E7782FDD76E61401422E010AACC4140
2 | 139.4717 | 35.593678 | 0101000020E6100000BE30992A186F6140662D05A4FDCB4140
3 | 139.467006 | 35.600335 | 0101000020E6100000252191B6F16E614074D2FBC6D7CC4140
4 | 139.476457 | 35.597946 | 0101000020E6100000691EC0223F6F61401363997E89CC4140
5 | 139.463482 | 35.599743 | 0101000020E61000001C0934D8D46E6140A180ED60C4CC4140
6 | 139.47421 | 35.594217 | 0101000020E6100000F52D73BA2C6F614016DD7A4D0FCC4140
7 | 139.46608 | 35.58624 | 0101000020E61000003BAA9A20EA6E6140B3CD8DE909CB4140
8 | 139.461828 | 35.591831 | 0101000020E6100000118C834BC76E614026E1421EC1CB4140
9 | 139.462946 | 35.588654 | 0101000020E61000000C3A2174D06E61406D54A70359CB4140
10 | 139.479771 | 35.593131 | 0101000020E61000003752B6485A6F6140349F73B7EBCB4140
11 | 139.462382 | 35.586075 | 0101000020E61000004A0856D5CB6E614069006F8104CB4140
12 | 139.460178 | 35.592046 | 0101000020E6100000D78A36C7B96E614023DDCF29C8CB4140
13 | 139.470633 | 35.595139 | 0101000020E610000063EDEF6C0F6F61407EC9C6832DCC4140