bounding box intersection query

[simod]

Hello list,

I'm trying to implement a bounding box intersection query. By bounding
box I mean that on the database I have the 4 coordinates of a
rectangle and I want to select all the entries that intersect another
bounding box coming from the application.
The query that I need to run is something like this:

abx = ABS(bbox_left + bbox_right - top - right)
aby = ABS(bbox_top + bbox_bottom - top - bottom)

axPby = bbox_right - bbox_left + right - left
ayPby = bbox_top - bbox_bottom + top - bottom

if abx <= axPbx and aby <= ayPby:
return true

I had a look at the Haystack Raw input but seems that it accepts SQL
that makes a selection on an existing field while my need is to select
on a result from a calculation between an existing field and other
parameters passed by the query.

Do you have any suggestion on how to solve it?

Thank you very much!

Simone

[Matt Chaput]

On 22/05/2012 4:27 AM, simod wrote:
> I'm trying to implement a bounding box intersection query. By bounding
> box I mean that on the database I have the 4 coordinates of a
> rectangle and I want to select all the entries that intersect another
> bounding box coming from the application.

Why are you trying to do this with Haystack? Shouldn't you use the
actual database API?

In any case, don't try to run a function on the items in the database,
use range queries to find corners within the target rectangle. This
allows the database to optimize the query. E.g. something like (pseudo SQL):

SELECT * WHERE
(bbox_left >= target_left AND bbox_left <= target_right
AND bbox_top >= target_top AND bbox_bottom <= target_bottom)
OR
...repeat for other three corners...

Matt

[Matt Chaput]

On 22/05/2012 11:44 AM, Matt Chaput wrote:
> AND bbox_top >= target_top AND bbox_bottom <= target_bottom)

Oops, that should be bbox_top <= target_bottom, but you get the idea.

Matt

[simod]

Matt thanks for your prompt reply.

I'm using this query because I'm not using a spatial database backend
and unfortunately I cannot move to it (this is an implementation of
haystack on http://geonode.org/).

The query you suggested is to check whether at least one of the points
is contained into the target_bbox, however could also happen the case
where the bbox is bigger than the target and none of the points are
contained in the target_bbox even if there is full overlap. This is
the main reason (unfortunately) why I was trying to implement the more
complex query which checks the difference between the sum of the
distance of the centers and the sum of the distance of each center
from the respective edge. (I'm not sure that this is possible with a
SQL query....)

Thank you for your help!

Simone

[Matt Chaput]

On 2012-05-23, at 5:57 AM, simod wrote:

> Matt thanks for your prompt reply.
>
> I'm using this query because I'm not using a spatial database backend
> and unfortunately I cannot move to it (this is an implementation of
> haystack on http://geonode.org/).
>
> The query you suggested is to check whether at least one of the points
> is contained into the target_bbox, however could also happen the case
> where the bbox is bigger than the target and none of the points are
> contained in the target_bbox even if there is full overlap.

...
OR (bbox_top < target_top AND bbox_bottom > target_bottom
AND bbox_left < target_left AND bbox_right > target_right)

You can do this in SQL or using Haystack range queries, but it will be much faster than running a function on every row because it allows the db/search engine to use indexes to eliminate rows/docs from consideration.

(Also I don't know how to run a function query in Haystack ;)

Matt

[simod]

Thank you very much!

On May 23, 3:05 pm, Matt Chaput <m...@whoosh.ca> wrote:
> On 2012-05-23, at 5:57 AM, simod wrote:
>
> > Matt thanks for your prompt reply.
>
> > I'm using this query because I'm not using a spatial database backend
> > and unfortunately I cannot move to it (this is an implementation of

> > haystack onhttp://geonode.org/).