rc/tma

Watch

No description

templ 47.2%
Go 43.2%
CSS 6%
Nix 3.6%

Find a file

Repository files (latest commit first)
Filename	Latest commit message	Latest commit date
Roberto Castellotti 0cf6f03fe4 update templ		2026-08-26 18:21:56 +02:00
amt	go migration: basic web service	2026-08-19 16:37:13 +02:00
assets	tweak themes	2026-08-20 21:57:24 +02:00
models	move models	2026-08-20 21:08:02 +02:00
templates	favorites with htmx	2026-08-26 18:09:07 +02:00
.air.toml	go migration: frontend, migration to tailwind	2026-08-19 21:23:00 +02:00
.envrc	first commit	2026-06-19 13:49:57 +02:00
.gitignore	nix	2026-08-20 17:53:24 +02:00
amt.db	big updates	2026-06-23 23:16:55 +02:00
db.go	move models	2026-08-20 21:08:02 +02:00
flake.lock	first commit	2026-06-19 13:49:57 +02:00
flake.nix	update templ	2026-08-26 18:21:56 +02:00
go.mod	update templ	2026-08-26 18:13:47 +02:00
go.sum	update templ	2026-08-26 18:13:47 +02:00
handlers.go	favorites with htmx	2026-08-26 18:09:07 +02:00
main.go	favorites with htmx	2026-08-26 18:09:07 +02:00
README.md	update readme	2026-08-20 18:03:22 +02:00

README.md

tma

nix run git+https://g.rcastellotti.dev/rc/tma.git -- updater --db /tmp/amt.db
nix run git+https://g.rcastellotti.dev/rc/tma.git -- server --port 9172 --db /tmp/amt.db

endpoints

observations

multiple rows for a single line

every line has an id and a public name (whatever is displayed on buses)

select * from lines where name like '189%';

╭────────┬───────┬────────────────────────────┬─────────────────────┬──────────┬────────────────────────────╮
│   id   │ name  │         start_stop         │      end_stop       │ category │        description         │
╞════════╪═══════╪════════════════════════════╪═════════════════════╪══════════╪════════════════════════════╡
│ 189-00 │ '189' │ P.za Ponchielli (Pegli FS) │ Via Salgari (Pegli) │ '1'      │                            │
│ 189-03 │ 189/  │ P.za Ponchielli (Pegli FS) │ Via Salgari (Pegli) │ '1'      │ Transita per Via Lorenzini │
│ 189-04 │ 189/  │ Pegli (Via Pavia)          │ Via Salgari (Pegli) │ '1'      │                            │
│ 189-05 │ 189/  │ Pegli (Via Pavia)          │ Via Salgari (Pegli) │ '1'      │                            │
│ 189-06 │ 189/  │ Pegli (Via Pavia)          │ Via Salgari (Pegli) │ '1'      │ Transita per Via Lorenzini │
│ 189-07 │ 189/  │ Pegli (Via Pavia)          │ Via Salgari (Pegli) │ '1'      │ Transita per Via Lorenzini │
╰────────┴───────┴────────────────────────────┴─────────────────────┴──────────┴────────────────────────────╯

the line "name" cannot be an identifer as multiple entries are present in the original data.

inferring direction from line_stops.line_id

SELECT line_id, stop_id, position, start_stop, end_stop, s.name, extra
FROM line_stops ls
JOIN lines l ON l.id = substr(ls.line_id, 1, instr(ls.line_id, '_') - 1)
JOIN stops s ON ls.stop_id = s.id
WHERE ls.line_id IN ('015-00_1', '015-00_2')
  AND ls.position IN (
    SELECT MIN(position) FROM line_stops WHERE line_id = ls.line_id
    UNION
    SELECT MAX(position) FROM line_stops WHERE line_id = ls.line_id
  )
ORDER BY ls.line_id, ls.position;

╭──────────┬─────────┬──────────┬───────────────────────┬─────────────────────────────┬───────────────────────┬─────────────────────╮
│ line_id  │ stop_id │ position │      start_stop       │          end_stop           │         name          │        extra        │
╞══════════╪═════════╪══════════╪═══════════════════════╪═════════════════════════════╪═══════════════════════╪═════════════════════╡
│ 015-00_1 │ '0331'  │        1 │ Via Gianelli (Quinto) │ P.za Verdi (Staz. Brignole) │ GIANELLI/CAPOLINEA    │ capolinea 15        │
│ 015-00_1 │ '0153'  │       47 │ Via Gianelli (Quinto) │ P.za Verdi (Staz. Brignole) │ BRIGNOLE FS/CAPOLINEA │ cap. 15 685 686 687 │
│ 015-00_2 │ '0153'  │        1 │ Via Gianelli (Quinto) │ P.za Verdi (Staz. Brignole) │ BRIGNOLE FS/CAPOLINEA │ cap. 15 685 686 687 │
│ 015-00_2 │ '0331'  │       46 │ Via Gianelli (Quinto) │ P.za Verdi (Staz. Brignole) │ GIANELLI/CAPOLINEA    │ capolinea 15        │
╰──────────┴─────────┴──────────┴───────────────────────┴─────────────────────────────┴───────────────────────┴─────────────────────╯

different lines (line_id) have the same start_stop and end_stop, to extract the real values we have to swap them if line_id is suffixed with _2

schema is denormalized and `stops` has a `lines` field

select * from stops where id='0331';

╭────────┬────────────────────┬──────────────┬───────────┬──────────┬────────────────────────────┬─────────╮
│   id   │        name        │    extra     │    lat    │   lon    │           lines            │ unknown │
╞════════╪════════════════════╪══════════════╪═══════════╪══════════╪════════════════════════════╪═════════╡
│ '0331' │ GIANELLI/CAPOLINEA │ capolinea 15 │ 44.384392 │ 9.019631 │ 015-00,N2-00,515-02,607-00 │ '1'     │
╰────────┴────────────────────┴──────────────┴───────────┴──────────┴────────────────────────────┴─────────╯

however this does not include information around the direction, we are better off retrieving this data joining the table with line_stops

SELECT *
FROM stops
JOIN line_stops ls ON ls.stop_id = stops.id
WHERE id = '0331';

╭────────┬────────────────────┬──────────────┬───────────┬──────────┬────────────────────────────┬─────────┬──────────┬─────────┬──────────╮
│   id   │        name        │    extra     │    lat    │   lon    │           lines            │ unknown │ line_id  │ stop_id │ position │
╞════════╪════════════════════╪══════════════╪═══════════╪══════════╪════════════════════════════╪═════════╪══════════╪═════════╪══════════╡
│ '0331' │ GIANELLI/CAPOLINEA │ capolinea 15 │ 44.384392 │ 9.019631 │ 015-00,N2-00,515-02,607-00 │ '1'     │ 015-00_1 │ '0331'  │        1 │
│ '0331' │ GIANELLI/CAPOLINEA │ capolinea 15 │ 44.384392 │ 9.019631 │ 015-00,N2-00,515-02,607-00 │ '1'     │ 015-00_2 │ '0331'  │       46 │
│ '0331' │ GIANELLI/CAPOLINEA │ capolinea 15 │ 44.384392 │ 9.019631 │ 015-00,N2-00,515-02,607-00 │ '1'     │ 515-02_1 │ '0331'  │       16 │
│ '0331' │ GIANELLI/CAPOLINEA │ capolinea 15 │ 44.384392 │ 9.019631 │ 015-00,N2-00,515-02,607-00 │ '1'     │ 607-00_1 │ '0331'  │        1 │
│ '0331' │ GIANELLI/CAPOLINEA │ capolinea 15 │ 44.384392 │ 9.019631 │ 015-00,N2-00,515-02,607-00 │ '1'     │ 607-00_2 │ '0331'  │       51 │
│ '0331' │ GIANELLI/CAPOLINEA │ capolinea 15 │ 44.384392 │ 9.019631 │ 015-00,N2-00,515-02,607-00 │ '1'     │ N2-00_1  │ '0331'  │        6 │
╰────────┴────────────────────┴──────────────┴───────────┴──────────┴────────────────────────────┴─────────┴──────────┴─────────┴──────────╯

data retrieved from the `descrizione` field in `timetables.xml` has different separators

---> or
-->

as we can check with grep -R "\ \--->" . and grep -R "\ \-->" . in timetables after running go run cmd/dev/main.go timetables, an example:

https://www.amt.genova.it/amt/servizi/orari_xml.php?linea=189&gg=19&mm=6&aa=2026

returns:

<?xml version='1.0' encoding='UTF-8'?>
<data>
<servizio>
  <id>A2026-V1</id>
  <giorno>2026-08-19</giorno>
  <tipo>STANDARD</tipo>
  <periodo>agostino</periodo>
  <tipogiorno>feriale</tipogiorno>
  <linea>
    <codice>189</codice>
    <barrato>0</barrato>
    <targa>LINEA  189</targa>
    <descrizione>Pegli FS (P.za Ponchielli) - Pegli (Quartiere Giardino)</descrizione>
    <trattaasc>
      <descrizione>P.za Ponchielli (Pegli FS) ---> Via Salgari (Pegli)</descrizione>
      <partenze>05:30 05:59 06:29 06:45 07:01 07:17 07:50 08:07 08:39 08:55 09:12 09:50 10:08 10:47 11:06 11:44 12:02 12:21 12:58 13:15 13:32 13:50 14:25 14:43 15:18 15:36 15:52 16:07 16:23 16:41 17:17 17:36 17:54 18:10 18:27 18:44 19:17 19:35 19:50 20:06 20:21 20:41 21:16 22:16 23:11 </partenze>
    </trattaasc>
    <trattadisc>
      <descrizione>Via Salgari (Pegli) ---> P.za Ponchielli (Pegli FS)</descrizione>
      <partenze>05:40 06:09 06:40 06:56 07:12 07:28 08:01 08:20 08:52 09:08 09:25 10:05 10:23 11:03 11:22 12:00 12:17 12:36 13:11 13:28 13:45 14:03 14:38 14:56 15:32 15:50 16:06 16:21 16:37 16:55 17:32 17:51 18:09 18:25 18:42 18:59 19:32 19:50 20:04 20:18 20:33 20:53 21:28 22:28 23:23 </partenze>
    </trattadisc>
  </linea>
  <linea>
    <codice>189</codice>
    <barrato>3</barrato>
    <targa>LINEA  189   BARRATA</targa>
    <descrizione></descrizione>
    <trattaasc>
      <descrizione>P.za Ponchielli (Pegli FS) -->Via Lorenzini-->Via Salgari (Pegli)</descrizione>
      <partenze>07:33 08:23 09:32 10:27 11:24 12:41 14:07 15:00 16:59 19:01 </partenze>
    </trattaasc>
    <trattadisc>
      <descrizione>Via Salgari  (Pegli) ---> P.za Ponchielli (Pegli FS)</descrizione>
      <partenze>07:47 08:37 09:48 10:43 11:41 12:58 14:22 15:15 17:16 19:18 </partenze>
    </trattadisc>
  </linea>
</servizio>
</data>