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:
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)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
SELECT start_lat, start_long, finish_lat, finish_long FROM deployments...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! :-\