Hi,
I'm trying to do something like this:
select *
from "Thing"
where ST_Intersects("LocationGeom",((E'POLYGON((74.6036376953125 20.113309860229492,74.6036376953125 20.14579963684082,74.612640380859375 20.14579963684082,74.612640380859375 20.113309860229492,74.6036376953125 20.113309860229492))'))) = TRUE
SORT BY
ST_Distance("LocationGeom", ST_GeomFromText('POINT(51 0)', -1)) asc
so basically using postgres do a select of a number of things within a boundary and then sort them by distance from a point. I need to do this so I can filter the top X closest results.
This is the code (which doesn't work):
EntityCollection<ThingEntity> things = new EntityCollection<ThingEntity>();
DbFunctionCall ST_Intersects = new DbFunctionCall("ST_Intersects({0},{1})", new object[] { ThingFields.LocationGeom, geom.AsText() });
EntityField2 field = ThingFields.LocationGeom.SetExpression(ST_Intersects);
IPredicateExpression SpatialFilter = new PredicateExpression(field == true);
Filter.Add(SpatialFilter);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(Filter);
if (relations != null)
foreach (IRelation relation in relations) bucket.Relations.Add(relation);
IPrefetchPath2 prefetchPath = new PrefetchPath2(ThngyData.EntityType.ThingEntity);
prefetchPath.Add(ThingEntity.PrefetchPathAttributeList);
DbFunctionCall ST_Distance = new DbFunctionCall("ST_Distance({0},{1})", new object[] { ThingFields.LocationGeom, geom.AsText() });
EntityField2 distancefield = ThingFields.LocationGeom.SetExpression(ST_Intersects);
SortClause sc = new SortClause(distancefield, null, SortOperator.Ascending);
SortExpression sorter = new SortExpression(sc);
context.DataAdapter.FetchEntityCollection(things, bucket, -1, sorter, prefetchPath, Page, NumRows);
return things;
I assume the problem is that I'm trying to do a LocationGeom.SetExpression twice - once for select and once for the sort.
Is there any way I can do what I'm trying to do? I'm completely stumped at the moment.
Thanks
Paul
