website/pkg/models/world_cities.go

package models

import (
	"encoding/csv"
	"errors"
	"fmt"
	"os"
	"strconv"
	"strings"

	"code.nonshy.com/nonshy/website/pkg/log"
)

// WorldCities is a lookup database of cities/countries to their geo coordinates.
//
// It aids the search by location feature of the member directory. Data obtained from simplemaps.com
type WorldCities struct {
	ID        uint64 `gorm:"primary_key"`
	City      string
	State     string
	Country   string
	ISO       string
	Canonical string `gorm:"index"` // the full City, State, ISO string
	Latitude  float64
	Longitude float64
}

// SearchWorldCities handles a type-ahead search query.
func SearchWorldCities(query string) ([]*WorldCities, error) {
	var (
		like   = fmt.Sprintf("%%%s%%", strings.ToLower(query))
		result = []*WorldCities{}
		tx     = DB.Model(&WorldCities{}).Where(
			"canonical ILIKE ?",
			like,
		).Limit(50).Scan(&result)
	)
	return result, tx.Error
}

// FindWorldCity looks up a world city by its Canonical name from typeahead search.
func FindWorldCity(canonical string) (*WorldCities, error) {
	var (
		city   *WorldCities
		result = DB.Model(&WorldCities{}).Where("canonical = ?", canonical).First(&city)
	)
	if result.Error != nil {
		return nil, result.Error
	}
	return city, nil
}

// InitializeWorldCities from an input CSV spreadsheet from simplemaps.com.
//
// This will wipe and reset the WorldCities table from the imported spreadsheet.
//
// The CSV file needs at least the columns: id, city, admin_name, country, iso2, lat, lng.
//
// The CSV can be downloaded from: https://simplemaps.com/data/world-cities
func InitializeWorldCities(csvFilename string) error {
	fh, err := os.Open(csvFilename)
	if err != nil {
		return err
	}

	reader := csv.NewReader(fh)

	// Read the header row and find the required fields.
	fields := map[string]*int{
		"id":         nil,
		"city":       nil,
		"admin_name": nil,
		"country":    nil,
		"iso2":       nil,
		"lat":        nil,
		"lng":        nil,
	}
	header, err := reader.Read()
	if err != nil {
		return fmt.Errorf("CSV header row: %s", err)
	}

	// Map the header fields to their index.
	for i, field := range header {
		if _, ok := fields[field]; ok {
			fields[field] = &i
		}
	}

	// Sanity check that all header fields are found.
	var errHeader error
	for k, v := range fields {
		if v == nil {
			log.Error("WorldCities CSV: required header %s not found", k)
			errHeader = errors.New("missing one or more required csv headers")
		}
	}
	if errHeader != nil {
		return errHeader
	}

	// Action!

	// Flush the existing WorldCities table.
	if tx := DB.Exec("DELETE FROM world_cities"); tx.Error != nil {
		return fmt.Errorf("deleting world_cities: %s", tx.Error)
	}

	// Populate the database.
	for {
		row, err := reader.Read()
		if err != nil {
			break
		}

		// Cast data types.
		id, err1 := strconv.ParseUint(row[*fields["id"]], 10, 64)
		lat, err2 := strconv.ParseFloat(row[*fields["lat"]], 64)
		lon, err3 := strconv.ParseFloat(row[*fields["lng"]], 64)
		if err1 != nil || err2 != nil || err3 != nil {
			return fmt.Errorf("row %+v failed to cast one or more data types: id (%s), lat (%s), lng (%s)", row, err1, err2, err3)
		}

		record := &WorldCities{
			ID:        id,
			City:      row[*fields["city"]],
			State:     row[*fields["admin_name"]],
			Country:   row[*fields["country"]],
			ISO:       row[*fields["iso2"]],
			Latitude:  lat,
			Longitude: lon,
		}

		// Canonical string for search.
		var canonical string
		if record.State != "" {
			canonical = fmt.Sprintf("%s, %s, %s", record.City, record.State, record.ISO)
		} else {
			canonical = fmt.Sprintf("%s, %s", record.City, record.ISO)
		}
		record.Canonical = canonical

		result := DB.Create(&record)
		if result.Error != nil {
			return fmt.Errorf("create row %+v: %s", record, result.Error)
		}

		log.Info("WorldCities: loaded %s, %s", record.City, record.Country)
	}

	return nil
}