public IHttpActionResult GetPOIs(POISelection data)
{
List <POI> poi = new List <POI>();
if (!String.IsNullOrEmpty(data.SearchText))
{
string query = $"SELECT name, st_asgeojson(geom) as geom, category FROM buildings As b " +
$"WHERE UPPER(b.name) = UPPER('{data.SearchText}')";
poi.AddRange(db.Database.SqlQuery <POI>(query).ToList());
return(Json(poi));
}
else
{
if (data.AcademicBuildings)
{
poi.AddRange(db.Database.SqlQuery <POI>(@"SELECT id, name, ST_AsGeoJSON(geom) as geom, category FROM buildings b where b.category = 'Academic Building';").ToList());
}
if (data.AdministrativeBuildings)
{
poi.AddRange(db.Database.SqlQuery <POI>(@"SELECT id, name, ST_AsGeoJSON(geom) as geom, category FROM buildings b where b.category = 'Administrative Building';").ToList());
}
if (data.OffCampus)
{
poi.AddRange(db.Database.SqlQuery <POI>(@"SELECT id, name, ST_AsGeoJSON(geom) as geom, category FROM buildings b where b.category = 'Off Campus Apartments';").ToList());
}
if (data.OnCampus)
{
poi.AddRange(db.Database.SqlQuery <POI>(@"SELECT id, name, ST_AsGeoJSON(geom) as geom, category FROM buildings b where b.category = 'On Campus Apartments';").ToList());
}
if (data.Playgrounds)
{
poi.AddRange(db.Database.SqlQuery <POI>(@"SELECT id, name, ST_AsGeoJSON(geom) as geom, category FROM buildings b where b.category = 'Playground';").ToList());
}
if (data.ResidenceHalls)
{
poi.AddRange(db.Database.SqlQuery <POI>(@"SELECT id, name, ST_AsGeoJSON(geom) as geom, category FROM buildings b where b.category = 'Residence Hall';").ToList());
}
return(Json(poi));
}
}
public IHttpActionResult GetNearestPOI(POISelection data)
{
List <POI> nearestPOI = new List <POI>();
string sourcePOIquery = $"SELECT name as name, st_asgeojson(geom) as geom, category as category FROM buildings As b " +
$"WHERE UPPER(b.name) = UPPER('{data.SearchText}')";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(sourcePOIquery).ToList());
if (data.Radius > 0)
{
if (data.AcademicBuildings || data.AdministrativeBuildings || data.OffCampus || data.OnCampus || data.Playgrounds || data.ResidenceHalls)
{
if (data.AcademicBuildings)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Academic Building' " +
$"and ST_DWithin(p1.geom::geography, p2.geom::geography, {data.Radius}*1609.344)";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.AdministrativeBuildings)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Administrative Building' " +
$"and ST_DWithin(p1.geom::geography, p2.geom::geography, {data.Radius}*1609.344)";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.OffCampus)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Off Campus Apartments' " +
$"and ST_DWithin(p1.geom::geography, p2.geom::geography, {data.Radius}*1609.344)";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.OnCampus)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'On Campus Apartments' " +
$"and ST_DWithin(p1.geom::geography, p2.geom::geography, {data.Radius}*1609.344)";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.Playgrounds)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Playground' " +
$"and ST_DWithin(p1.geom::geography, p2.geom::geography, {data.Radius}*1609.344)";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.ResidenceHalls)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Residence Hall' " +
$"and ST_DWithin(p1.geom::geography, p2.geom::geography, {data.Radius}*1609.344)";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
}
else
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name " +
$"and ST_DWithin(p1.geom::geography, p2.geom::geography, {data.Radius}*1609.344)";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
}
else
{
if (data.AcademicBuildings)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Academic Building'" +
$"ORDER BY ST_Distance(p1.geom, p2.geom) " +
$"LIMIT 1";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.AdministrativeBuildings)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Administrative Building'" +
$"ORDER BY ST_Distance(p1.geom, p2.geom) " +
$"LIMIT 1";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.OffCampus)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Off Campus Apartments'" +
$"ORDER BY ST_Distance(p1.geom, p2.geom) " +
$"LIMIT 1";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.OnCampus)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'On Campus Apartments'" +
$"ORDER BY ST_Distance(p1.geom, p2.geom) " +
$"LIMIT 1";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.Playgrounds)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Playground'" +
$"ORDER BY ST_Distance(p1.geom, p2.geom) " +
$"LIMIT 1";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
if (data.ResidenceHalls)
{
string query = $"SELECT p2.name as name, st_asgeojson(p2.geom) as geom, p2.category as category FROM buildings As p1, buildings As p2 " +
$"WHERE UPPER(p1.name) = UPPER('{data.SearchText}') and p1.name <> p2.name and p2.category = 'Residence Hall'" +
$"ORDER BY ST_Distance(p1.geom, p2.geom) " +
$"LIMIT 1";
nearestPOI.AddRange(db.Database.SqlQuery <POI>(query).ToList());
}
}
return(Json(nearestPOI));
}
