Jump to content

Spatial Database, Search with MYSQL Functions?


sammermpc

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! :-\
Link to comment
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.
Link to comment
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.
Link to comment
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].
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.