Query for loading items in a box

Mar 16, 2011 at 7:42 PM

Hello

I have a table of places with latitude and longitude. I have defined table like this

 

CreateTableDefinition<Place,String>(i => i.Id)
.WithIndex<Place, Double, String>("LatitudeIndex", l => l.Latitude)
.WithIndex<Place, Double, String>("LongitudeIndex", l => l.Longitude)

How could I do a query to search within a box (min/max latitude and min/max longitude). Any help?

Best regards,
Erol

 

Coordinator
Mar 17, 2011 at 12:40 PM

First, I would consolidate the two values into a single index - less overhead for Sterling - like this:

CreateTableDefinition(i => i.Id)
.WithIndex("LatitudeLongitudeIndex", l => l.Latitude, l => l.Longitude)

Then, the query will look like:

public IEnumerable<Place> WithinBounds(Point leftTop, Point bottomRight) 
{
   return from p in _databaseInstance.Query<Place,double,double,string>("LatitudeLongitudeIndex") where p.Item1 <= leftTop.Y && p.Item1 >= bottomRight.Y && p.Item2 >= leftTop.X && p.Item2 <= leftTop.Y select p.LazyValue.Value;
}

Only caveat would be where the box traverses the 0 longitude line, then you'd need to get more specific with handling some overflows, but you should get the general idea there.

 

Mar 17, 2011 at 7:16 PM

I have tried to add WithIndex but it says there is no method which takes 3 arguments..

Coordinator
Mar 17, 2011 at 7:30 PM

Sorry, bad typo on my part. Should be .WithIndex<Place, double, double, string>("LatitudeLongitudeIndex", l=>Tuple.Create(l.Latitude, l.Longitude))

Mar 17, 2011 at 7:36 PM

Thank you for quick reply. Now it works great.

But what should I use for Item1 and Item2 in query? p.LazyValue.Latitude and p.LayValue.Longitude??

Coordinator
Mar 17, 2011 at 8:11 PM

Ugh that's what I get for going from the top of my head (and my email reply may drift in here any second) should be Index1 and Index2 instead of Item1 and Item2.

Mar 17, 2011 at 8:21 PM

Amazing. Thanks for help!

Mar 20, 2011 at 10:31 PM
Edited Mar 20, 2011 at 10:43 PM

Hi,

I will just add to this thread. Is it possible to include additional where condition. My Place class looks like

 

public class Place
{
        public String Id { get; set; }
        public String Name{ get; set; }
        public Double Latitude { get; set; }
        public Double Longitude { get; set; }

        public Bank Bank { get; set; }
}

 

I have a list of Banks with parameter Active (Boolean). Could I query all Places in a "box" AND where Place.Bank.Active == true? Or should I define my Place class differently?

Best regards,
Erol

Edit: I was able to make it work with loading all Keys from Bank and just adding

banks.Contains(place.LazyValue.Value.Bank.Id)

to where condition. It works, but I'm not sure if its the best solution.

Coordinator
Mar 27, 2011 at 6:34 PM

I would expose an index that shows the id of the bank, so then you won't have to load the Place in order to check the existence of the bank. You can easily define an "active" index as Index<Place,bool,string>("BankIsActiveIndex", p=>p.Bank.Active)