private string srid = "4326"; // name of SRID Constraint, EPSG:4326, of tables
/// <summary>
/// Experimental SQL service
/// </summary>
/// <param name="Parameters">SQLParameter Parameters</param>
/// <returns>Stream</returns>
public Stream GetSQLBBox(SQLParameter Parameters)
{
Encoding encoding = new UTF8Encoding();
Stream WKTStream = new MemoryStream();
if (Parameters.columnnames == null || Parameters.columnnames.Length == 0) Parameters.columnnames = "*";
string connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
SqlDataReader rdr = null;
try
{
StringBuilder query = new StringBuilder("SELECT " + Parameters.columnnames + "," + geomField + ".Reduce(" + Parameters.reduce + ").STAsText() as " + geomField + "wkt FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx))");
if (Parameters.points != null || Parameters.filter != null) query.Append(" WHERE ");
query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON((" + Parameters.points + "))', " + srid + "))=1");
if (Parameters.filter != null && Parameters.filter.Length>0)
{
if (Parameters.points != null) query.Append(" AND ");
query.Append(Parameters.filter);
}
log.Info(query);
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Shape shp = new Shape();
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);
WKTStream.Write(encoding.GetBytes(shp.WKT), 0, shp.WKT.Length);
WKTStream.Flush();
}
}
catch (Exception e)
{
log.Error(e.Message);
}
finally
{
if (rdr != null) rdr.Close();
if (conn != null) conn.Close();
}
return WKTStream;
}
private string srid = "4326"; // name of SRID Constraint, EPSG:4326, of tables
/// <summary>
/// Experimental SQL service
/// </summary>
/// <param name="Parameters">SQLParameter Parameters</param>
/// <returns>Stream</returns>
public Stream GetSQLBBox(SQLParameter Parameters)
{
Encoding encoding = new UTF8Encoding();
Stream WKTStream = new MemoryStream();
if (Parameters.columnnames == null || Parameters.columnnames.Length == 0)
{
Parameters.columnnames = "*";
}
string connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
SqlDataReader rdr = null;
try
{
StringBuilder query = new StringBuilder("SELECT " + Parameters.columnnames + "," + geomField + ".Reduce(" + Parameters.reduce + ").STAsText() as " + geomField + "wkt FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx))");
if (Parameters.points != null || Parameters.filter != null)
{
query.Append(" WHERE ");
}
query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON((" + Parameters.points + "))', " + srid + "))=1");
if (Parameters.filter != null && Parameters.filter.Length > 0)
{
if (Parameters.points != null)
{
query.Append(" AND ");
}
query.Append(Parameters.filter);
}
log.Info(query);
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Shape shp = new Shape();
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);
WKTStream.Write(encoding.GetBytes(shp.WKT), 0, shp.WKT.Length);
WKTStream.Flush();
}
}
catch (Exception e)
{
log.Error(e.Message);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
return(WKTStream);
}
