Using PostgreSQL / PostGIS

Every entity model page has a PostgreSQL / PostGIS table in the Download row: a CREATE TABLE with the model's rules as constraints, a spatial index, and comments with each attribute's IRI. This page loads the table of designated emergency evacuation site (EvacuationSite) into PostGIS, adds a site, and asks which sites are near a station.

You need Docker. A PostgreSQL with PostGIS of your own works the same way.

1. Start PostGIS

bash

docker run -d --name datamodels-postgis -e POSTGRES_HOST_AUTH_METHOD=trust \
  postgis/postgis:16-3.4
# Wait until the database is ready: about 10 seconds the first time, at most 2 minutes.
for i in $(seq 120); do
  docker logs datamodels-postgis 2>&1 | grep -q "init process complete" &&
    docker exec datamodels-postgis pg_isready -U postgres -q && break
  sleep 1
done
# "accepting connections" means ready; otherwise the log says what went wrong.
docker exec datamodels-postgis pg_isready -U postgres || docker logs datamodels-postgis

POSTGRES_HOST_AUTH_METHOD=trust lets anyone connect without a password: use it only for a test on your own computer.

2. Create the table

bash

BASE=https://datamodels.jp/adapters
curl -sSfO "$BASE/postgis/disaster/EvacuationSite.sql"
docker exec -i datamodels-postgis psql -U postgres < EvacuationSite.sql

text

CREATE FUNCTION
CREATE TABLE
CREATE INDEX
COMMENT
COMMENT
…

The columns are the attributes in snake_case: hazardTypes becomes hazard_types. Each field of a value gets its own column (address.addressText becomes address_address_text), a list is an array, and the location is a geometry(Point, 4326).

3. Add a site

Open a SQL prompt with docker exec -it datamodels-postgis psql -U postgres and run:

sql

INSERT INTO evacuation_site (id, name, external_site_id,
  local_government_code, address_address_text, location,
  hazard_types, also_designated_shelter)
VALUES ('urn:ngsi-ld:EvacuationSite:0012', '都立一橋高校', '13101-0012',
  '131016', '東京都千代田区東神田1-12-13',
  ST_SetSRID(ST_MakePoint(139.7810002, 35.6945488), 4326),
  ARRAY['stormSurge', 'earthquake', 'inlandFlood'], true);

text

INSERT 0 1

The table refuses values that break the model's rules: allowed values, patterns such as the 6-digit local government code, minimums, and rules on several columns ("externalSiteId or nationalShelterId"). fire is not a hazard type of this model:

sql

UPDATE evacuation_site SET hazard_types = ARRAY['fire']
WHERE id = 'urn:ngsi-ld:EvacuationSite:0012';

text

ERROR:  new row for relation "evacuation_site" violates check constraint "evacuation_site_hazard_types_check"
DETAIL:  Failing row contains (urn:ngsi-ld:EvacuationSite:0012, 都立一橋高校, null, …).

4. Query by distance

Sites within 1 km of Akihabara Station, with the distance in metres:

sql

WITH station AS (
  SELECT ST_SetSRID(ST_MakePoint(139.7731, 35.6984), 4326)::geography AS p
)
SELECT name, round(ST_Distance(location::geography, p)) AS metres
FROM evacuation_site, station
WHERE ST_DWithin(location::geography, p, 1000);

text

     name     | metres
--------------+--------
 都立一橋高校 |    833
(1 row)

The query measures in metres on the earth (geography). The spatial index of the file is on the geometry; for a large table, add one that this query uses: CREATE INDEX ON evacuation_site USING gist ((location::geography));.

The IRIs in the comments

Each column's comment has the description in Japanese and English, and the attribute's IRI in angle brackets on the last line. Database tools show it, and a query can read it:

sql

SELECT attname AS "column",
  substring(col_description(attrelid, attnum) from '<([^>]+)>$') AS iri
FROM pg_attribute
WHERE attrelid = 'evacuation_site'::regclass
  AND attname IN ('name', 'address_postal_code', 'hazard_types');

text

       column        |                      iri
---------------------+-----------------------------------------------
 name                | https://uri.etsi.org/ngsi-ld/name
 address_postal_code | https://schema.org/postalCode
 hazard_types        | https://datamodels.jp/ns/disaster/hazardTypes
(3 rows)

The IRIs are the same as in the model's @context, so a row and an NGSI-LD entity of the model mean the same thing. When you are done, remove the container with docker rm -f datamodels-postgis.

Your own models

For the models of a site of your own, make the same file with datamodels export (Exporting to other tools).

Using PostgreSQL / PostGIS ​

1. Start PostGIS ​

2. Create the table ​

3. Add a site ​

4. Query by distance ​

The IRIs in the comments ​

Your own models ​

Using PostgreSQL / PostGIS

1. Start PostGIS

2. Create the table

3. Add a site

4. Query by distance

The IRIs in the comments

Your own models