public IEnumerable <int> GetPointsForStopGroupId(int stopGroupId)
{
var results = _database.ExecuteQuery(
"SELECT id FROM public.points WHERE points.stop_group_id=@StopGroupId",
new { StopGroupId = stopGroupId });
return(results.Select(row => (int)row.id));
}
public IEnumerable <StopFindResult> FindNearestStops(double lon, double lat, int limit)
{
var query = @"SELECT
s.stop_group_id as StopGroupId,
sg.name as Name,
AVG(ST_Distance(ST_MakePoint(location[0], location[1])::geography, ST_PointFromText(@Point)::geography)) as Distance
FROM public.stops s
JOIN public.stop_groups sg ON (s.stop_group_id = sg.id)
GROUP BY s.stop_group_id, sg.name
ORDER BY Distance
LIMIT @Limit;";
return(_database.ExecuteQuery <StopFindResult>(query,
new { Point = $"POINT({GetStringWithDotSeparator(lon)} {GetStringWithDotSeparator(lat)})", Limit = limit }));
}