Jump to content

Archived

This topic is now archived and is closed to further replies.

sammermpc

Spatial Database, Search with MYSQL Functions?

Recommended Posts

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! :-\

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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().

Share this post


Link to post
Share on other sites
[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].

Share this post


Link to post
Share on other sites
Yeah, LineString() function, that's what I meant; the logic remains the same.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.