Spatial Database, Search with MYSQL Functions?

[sammermpc]

Even if you are no familiar with the spatial elements of MySQL, I think you will likely be able to help, as this problem has largely to do with executing functions across tables, not the spatial elements of MySQL per se. 

In any case, here is the situation:

I'm trying to put together a searchable spatial database--as of now, I have a large deployments table which stores data from various marine instruments--each row includes a start coordinate and an end coordinate (GPS).  As of now these are stored as integers, but for the purposes of a spatial search, I transform the two coordinates into a LINESTRING.  I want to check whether this LINESTRING intersects with the search queries input by the user (another LINESTRING, called, say, if the first is LS1, this is LS2).

This can be done quite well with the function MBRIntersects(LS1, LS2), and indeed, this works perfectly on a one to one basis (if I enter in the numbers for LS1 by hand, then LS2, and then run the function).  For example:
[code]
SET @LS1 = GeomFromText('LineString(118.123 36.223, 123.132 33.232)');
SET @LS2 = GeomFromText('LineString(117.233 36.329, 122.423 33.348)');

SELECT MBRIntersects(@LS1, @LS2)
[/code]
This works fine.  Now, considering I have some 2500 entries within the deployments table, how do I automate this for a search function?  I've been trying various combinations of [code]SELECT start_lat, start_long, finish_lat, finish_long FROM deployments...[/code] but I havn't been having any luck.

Once you enter in starting coordinates, I want it to repeat the operations above through the entire deployments table, and return every row where MBRIntersects returns a positive result. 

I think this is trickier than it sounds, thanks for any help--I'm on deadline! :-\

[shoz]

[quote=sammermpc]
Even if you are no familiar with the spatial elements of MySQL, I think you will likely be able to help
[/quote]
I'm not familiar with the topic, but you can try the following

[code]
SELECT
start_lat, start_long, finish_lat, finish_long
FROM
deployments
WHERE
MBRIntersects(
                  GeomFromText(CONCAT('LineString(', start_lat,' ',start_long,', ',finish_lat,' ', finish_long,')')),
                  GeomFromText('LineString($inputStartLat $inputStartLong, $inputFinishLat $inputFinishLong)')
                  ) > 0
[/code]
The > 0 should not be required, but I've still added it.

[sammermpc]

Wow shoz.  Wow.  Thank you so much.  That works perfectly--ridiculously perfectly.  Essentially the difference between what I was doing is the CONCAT statement--how does that somehow make it all possible?  Or is that not the essential part?

Thanks again.

[fenway]

Ultimately, you need to "compose" the parameters to go into the LineString() function and subsequently into the MBRIntersects() function.  There's really no way to do this other than using CONCAT().

[shoz]

[quote=fenway]
Ultimately, you need to "compose" the parameters to go into the LineString() function and subsequently into the
MBRIntersects() function.  There's really no way to do this other than using CONCAT().
[/quote]
The LineString() etc is part of a string which seems to be a way to identify what the values mean rather than a function itself. 'LineString(..)';

[quote=sammermpc]
Essentially the difference between what I was doing is the CONCAT statement--how does that somehow make it all possible?  Or is that not the essential part?
[/quote]
The argument to the GeomFromText function is a string (The "LineString()" etc). The question is how do you create a string with the literal "Linestring( etc" and the column values. I don't know another way except by using [url=http://dev.mysql.com/doc/refman/4.1/en/string-functions.html]CONCAT[/url].

[fenway]

Yeah, LineString() function, that's what I meant; the logic remains the same.