/// <summary> /// Builds a string element for the languages file /// </summary> /// <param name="category">POI Category</param> /// <param name="language">POI Language</param> /// <returns>an XElement with the relevant children</returns> private static XElement BuildStringElement(PointOfInterestCategory category, string language) { return(new XElement( "string", new XAttribute("id", category.Id), new XAttribute("type", 0), new XElement( "lang", new XAttribute("lang", language)), new XElement("text", category.Name))); }
/// <summary> /// Add a POI to the database /// </summary> /// <param name="connection">The Database to update</param> /// <param name="pointOfInterest">The Point of Interest to add</param> /// <param name="category">Cache Category</param> private static void AddPoiToDatabase(SQLiteConnection connection, PointOfInterest pointOfInterest, PointOfInterestCategory category) { using (SQLiteCommand command = new SQLiteCommand("INSERT INTO poiname (name) VALUES (@name)", connection)) { command.Parameters.Add(new SQLiteParameter("name", DbType.String) { Value = pointOfInterest.Name.Replace("'", "''") }); command.ExecuteNonQuery(); command.CommandText = "SELECT last_insert_rowid()"; long poiId = (long)command.ExecuteScalar(); // The other implimentations seem to adjust the cords from the suppled to a value a little bigger and a little smaller, not sure why but lets do the same to avoid issues double offset = 0.000005; command.CommandText = "INSERT INTO poicoord (poiid, latmin, latmax, lonmin, lonmax) VALUES (@poiid, @latmin, @latmax, @lonmin, @lonmax)"; command.Parameters.Add(new SQLiteParameter("poiid", DbType.Int32) { Value = poiId }); command.Parameters.Add(new SQLiteParameter("latmin", DbType.Double) { Value = pointOfInterest.Latitude - offset }); command.Parameters.Add(new SQLiteParameter("latmax", DbType.Double) { Value = pointOfInterest.Latitude + offset }); command.Parameters.Add(new SQLiteParameter("lonmin", DbType.Double) { Value = pointOfInterest.Longitude - offset }); command.Parameters.Add(new SQLiteParameter("lonmax", DbType.Double) { Value = pointOfInterest.Longitude + offset }); command.ExecuteNonQuery(); // Unused fields in the poidata table - ccode, ntlimportance, exttype, extcont, warning, warnphon, namephon, zipcode, phone command.CommandText = "INSERT INTO poidata (poiid, type, namephon, zipcode, city, street, housenr, phone) VALUES (@poiid, @category, '', '', @city, @street, @housenr, @phone)"; command.Parameters.Add(new SQLiteParameter("poiid", DbType.Int32) { Value = poiId }); command.Parameters.Add(new SQLiteParameter("category", DbType.Int32) { Value = category.Id }); command.Parameters.Add(new SQLiteParameter("city", DbType.String) { Value = pointOfInterest.City }); command.Parameters.Add(new SQLiteParameter("street", DbType.String) { Value = pointOfInterest.Street }); command.Parameters.Add(new SQLiteParameter("housenr", DbType.String) { Value = pointOfInterest.HouseNumber }); command.Parameters.Add(new SQLiteParameter("phone", DbType.String) { Value = pointOfInterest.Phone }); command.ExecuteNonQuery(); } }
/// <summary> /// Gets the Points of Interest for a given Category /// </summary> /// <param name="rootPath">Target Path</param> /// <param name="category">Category to search</param> /// <returns>A collection of Points of interest</returns> public static Collection <PointOfInterest> GetPointsOfInterest(string rootPath, PointOfInterestCategory category) { if (category == null) { throw new ArgumentNullException("category"); } Collection <PointOfInterest> pointsOfInterest = new Collection <PointOfInterest>(); string databaseLocation = string.Format(Resources.DataFilePath, rootPath, "poidata.db"); using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", databaseLocation))) { connection.Open(); using (SQLiteCommand command = new SQLiteCommand( "SELECT latmin + (latmax - latmin) / 2 as lat, lonmin + (lonmax - lonmin) / 2 as lon, city, street, housenr, name FROM poicoord JOIN poidata ON poicoord.poiid = poidata.poiid JOIN poiname on poiname.docid = poicoord.poiid WHERE type = @category", connection)) { command.Parameters.Add(new SQLiteParameter("category", DbType.Int32) { Value = category.Id }); using (SQLiteDataReader reader = command.ExecuteReader()) { int latOrdinal = reader.GetOrdinal("lat"); int lonOrdinal = reader.GetOrdinal("lon"); int nameOrdinal = reader.GetOrdinal("name"); int houseNrOrdinal = reader.GetOrdinal("housenr"); int streetOrdinal = reader.GetOrdinal("street"); int cityOrdinal = reader.GetOrdinal("city"); while (reader.Read()) { pointsOfInterest.Add(new PointOfInterest() { Latitude = reader.GetDouble(latOrdinal), Longitude = reader.GetDouble(lonOrdinal), Name = reader.GetString(nameOrdinal), HouseNumber = reader.IsDBNull(houseNrOrdinal) ? string.Empty : reader.GetString(houseNrOrdinal), Street = reader.IsDBNull(streetOrdinal) ? string.Empty : reader.GetString(streetOrdinal), City = reader.IsDBNull(cityOrdinal) ? string.Empty : reader.GetString(cityOrdinal), }); } } } } return(pointsOfInterest); }