private void GetGeo(SqlDataReader dr, SqlConnection conn, string status)
{
Geo geo = new Geo()
{
ID = (int)dr["GeoID"], Title = dr["Title"].ToString(), Intro = dr["Intro"].ToString()
};
LatLng ll = LatLng.FromHACoord(new HACoord((int)dr["GeoX"], (int)dr["GeoY"]));
geo.Latitude = ll.latitude;
geo.Longitude = ll.longitude;
geo.Url = "http://historiskatlas.dk/" + geo.Title.Replace(' ', '_') + "_(" + geo.ID + ")";
geo.status = status;
using (SqlDataReader drImage = new SqlCommand("SELECT Image.ImageID, Text FROM Geo_Image, Image WHERE Geo_Image.ImageID = Image.ImageID AND GeoID = " + geo.ID, conn).ExecuteReader())
if (drImage.Read())
{
geo.Image = new Image()
{
Url = "http://service.historiskatlas.dk/image/" + (int)drImage["ImageID"], Text = drImage["Text"].ToString()
}
}
; //Year = imageInfos[0].Year == 0 ? (int?)null : imageInfos[0].Year };
using (SqlDataReader drTag = new SqlCommand("SELECT TagID FROM Tag_Geo WHERE GeoID = " + geo.ID, conn).ExecuteReader())
{
while (drTag.Read())
{
int tagID = (int)drTag["TagID"];
if (subjects.ContainsKey(tagID))
{
geo.Subjects.Add(subjects[tagID]);
}
if (periods.ContainsKey(tagID))
{
geo.Periods.Add(periods[tagID]);
}
}
}
biggestGeoID = Math.Max(biggestGeoID, geo.ID);
geos.Add(geo);
}
private void Clustering()
{
List <Cluster> clusters = new List <Cluster>();
while (geos.Count > 0)
{
Geo geo = geos[0];
geos.RemoveAt(0);
Cluster closesetCluster = null;
double closesetDistance = double.MaxValue;
foreach (Cluster cluster in clusters)
{
double distance = cluster.latLng.PixelDistance(new LatLng(geo.lat, geo.lng), z);
if (distance < closesetDistance)
{
closesetCluster = cluster;
closesetDistance = distance;
}
}
if (closesetDistance > 20)
{
Cluster cluster = new Cluster();
cluster.latLng = new LatLng(geo.lat, geo.lng);
clusters.Add(cluster);
cluster.Add(geo);
}
else
{
closesetCluster.Add(geo);
closesetCluster.latLng = new LatLng(0, 0);
foreach (Geo cGeo in closesetCluster)
{
closesetCluster.latLng += new LatLng(cGeo.lat, cGeo.lng);
}
closesetCluster.latLng /= closesetCluster.Count;
}
}
foreach (Cluster cluster in clusters)
{
if (cluster.Count == 1)
{
geos.Add(cluster[0]);
}
else
{
Geo geo = new Geo()
{
id = -cluster.Count
};
LatLng cLatLng = new LatLng(0, 0);
foreach (Geo cGeo in cluster)
{
cLatLng += new LatLng(cGeo.lat, cGeo.lng);
foreach (int tagid in cGeo.tagids)
{
if (!geo.tagids.Contains(tagid))
{
geo.tagids.Add(tagid);
}
}
}
cLatLng /= cluster.Count;
geo.lat = cLatLng.latitude;
geo.lng = cLatLng.longitude;
geos.Add(geo);
}
}
}
private void GetHAGeos(HttpContext context)
{
bool alsooffline = false;
if (context.Request.Params["alsooffline"] != null)
{
bool.TryParse(context.Request.Params["alsooffline"], out alsooffline);
}
int tagscategory = -1; //all
if (context.Request.Params["tagscategory"] != null)
{
Int32.TryParse(context.Request.Params["tagscategory"], out tagscategory);
}
string sqlTagSearch = "";
if (!string.IsNullOrEmpty(context.Request.Params["includetags"]))
{
List <string> includeTags = new List <string>(context.Request.Params["includetags"].Split(new char[] { ',' }));
sqlTagSearch = " AND GeoID IN (SELECT GeoID FROM Tag_Geo WHERE TagID IN (" + string.Join(",", includeTags) + "))";
}
string sqlOrderBy = "";
if (center != null && llBox == null)
{
sqlOrderBy = " ORDER BY POWER((Latitude - " + center.latitude + "), 2) + POWER((Longitude - " + center.longitude + "), 2)"; //Untested
}
using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["hadb5"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT" + (count > -1 ? " TOP " + count : "") + " GeoID, Title, Latitude, Longitude FROM Geo WHERE " + (alsooffline ? "Online IS NOT NULL" : "Online = 1") + (llBox == null ? "" : " AND Latitude > " + llBox.llLatLng.latitude + " AND Longitude < " + llBox.urLatLng.latitude + " AND Latitude > " + llBox.urLatLng.latitude + " AND Longitude < " + llBox.llLatLng.longitude) + sqlTagSearch + sqlOrderBy, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
LatLng ll = new LatLng((decimal)dr["Latitude"], (decimal)dr["Longitude"]);
Geo geo = new Geo()
{
id = (int)dr["GeoID"], title = dr["Title"].ToString(), lat = ll.latitude, lng = ll.longitude
};
SqlCommand cmdTags;
if (tagscategory == -1)
{
cmdTags = new SqlCommand("SELECT TagID FROM Tag_Geo WHERE GeoID = " + geo.id, conn);
}
else
{
cmdTags = new SqlCommand("SELECT Tag_Geo.TagID FROM Tag_Geo, Tag WHERE Tag_Geo.TagID = Tag.TagID AND Tag.Category = " + tagscategory + " AND GeoID = " + geo.id, conn);
}
using (SqlDataReader drTags = cmdTags.ExecuteReader())
while (drTags.Read())
{
geo.tagids.Add((int)drTags["TagID"]);
}
geos.Add(geo);
}
}
}
}
private void GetHAGeos(HttpContext context, bool needDistance)
{
int tagscategory = -1; //all
if (context.Request.Params["tagscategory"] != null)
{
Int32.TryParse(context.Request.Params["tagscategory"], out tagscategory);
}
string sqlTagSearch = "";
if (!string.IsNullOrEmpty(context.Request.Params["includetags"]))
{
List <string> includeTags = new List <string>(context.Request.Params["includetags"].Split(new char[] { ',' }));
sqlTagSearch = " AND GeoID IN (SELECT GeoID FROM Tag_Geo WHERE TagID IN (" + string.Join(",", includeTags) + "))";
}
string sqlOrderBy = "";
if (center != null && llBox == null)
{
HACoord coord = HACoord.FromLatLng(center);
sqlOrderBy = " ORDER BY POWER((GeoX - " + coord.x + ") / 10000.0, 2) + POWER((GeoY - " + coord.y + ") / 10000.0, 2)";
}
using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["hadb"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT" + (count > -1 ? " TOP " + count : "") + " GeoID, Title, GeoX, GeoY FROM Geo WHERE Online = 1" + (llBox == null ? "" : " AND GeoX > " + HACoord.FromLatLng(llBox.llLatLng).x + " AND GeoX < " + HACoord.FromLatLng(llBox.urLatLng).x + " AND GeoY > " + HACoord.FromLatLng(llBox.urLatLng).y + " AND GeoY < " + HACoord.FromLatLng(llBox.llLatLng).y) + sqlTagSearch + sqlOrderBy, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
LatLng ll = LatLng.FromHACoord(new HACoord((int)dr["GeoX"], (int)dr["GeoY"]));
Geo geo = new Geo()
{
id = (int)dr["GeoID"], title = dr["Title"].ToString(), lat = ll.latitude, lng = ll.longitude
};
if (center != null && needDistance)
{
geo.distFromCenter = ll.Distance(center);
}
SqlCommand cmdTags;
if (tagscategory == -1)
{
cmdTags = new SqlCommand("SELECT TagID FROM Tag_Geo WHERE GeoID = " + geo.id, conn);
}
else
{
cmdTags = new SqlCommand("SELECT Tag_Geo.TagID FROM Tag_Geo, Tag WHERE Tag_Geo.TagID = Tag.TagID AND Tag.Category = " + tagscategory + " AND GeoID = " + geo.id, conn);
}
using (SqlDataReader drTags = cmdTags.ExecuteReader())
while (drTags.Read())
{
geo.tagids.Add((int)drTags["TagID"]);
}
geos.Add(geo);
}
}
}
}