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); } } } }