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