/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesFromDb()
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlSites = "SELECT Stations.st_id, st_name, altitude, location_id FROM Stations WHERE Stations.st_id IN (SELECT st_id FROM StationsVariables WHERE var_id=8 OR var_id=1)";
string sql = "SELECT st.st_id, st_name, lat, lon, altitude FROM " +
"(" + sqlSites + ") st INNER JOIN Locations ON st.location_id = Locations.loc_id";
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["altitude"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["altitude"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["Lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
//si.oid = Convert.ToString(dr["st_id"]);
si.note = new NoteType[1];
si.note[0] = new NoteType();
si.note[0].title = "my note";
si.note[0].type = "custom";
si.note[0].Value = "CZSNW";
si.verticalDatum = "Unknown";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["st_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["st_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
public static SiteInfoType GetSiteFromDb2(string siteId)
{
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
SiteInfoType si = new SiteInfoType();
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlSite = string.Format(Resources.SqlQueries.query_sitebyid2, siteId);
cmd.CommandText = sqlSite;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["Lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["st_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["st_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
}
}
}
return si;
}
/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesByBox(box queryBox, bool includeSeries)
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlSites = "SELECT plaveninycz.Stations.st_id, st_name, altitude, location_id, lat, lon FROM plaveninycz.Stations INNER JOIN StationsVariables stv ON Stations.st_id = stv.st_id " +
"WHERE var_id in (1, 4, 5, 16) AND lat IS NOT NULL";
cmd.CommandText = sqlSites;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["altitude"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["altitude"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["Lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
si.verticalDatum = "Unknown";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["st_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["st_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesFromDb()
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sql = Resources.SqlQueries.query_sites_hourly;
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["altitude"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["altitude"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["Lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
//si.oid = Convert.ToString(dr["st_id"]);
//si.note = new NoteType[1];
//si.note[0] = new NoteType();
//si.note[0].title = "my note";
//si.note[0].type = "custom";
//si.note[0].Value = "CHMI-D";
si.verticalDatum = "MSL";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["st_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["st_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesFromDb()
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sql = "SELECT st_id, st_name, wmo_id, lat, lon, elev, country FROM st " +
"WHERE st.st_id IN (SELECT st_id FROM series WHERE value_count > 1000) ORDER BY country, st_id";
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["elev"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["elev"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
//si.oid = Convert.ToString(dr["st_id"]);
si.note = new NoteType[2];
si.note[0] = new NoteType();
si.note[0].title = "Country";
si.note[0].type = "custom";
si.note[0].Value = dr["country"].ToString();
si.note[1] = new NoteType();
si.note[1].title = "db_id";
si.note[1].type = "custom";
si.note[1].Value = dr["st_id"].ToString();
si.verticalDatum = "Unknown";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["wmo_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["wmo_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
