public JArray GetHomeZoneWheel([FromRoute] int day, double longy, double lat)
{
string _selectString = "SELECT hz.fraction AS fraction, ST_Distance(ST_Centroid(z1.geom)::geography, ST_Centroid(z2.geom)::geography) AS distance, SUM(act.people) *hz.fraction AS people FROM \"MtcHomezones\" AS hz INNER JOIN \"Mtcs\" AS z1 ON z1.id = hz.zone INNER JOIN \"Mtcs\" AS z2 ON z2.id = hz.homezone INNER JOIN \"MtcActivitys\" AS act ON act.zone = hz.zone AND act.day = hz.day WHERE ST_Contains(z1.geom, ST_SetSRID(ST_MakePoint(" + longy + ", " + lat + "), 4326)) AND hz.day = " + day + " GROUP BY hz.id, z1.geom, z2.geom ORDER BY distance ASC";
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
using (var cmd = new NpgsqlCommand(_selectString, conn))
{
using (var reader = cmd.ExecuteReader())
{
List <HomezoneWheelDTO> homezoneWheelDtoList = new List <HomezoneWheelDTO>();
while (reader.Read())
{
InterfaceDataReader dataReader = new DataReader();
HomezoneWheelDTO HomezoneWheelDTO = dataReader.ReadHomezoneWheelDTO(reader);
homezoneWheelDtoList.Add(HomezoneWheelDTO);
}
IObjectConverter objConverted = new ObjectConverter();
var obj = objConverted.HomezoneWheelJson(homezoneWheelDtoList);
return(obj);
}
}
}
}
public HomezoneWheelDTO ReadHomezoneWheelDTO(NpgsqlDataReader reader)
{
decimal percent = reader.GetDecimal(0);
double distance = reader.GetDouble(1);
int people = reader.GetInt32(2);
HomezoneWheelDTO homezoneWheelDTO = new HomezoneWheelDTO()
{
Percent = percent,
Distance = distance,
People = people
};
return(homezoneWheelDTO);
}
