stops.go

package main

import (
	"fmt"
	"encoding/json"
	"io/ioutil"

	_ "github.com/lib/pq"
	_ "database/sql"

	"github.com/jmoiron/sqlx"
)

type Stop struct {
	StopId    int     `json:"stopId,string",db:"stopid"`
	LongName  string  `json:"longName",db:"longname"`
	ShortName string  `json:"shortName",db:"shortname"`
	Latitude  float32 `json:"latitude",db:"latitude"`
	Longitude float32 `json:"longitude",db:"longitude"`
}

const StopTable string = `
DROP TABLE stops;
CREATE TABLE stops (
    stopId int PRIMARY KEY,
    shortName varchar(100),
    longName varchar(100),
    location point
);
CREATE INDEX location_idx on stops USING GIST(location)
`

const StopTableInsert string = `
INSERT INTO stops (stopId, shortName, longName, location) VALUES ($1, $2, $3, point($4, $5))
`

const StopTableNearest string = `
SELECT stopId, shortName, longName, location[0] as latitude, location[1] as longitude FROM stops ORDER BY location <-> point($1, $2) LIMIT $3
`

func readStops(fname string) []Stop {
	file, err := ioutil.ReadFile(fname)
	if err != nil {
	  panic(err)
	}

	var stops []Stop
	json.Unmarshal(file, &stops)
	return stops
}

type Route struct {
	Id			int		`json:"id,string"`
	Directions	[]Direction	`json:"directions"`
}

type Direction struct {
	Name 		string		`json:"name"`
	Stations	[]int		`json:"stations"`
	Schedule	Days		`json:"schedule"`
}

type Days struct {
	Weekdays	[]Trip	`json:"weekday"`
	Saturday	[]Trip	`json:"saturday"`
	Sunday		[]Trip	`json:"sunday"`
}

type Trip []StopTime

type StopTime struct {
	StopId		int		`json:"station"`
	StopType	string	`json:"type"`
	Time		string	`json:"time"`
}

func readRoute(fname string) *Route {
	file, err := ioutil.ReadFile(fname)
	if err != nil {
	  panic(err)
	}

	var route Route
	json.Unmarshal(file, &route)
	return &route
}

func createSchema(db *sqlx.DB) {
	if _, err := db.Exec(StopTable); err != nil {
		panic(err)
	}
}

func persistStops(db *sqlx.DB, stops *[]Stop) {
	tx, err := db.Begin()
	defer func() {
		if r := recover(); r != nil {
			tx.Rollback()
			panic(r)
		}
	}()

	stmt, err := tx.Prepare(StopTableInsert)
	if err != nil {
		panic(err)
	}

	for _, s := range *stops {
		if _, err := stmt.Exec(s.StopId, s.ShortName, s.LongName, s.Latitude, s.Longitude); err != nil {
			panic(err)
		}
	}

	tx.Commit()
}

func findNearestStops(db *sqlx.DB, latitude, longitude float32) []Stop {
	stops := []Stop{}
	err := db.Select(&stops, StopTableNearest, latitude, longitude, 10)
	if err != nil {
		panic(err)
	}

	return stops
}

func main() {
	stops := readStops("allStops.json")
	fmt.Println(stops[10])

	route := readRoute("route-01.json")
	s := route.Directions[0].Schedule.Sunday[0][0]
	fmt.Println(s)

	db, err := sqlx.Open("postgres", "user=gudmundur dbname=gulur sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	createSchema(db)
	persistStops(db, &stops)

	fmt.Println(findNearestStops(db, 64.131956, -21.909978))
}