Esempio n. 1
0
 /// <summary>
 /// ServiceException
 ///     sets response for exception
 /// </summary>
 /// <param name="response">WKTResponse</param>
 /// <param name="message">string exception messsage</param>
 /// <param name="code">int error code</param>
 public void ServiceException(WKTResponse response, string message, int code)
 {
     response.ErrorCode     = code;
     response.OutputMessage = message;
     response.OutputShapes.Clear();
     log.Error(message);
 }
Esempio n. 2
0
        /// <summary>
        /// GetSQLDataWKT
        ///     returns WKT with results of SQL Server query
        /// </summary>
        /// <param name="Parameters">WKTParameters required for query</param>
        public WKTResponse GetSQLDataWKT(WKTParameters Parameters)
        {

            

            DateTime queryStart = DateTime.Now;
            DateTime queryStop;
            int recordCnt = 0;

            WKTResponse _WKTResponse = new WKTResponse();
            _WKTResponse.ErrorCode = 0;
            _WKTResponse.OutputMessage = "Success";
            _WKTResponse.OutputShapes = new List<WKTShape>();
            string connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader rdr = null;
            try
            {
                if (Parameters.querytype == null || Parameters.querytype.Length == 0) throw new ArgumentException("Invalid Parameters: querytype=\""+Parameters.querytype+"\"");
                if (Parameters.querytype.ToLower().Equals("buffer") && Parameters.radius <= 0) throw new ArgumentException("Invalid Parameters: querytype requires radius >0 - \"" + Parameters.radius + "\"");
                if (Parameters.points.Length == 0 && Parameters.querytype.ToLower().Equals("buffer")) throw new ArgumentException("Invalid Parameters: points must contain at least one point for querytype buffer");
                if (Parameters.points.Split(',').Length != 5 && Parameters.querytype.ToLower().Equals("bbox")) throw new ArgumentException("Invalid Parameters: points must contain 5 points for a closed querytype bbox");

                StringBuilder query = new StringBuilder();
                
                if (Parameters.querytype.ToLower().Equals("bbox"))
                {   //BBox
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce).STAsText() as " + geomField + "wkt FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON(('+@points+'))', @srid))=1");
                }
                else if (Parameters.querytype.ToLower().Equals("buffer"))
                {
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce).STAsText() as " + geomField + "wkt FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    if (Parameters.points.Split(',').Length > 1)
                    {   //Polyline Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('LINESTRING('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                    else
                    {   //Point Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('POINT('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                }
                log.Info(query);

                queryStart = DateTime.Now;
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                cmd.Parameters.Add(new SqlParameter("reduce", Parameters.reduce));
                cmd.Parameters.Add(new SqlParameter("srid", srid));
                cmd.Parameters.Add(new SqlParameter("points", Parameters.points));
                cmd.Parameters.Add(new SqlParameter("radius", Parameters.radius));
                conn.Open();
                
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    WKTShape shp = new WKTShape();
                    shp.Fields = new Dictionary<string, string>();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i));
                        if (rdr[i].GetType().Equals(typeof(SqlGeography)) ||
                            rdr[i].GetType().Equals(typeof(SqlGeometry))
                            )
                        { //skip
                            log.Debug("skip " + rdr[i].GetType());
                        }
                        else
                        {
                            if (rdr.GetName(i).Equals("ID"))
                            {
                                shp.ID = rdr[i].ToString();
                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                            }
                            else if (rdr.GetName(i).Equals(geomField + "wkt"))
                            {
                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                                shp.WKT = rdr[i].ToString();
                            }
                            else
                            {
                                shp.Fields.Add(rdr.GetName(i), rdr[i].ToString());
                                log.Debug(rdr.GetName(i) + " : " + rdr[i].ToString());
                            }
                        }
                    }
                    log.Debug(shp.ID + "  " + shp.WKT);
                    if (recordCnt++ > recordLimit) throw new Exception("Query result exceeds limit "+recordLimit);
                    _WKTResponse.OutputShapes.Add(shp);
                }
            }
            catch (ArithmeticException e)
            {
                ServiceException(_WKTResponse,"ArithmeticException "+ e.Message, 3);
            }
            catch (ArgumentException e)
            {
                ServiceException(_WKTResponse, "ArgumentException "+e.Message,1);
            }
            catch (Exception e)
            {
                ServiceException(_WKTResponse, e.Message,2);
            }
            finally
            {
                if (rdr != null) rdr.Close();
                if (conn != null) conn.Close();
            }
            queryStop = DateTime.Now;
            log.Debug(String.Format("Query Time: {0,0:0}ms", (queryStop - queryStart).TotalMilliseconds));
            _WKTResponse.QueryTime = (queryStop - queryStart).TotalMilliseconds;

            return _WKTResponse;
        }
Esempio n. 3
0
 /// <summary>
 /// ServiceException
 ///     sets response for exception
 /// </summary>
 /// <param name="response">WKTResponse</param>
 /// <param name="message">string exception messsage</param>
 /// <param name="code">int error code</param>
 public void ServiceException(WKTResponse response, string message, int code)
 {
     response.ErrorCode = code;
     response.OutputMessage = message;
     response.OutputShapes.Clear();
     log.Error(message);
 }
Esempio n. 4
0
        /// <summary>
        /// GetSQLDataWKT
        ///     returns WKT with results of SQL Server query
        /// </summary>
        /// <param name="Parameters">WKTParameters required for query</param>
        public WKTResponse GetSQLDataWKT(WKTParameters Parameters)
        {
            DateTime queryStart = DateTime.Now;
            DateTime queryStop;
            int      recordCnt = 0;

            WKTResponse _WKTResponse = new WKTResponse();

            _WKTResponse.ErrorCode     = 0;
            _WKTResponse.OutputMessage = "Success";
            _WKTResponse.OutputShapes  = new List <WKTShape>();
            string        connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
            SqlConnection conn    = new SqlConnection(connStr);
            SqlDataReader rdr     = null;

            try
            {
                if (Parameters.querytype == null || Parameters.querytype.Length == 0)
                {
                    throw new ArgumentException("Invalid Parameters: querytype=\"" + Parameters.querytype + "\"");
                }
                if (Parameters.querytype.ToLower().Equals("buffer") && Parameters.radius <= 0)
                {
                    throw new ArgumentException("Invalid Parameters: querytype requires radius >0 - \"" + Parameters.radius + "\"");
                }
                if (Parameters.points.Length == 0 && Parameters.querytype.ToLower().Equals("buffer"))
                {
                    throw new ArgumentException("Invalid Parameters: points must contain at least one point for querytype buffer");
                }
                if (Parameters.points.Split(',').Length != 5 && Parameters.querytype.ToLower().Equals("bbox"))
                {
                    throw new ArgumentException("Invalid Parameters: points must contain 5 points for a closed querytype bbox");
                }

                StringBuilder query = new StringBuilder();

                if (Parameters.querytype.ToLower().Equals("bbox"))
                {   //BBox
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce).STAsText() as " + geomField + "wkt FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON(('+@points+'))', @srid))=1");
                }
                else if (Parameters.querytype.ToLower().Equals("buffer"))
                {
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce).STAsText() as " + geomField + "wkt FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    if (Parameters.points.Split(',').Length > 1)
                    {   //Polyline Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('LINESTRING('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                    else
                    {   //Point Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('POINT('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                }
                log.Info(query);

                queryStart = DateTime.Now;
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                cmd.Parameters.Add(new SqlParameter("reduce", Parameters.reduce));
                cmd.Parameters.Add(new SqlParameter("srid", srid));
                cmd.Parameters.Add(new SqlParameter("points", Parameters.points));
                cmd.Parameters.Add(new SqlParameter("radius", Parameters.radius));
                conn.Open();

                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    WKTShape shp = new WKTShape();
                    shp.Fields = new Dictionary <string, string>();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i));
                        if (rdr[i].GetType().Equals(typeof(SqlGeography)) ||
                            rdr[i].GetType().Equals(typeof(SqlGeometry))
                            )
                        { //skip
                            log.Debug("skip " + rdr[i].GetType());
                        }
                        else
                        {
                            if (rdr.GetName(i).Equals("ID"))
                            {
                                shp.ID = rdr[i].ToString();
                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                            }
                            else if (rdr.GetName(i).Equals(geomField + "wkt"))
                            {
                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                                shp.WKT = rdr[i].ToString();
                            }
                            else
                            {
                                shp.Fields.Add(rdr.GetName(i), rdr[i].ToString());
                                log.Debug(rdr.GetName(i) + " : " + rdr[i].ToString());
                            }
                        }
                    }
                    log.Debug(shp.ID + "  " + shp.WKT);
                    if (recordCnt++ > recordLimit)
                    {
                        throw new Exception("Query result exceeds limit " + recordLimit);
                    }
                    _WKTResponse.OutputShapes.Add(shp);
                }
            }
            catch (ArithmeticException e)
            {
                ServiceException(_WKTResponse, "ArithmeticException " + e.Message, 3);
            }
            catch (ArgumentException e)
            {
                ServiceException(_WKTResponse, "ArgumentException " + e.Message, 1);
            }
            catch (Exception e)
            {
                ServiceException(_WKTResponse, e.Message, 2);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            queryStop = DateTime.Now;
            log.Debug(String.Format("Query Time: {0,0:0}ms", (queryStop - queryStart).TotalMilliseconds));
            _WKTResponse.QueryTime = (queryStop - queryStart).TotalMilliseconds;

            return(_WKTResponse);
        }