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