/// <summary>
/// 矩形范围查询
/// </summary>
/// <param name="Keywords"></param>
/// <param name="Types"></param>
/// <param name="PageNumber"></param>
/// <param name="PageSize"></param>
/// <param name="Point1"></param>
/// <param name="Point2"></param>
/// <returns></returns>
private static POIResult SearchInBounds
(
string[] Keywords,
string[] Types,
int PageNumber,
int PageSize,
LngLat Point1,
LngLat Point2
)
{
POIResult poiResult = new POIResult();
double x1 = Math.Min(Point1.lng, Point2.lng);
double y1 = Math.Min(Point1.lat, Point2.lat);
double x2 = Math.Max(Point1.lng, Point2.lng);
double y2 = Math.Max(Point1.lat, Point2.lat);
var query = GetBaseQuery(Keywords, Types)
.Where(
p =>
p.CENTERX >= x1 && p.CENTERX <= x2
&&
p.CENTERY >= y1 && p.CENTERY <= y2
);
poiResult.Count = query.Count();
query = OrderAndTakePage(query, PageNumber, PageSize);
poiResult.Results = query.ToList();
return(poiResult);
}
/// <summary>
/// 半径内查询
/// </summary>
/// <param name="Keywords"></param>
/// <param name="Types"></param>
/// <param name="PageNumber"></param>
/// <param name="PageSize"></param>
/// <param name="CenterPoint"></param>
/// <param name="Radius"></param>
/// <returns></returns>
private static POIResult SearchInRadius
(
string[] Keywords,
string[] Types,
int PageNumber,
int PageSize,
LngLat CenterPoint,
double Radius
)
{
POIResult poiResult = new POIResult();
List <object> paras = new List <object>();
string baseSQL = @"select featureguid,fcode,name,shortname,aliasname,address,labelx,labely,centerx,centery,type,phone,website,photo,fscale,stylename,usource,updatetime,updatestatus from poi t where 1=1 ";
string keywordsConditon = string.Empty;
for (int i = 0, l = Keywords.Length; i < l; i++)
{
string kWord = Keywords[i];
keywordsConditon += " and t.name like :t" + i;
paras.Add(new OracleParameter(":t" + i, "%" + kWord + "%"));
}
baseSQL += keywordsConditon;
if (Types != null && Types.Length > 0)
{
string typeCondition = string.Empty;
for (int i = 0, l = Types.Length; i < l; i++)
{
var type = Types[i];
typeCondition += ":t" + i + ",";
paras.Add(new OracleParameter(":t" + i, type));
}
typeCondition = " and t.fcode in (" + typeCondition.Trim(',') + ")";
baseSQL += typeCondition;
}
baseSQL += @" and sdo_within_distance(geometry,sdo_geometry(:point, 4326),:radius) = 'TRUE'";
string sqlCount = string.Format(@"select count(*) from ({0})", baseSQL);
paras.Add(new OracleParameter(":point", string.Format("POINT({0} {1})", CenterPoint.lng, CenterPoint.lat)));
paras.Add(new OracleParameter(":radius", string.Format("distance={0} unit=m", Radius)));
var count = _context.Database.SqlQuery <int>(sqlCount, paras.ToArray());
poiResult.Count = count.ToList().First();
baseSQL += " order by fscale asc";
string sqlList = string.Format(@"select * from (select t.*,rownum rn from ({0})t where rownum<=:endCount) where rn>:beginCount", baseSQL);
paras.Add(new OracleParameter(":endCount", (PageNumber + 1) * PageSize));
paras.Add(new OracleParameter(":beginCount", PageNumber * PageSize));
var results = _context.Database.SqlQuery <POI>(sqlList, paras.ToArray());
poiResult.Results = results.ToList();
return(poiResult);
}
/// <summary>
/// 关键字查询
/// </summary>
/// <param name="Keywords"></param>
/// <param name="Types"></param>
/// <param name="PageNumber"></param>
/// <param name="PageSize"></param>
/// <returns></returns>
private static POIResult SearchByKeywords
(
string[] Keywords,
string[] Types,
int PageNumber,
int PageSize
)
{
POIResult poiResult = new POIResult();
var query = GetBaseQuery(Keywords, Types);
poiResult.Count = query.Count();
query = OrderAndTakePage(query, PageNumber, PageSize);
poiResult.Results = query.ToList();
return(poiResult);
}
public ActionResult SearchPOI(POISearchParameters SearchParameters)
{
ReturnObject2 ro = null;
try
{
POIResult poiResult = POISearch.Search(SearchParameters);
ro = new ReturnObject2();
ro.AddData("Data", poiResult);
}
catch (Exception ex)
{
ro = new ReturnObject2(ex.Message);
}
return(Json(ro));
}
