1
Fork 0
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

92 lines
1.8 KiB
Go

package main
import (
"encoding/csv"
"io"
"os"
"strconv"
"strings"
"zombiezen.com/go/sqlite"
"zombiezen.com/go/sqlite/sqlitex"
)
const latestData = "./data/D_Bahnhof_2020_alle.CSV"
const (
rowId = 1
rowName = 3
rowLat = 6
rowLon = 5
)
func parseData() (*sqlite.Conn, error) {
// Open data file
dataFile, err := os.Open(latestData)
if err != nil {
return nil, err
}
defer dataFile.Close()
// Create in-memory sqlite database
db, err := createDB()
if err != nil {
return nil, err
}
// Read csv
r := csv.NewReader(dataFile)
r.Comma = ';'
i := 0
for {
record, err := r.Read()
if err == io.EOF {
break
}
if err != nil {
return nil, err
}
// Skip header
i++
if i == 1 {
continue
}
// Insert data
lat, err := strconv.ParseFloat(strings.ReplaceAll(record[rowLat], ",", "."), 64)
if err != nil {
return nil, err
}
lon, err := strconv.ParseFloat(strings.ReplaceAll(record[rowLon], ",", "."), 64)
if err != nil {
return nil, err
}
err = sqlitex.Exec(db, "INSERT INTO stations (id, name, lat, lon) VALUES (?, ?, ?, ?)", nil, record[rowId], record[rowName], lat, lon)
if err != nil {
return nil, err
}
}
// Finished
return db, nil
}
type station struct {
id string
name string
lat float64
lon float64
}
func findStations(db *sqlite.Conn, name string) (stations []*station, err error) {
err = sqlitex.Exec(db, "select id, name, lat, lon from stations where id = ? or ( name like '%' || ? || '%' and ? not in ( select id from stations ) ) order by name asc", func(stmt *sqlite.Stmt) error {
stations = append(stations, &station{
id: stmt.ColumnText(0),
name: stmt.ColumnText(1),
lat: stmt.ColumnFloat(2),
lon: stmt.ColumnFloat(3),
})
return nil
}, name, name, name)
if err != nil {
return nil, err
}
return stations, nil
}