Exemple #1
0
        private CityInformation ReadCity(OsmPoint point, string placeTag = null)
        {
            OpenConnection();
            var str = @"SELECT TOP(1) Id, Name, Place, Latitude, Longitude, location.STDistance(geography::STPointFromText('POINT(12.100067 47.772099)',4326))  as Distance
						from Info.Cities WITH(INDEX(idxCities)) 
						WHERE location.STDistance(geography::STPointFromText('{0}',4326)) < 100000 {1} 
						order by location.STDistance(geography::STPointFromText('{0}',4326)) "                        ;

            if (!string.IsNullOrEmpty(placeTag))
            {
                placeTag = "and Place = '" + placeTag + "'";
            }
            _command.CommandText = string.Format(str, GeneratePointString(point), placeTag);
            using (var reader = _command.ExecuteReader())
            {
                if (reader.Read())
                {
                    var info = new CityInformation
                    {
                        RelationId = reader.GetInt64(0),
                        Name       = reader[1] as string,
                        Place      = reader[2] as string,
                        Location   = new OsmPoint {
                            Latitude  = (double)reader[3],
                            Longitude = (double)reader[4]
                        },
                        Distance = (double)reader[5]
                    };
                    return(info);
                }
                return(null);
            }
        }
Exemple #2
0
        /// <summary>
        /// Determines the exact city of a specified OsmPoint and all administrative Borders around it
        /// (e.g.: Town: Schäftlarn (Level 8) and Regierungsbezirk Oberbayern (Level 5)
        /// http://wiki.openstreetmap.org/wiki/Key:admin_level#admin_level</param>
        /// </summary>
        /// <param name="point">A geopoint with Latitude/Longitude</param>
        /// <param name="adminLevel">If you want to specifie a adminlevel</param>
        /// <returns>List of cities - take care of the admin level</returns>
        public List <CityInformation> GetCityInformation(OsmPoint point, int?adminLevel = null)
        {
            OpenConnection();

            var str = @"SELECT RelationId,Name,PostalCode,Place, AdminLevel
						FROM info.AdminLevels WITH(nolock, index(idx_AdminLevelsSpatial)) 
						WHERE geo.STIntersects(geography::STPointFromText('{0}',4326)) = 1"                        ;

            if (adminLevel.HasValue)
            {
                str += "and AdminLevel = " + adminLevel.Value;
            }
            else
            {
                str += " and  AdminLevel between 4 and 10";
            }

            _command.CommandText = string.Format(str, GeneratePointString(point));
            using (var reader = _command.ExecuteReader())
            {
                var res = new List <CityInformation>();
                while (reader.Read())
                {
                    var info = new CityInformation
                    {
                        RelationId = reader.GetInt64(0),
                        Name       = reader[1] as string,
                        PostalCode = reader[2] as string,
                        Place      = reader[3] as string,
                        AdminLevel = reader.GetInt32(4)
                    };
                    res.Add(info);
                }
                return(res);
            }
        }