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