replace geojson with psql table

[00Bock]

The attributes should be similar to existing InnoZ projects. Best would be, to use the current naming in innoz-tracks as they should be similar in data characteristics. @kjoscha can you post the datastructure here at some point or show me how to get the data structure from innoz tracks?

So far I would suggest something like:

CREATE TABLE plans
(
  id serial NOT NULL,
  agent_id text NOT NULL,
  started_at time without time zone NOT NULL,
  ended_at time without time zone NOT NULL,
  latitude_start numeric(11,8) NOT NULL,
  longitude_start numeric(11,8) NOT NULL,
  latitude_end numeric(11,8) NOT NULL,
  longitude_end numeric(11,8) NOT NULL,
  mode character(25) NOT NULL,
  scenario_id text NOT NULL
)

Other attributes could be calculated afterwards (e.g. distance). Once we use MATSim event-files, data-structure could look something like:

CREATE TABLE trips
(
  id serial NOT NULL,
  agent_id text NOT NULL,
  started_at time without time zone NOT NULL,
  ended_at time without time zone NOT NULL,
  from_activity_type character varying NOT NULL,,
  to_activity_type character varying NOT NULL,,
  latitude_start numeric(11,8) NOT NULL,
  longitude_start numeric(11,8) NOT NULL,
  latitude_end numeric(11,8) NOT NULL,
  longitude_end numeric(11,8) NOT NULL,
  mode character(25) NOT NULL,
  access_time time without time zone,
  access_distance numeric,
  egress_time numeric,
  egress_distance numeric,
  geom geometry NOT NULL,
  scenario_id text NOT NULL
)

[wese-da]

CREATE TABLE plans ( id serial NOT NULL, agent_id text NOT NULL, started_at time without time zone NOT NULL, ended_at time without time zone NOT NULL, latitude_start numeric(11,8) NOT NULL, longitude_start numeric(11,8) NOT NULL, latitude_end numeric(11,8) NOT NULL, longitude_end numeric(11,8) NOT NULL, mode character(25) NOT NULL, scenario_id text NOT NULL )

Looks good so far. We could also include the _from_activitytype and _to_activitytype attributes since they are defined in the plans.

EDIT: Do we need the _scenarioid attribute? I would rather use it as schema name (at least that's my intuition).

[00Bock]

upps. yes, definately!

CREATE TABLE plans
(
  id serial NOT NULL,
  agent_id text NOT NULL,
  started_at time without time zone NOT NULL,
  ended_at time without time zone NOT NULL,
  from_activity_type character varying NOT NULL,
  to_activity_type character varying NOT NULL,
  latitude_start numeric(11,8) NOT NULL,
  longitude_start numeric(11,8) NOT NULL,
  latitude_end numeric(11,8) NOT NULL,
  longitude_end numeric(11,8) NOT NULL,
  mode character(25) NOT NULL,
  scenario_id text NOT NULL
)

[00Bock]

is there anything like vehicle_type in the moment?

[wese-da]

There is only the default vehicle type at the moment. We didn't explicitly define private cars so far because we didn't need them.

Would definitely make sense to add vehicle types if we're calculating emissions and electric mobility.

[00Bock]

ok... seems like the question is still open if we should have many tables for each scenario or have one table with an attribute for the scenario identifier. Let's ask til...

[00Bock]

Til is in favor of a large single table. So let's stick to the scenario_id.

[BenSto]

There is now a table and a model done via migration in the playground branch that is equivalent to

CREATE TABLE plans
(
  id serial NOT NULL,
  agent_id text NOT NULL,
  started_at time without time zone NOT NULL,
  ended_at time without time zone NOT NULL,
  from_activity_type character varying NOT NULL,
  to_activity_type character varying NOT NULL,
  latitude_start numeric(11,8) NOT NULL,
  longitude_start numeric(11,8) NOT NULL,
  latitude_end numeric(11,8) NOT NULL,
  longitude_end numeric(11,8) NOT NULL,
  mode character(25) NOT NULL,
  scenario_id text NOT NULL
)

so you can start testing to fill the table with matsim @dhosse @00Bock

[BenSto]

see commit 12cf8a64f7ec3b97f7b818db9f5e21c4b955d1a5

[BenSto]

I have created geographies (https://postgis.net/docs/geography.html) instead of lat lon.. so you have to create geometries...the advantages are pretty obvious, I guess.

[BenSto]

ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)

would be the common way....

[wese-da]

d12505c69511de2a45de49e3716cad2d1d11b8a8 enables writing agents' routines in the plans table

[BenSto]

enables writing agents' routines in the plans table

Does this work inside the rails app? When I create a new scenario the plans table stays empty :sob:

[wese-da]

Hm... okay, works at my laptop :confused:

[wese-da]

This could have sth. to do with the postgreSQL users. The default in the scenario generation code is user / pw postgres

[BenSto]

In other projects we use a user called apprunner for those operations running the app and DB. So that we don't have someone running it with admin rights.

[BenSto]

see extracts from the ansible setup for the consulting webpage:

- name: create database user
  postgresql_user: name=apprunner password={{railsapp_database_password}}

- name: create apprunner user
  user: name=apprunner createhome=no home=/srv/{{railsapp_name}}

[wese-da]

You may pull and try again...

[BenSto]

As the table and model are now existing and got filled with data by matsim successfully, I think this Issue is potentially solved?

[wese-da]

I would definitely think so. Objections?