Querying a MySQL database for "lines" that have specified end and start points

[yanjchan]

Hi!

I wasn't able to fit the whole question in the title, because it is pretty confusing. (to me - hopefully, not to you guys.  )

 

Pretend that there are a bunch of "lines" stored in a database as having starting and ending point coordinates.

 

For example,

 

ID / startx / starty / endx / endy

0 / 1 / 1 / 10 / 10

 

However, the screen only shows an are which extends from 2, 2 to 7, 7.

 

I could run calculations on every single row in the "line" database and determine which of those intersect with the viewport area, but that would take a long time, yes?

 

My question is whether there is an efficient and practical way to accomplish this task.

 

Thanks in advance!

[ialsoagree]

Do the rows in the database have to be fully within the viewing area to be returned, or are they returned if any part of their area should be displayed?

 

For the former (only show objects fully within the area), and assuming your columns are ints:

 

$query = "SELECT * FROM coordinates_table WHERE startx <= $startx AND starty <= $starty AND endx >= $endx AND endy >= $endy";

To do the 2nd option, show every object that at least partially appears in the viewing area, then change the AND's to OR's (you may also want to remove the ='s from all the segments. This prevents a row that appears on the edge of the view or on the corner of the view - in other words, sharing a side but no area - from being displayed).

 

Edit: few corrections to the SQL.

[yanjchan]

Hi!

 

Thanks for replying.

 

Unfortunately, it has to display lines that may only be partly in the display.

 

Do you know of a method to do this?

 

Thanks!

 

[ialsoagree]

As suggested above, use the same SQL using OR's instead of AND's. You'll also want to remove the ='s, unless you want rows that share a border (but no area) to be returned too.

[yanjchan]

No, I meant objects whose starting and ending coordinates are completely off the display.

 

Unless I misread your SQL, either the starting or ending coordinate has to be on the display.

 

Thanks for the continued help!

[ialsoagree]

Alright, now I'm a bit confused as to what you're asking. You say starting an ending points, as in, making a rectangle correct? And you want anything that is at least partly inside that rectangle to display, correct? And the rows you're looking for are themselves rectangles, correct?

 

If so, you need to change the AND's to OR's and remove the = signs (otherwise, rows that simply share a border, but whose area isn't actually in the display will also get pulled).

 

If not, you need to more clearly explain the problem.

[yanjchan]

Oh, sorry.

The rows in the database are in fact lines.

 

The situation I am trying to account for is something like this:

 

 

Thanks!