/// <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 SiteInfoResponseTypeSite GetSiteFromDb(string siteId, bool includeSeriesCatalog) { SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite(); newSite.siteInfo = GetSiteFromDb2(siteId); //to add the catalog if (includeSeriesCatalog) { List<int> variableIdList = GetVariablesForSite(Convert.ToInt32(siteId)); int numVariables = variableIdList.Count; newSite.seriesCatalog = new seriesCatalogType[1]; newSite.seriesCatalog[0] = new seriesCatalogType(); newSite.seriesCatalog[0].series = new seriesCatalogTypeSeries[numVariables]; for (int i = 0; i < numVariables; i++) { newSite.seriesCatalog[0].series[i] = GetSeriesCatalogFromDb(Convert.ToInt32(siteId), variableIdList[i]); } } return newSite; }
/// <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[] 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 = "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(); }
public static SiteInfoResponseTypeSite GetSiteFromDb(string siteId, bool includeSeriesCatalog) { WriteLog("Executing GetSiteFromDb(" + siteId + ", " + includeSeriesCatalog + ")"); try { SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite(); newSite.siteInfo = GetSiteFromDb2(siteId); //db site id int dbSiteId = Convert.ToInt32(newSite.siteInfo.note[1].Value); WriteLog("DbSiteID: " + dbSiteId); //to add the catalog if (includeSeriesCatalog) { //first, get the variables for the site string sqlVariables = String.Format("SELECT var_id FROM series WHERE st_id = {0} AND value_count > 1000", dbSiteId); List<int> variableIDs = new List<int>(); string connStr = GetConnectionString(); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sqlVariables, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { int varID = Convert.ToInt32(dr["var_id"]); variableIDs.Add(varID); } conn.Close(); } } int numVariables = variableIDs.Count; WriteLog("numVariables: " + numVariables); newSite.seriesCatalog = new seriesCatalogType[1]; newSite.seriesCatalog[0] = new seriesCatalogType(); newSite.seriesCatalog[0].series = new seriesCatalogTypeSeries[numVariables]; for (int i = 0; i < numVariables; i++) { newSite.seriesCatalog[0].series[i] = GetSeriesCatalogFromDb(dbSiteId, variableIDs[i]); } } return newSite; } catch (Exception ex) { WriteLog("WebServiceUtils.GetSiteFromDb " + ex.Message); return null; } }