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);
                    }
                }
            }
        }
Beispiel #4
0
        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);
                    }
                }
            }
        }