Пример #1
0
        /// <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();
        }
Пример #2
0
        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;
        }
Пример #3
0
        /// <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();
        }
Пример #4
0
        /// <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();
        }
Пример #5
0
        /// <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();
        }