yongestreet/YongeStreet/Services/DataService.cs

using DuckDB.NET.Data;
using FuzzierSharp;

namespace YongeStreet.Services;

public record Road(int RelationId, List<List<double>> Points);

public record CityOverview(string Name, double TotalStreetSize, IList<DateOnly> AvailableDates);

public record CityRoadLengthStats(string Name, double MunicipalRoadLength, DateOnly Date);

public class DataService
{
    private static readonly List<string> CITY_STREET_HIGHWAY =
    [
        "primary",
        "primary_link",
        "secondary",
        "secondary_link",
        "tertiary",
        "tertiary_link",
        "residential",
        "unclassified"
    ];

    private readonly ILogger<DataService> _logger;

    private readonly string path = Environment.GetEnvironmentVariable("PREPROCESSED_DUCKDB_FILE") ??
                                   "/home/didier/all-dressed-programming/YongeStreet/real.duckdb";

    public DataService(ILogger<DataService> logger)
    {
        _logger = logger;
    }

    public IList<Road> GetRoads(string city, DateOnly extractDate)
    {
        using var duckDBConnection = new DuckDBConnection($"Data Source={path};ACCESS_MODE=READ_ONLY");
        duckDBConnection.Open();

        using var command = duckDBConnection.CreateCommand();
        command.CommandText = """
                                  SELECT relation_id, points 
                                  FROM city_road 
                                  WHERE city_name = $city_name 
                                        AND extract_date = $extract_date
                                        AND list_contains($allowed_highway, highway)
                              """;
        command.Parameters.Add(new DuckDBParameter("city_name", city));
        command.Parameters.Add(new DuckDBParameter("extract_date", extractDate));
        command.Parameters.Add(new DuckDBParameter("allowed_highway", CITY_STREET_HIGHWAY));
        using var reader = command.ExecuteReader();

        var ret = new List<Road>();
        while (reader.Read())
        {
            var points = reader.GetFieldValue<List<List<double>>>(1);
            ret.Add(new Road(reader.GetInt32(0), points));
        }

        return ret;
    }

    public List<CityOverview> GetCityOverview()
    {
        using var duckDBConnection = new DuckDBConnection($"Data Source={path};ACCESS_MODE=READ_ONLY");
        duckDBConnection.Open();

        using var command = duckDBConnection.CreateCommand();
        command.CommandText = """
                              WITH raw AS (SELECT city_name, 
                                         SUM(length) FILTER (list_contains($allowed_highway, highway) AND extract_date = (Date '2025-01-01')) AS length,
                                         LIST(DISTINCT extract_date) AS available_years
                                  FROM city_road 
                                  GROUP BY city_name
                                  ORDER BY length desc)
                              SELECT * FROM raw WHERE length > 1000
                              """;
        command.Parameters.Add(new DuckDBParameter("allowed_highway", CITY_STREET_HIGHWAY));
        using var reader = command.ExecuteReader();

        var ret = new List<CityOverview>();
        while (reader.Read())
            ret.Add(new CityOverview(reader.GetString(0),
                reader.GetDouble(1),
                reader.GetFieldValue<List<DateOnly>>(2)));

        return ret;
    }

    public List<string> GetAllCityNames()
    {
        using var duckDBConnection = new DuckDBConnection($"Data Source={path};ACCESS_MODE=READ_ONLY");
        duckDBConnection.Open();

        using var command = duckDBConnection.CreateCommand();
        command.CommandText = "SELECT DISTINCT city_name FROM city_road";
        using var reader = command.ExecuteReader();

        var ret = new List<string>();
        while (reader.Read()) ret.Add(reader.GetString(0));
        return ret;
    }

    public List<CityOverview> GetCityOverview(string search)
    {
        _logger.LogWarning("Seeking " + search);
        if (search == "") return GetCityOverview().Take(20).ToList();

        var cityNames = GetAllCityNames();
        var foundCityNames = Process.ExtractSorted(search, cityNames);
        return (
            from cityName in foundCityNames
            join city in GetCityOverview() on cityName.Value equals city.Name
            orderby cityName.Score descending
            select city
        ).Take(20).ToList();
    }

    public IList<CityRoadLengthStats> GetCityRoadLengthStats(string city)
    {
        using var duckDBConnection = new DuckDBConnection($"Data Source={path};ACCESS_MODE=READ_ONLY");
        duckDBConnection.Open();

        using var command = duckDBConnection.CreateCommand();
        command.CommandText = """
                                  SELECT SUM(length) FILTER (list_contains($allowed_highway, highway)) AS length,
                                         extract_date AS date
                                  FROM city_road 
                                  WHERE city_name = $city_name 
                                  GROUP BY extract_date
                                  ORDER BY date DESC
                              """;
        command.Parameters.Add(new DuckDBParameter("city_name", city));
        command.Parameters.Add(new DuckDBParameter("allowed_highway", CITY_STREET_HIGHWAY));
        using var reader = command.ExecuteReader();

        var ret = new List<CityRoadLengthStats>();
        while (reader.Read())
            ret.Add(new CityRoadLengthStats(city,
                reader.GetInt32(0),
                reader.GetFieldValue<DateOnly>(1)));

        return ret;
    }
}