Jump to content


Photo

Spatial Database, Search with MYSQL Functions?


  • Please log in to reply
5 replies to this topic

#1 sammermpc

sammermpc
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 24 July 2006 - 11:12 PM

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

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 July 2006 - 01:58 AM

Even if you are no familiar with the spatial elements of MySQL, I think you will likely be able to help

I'm not familiar with the topic, but you can try the following

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
The > 0 should not be required, but I've still added it.

#3 sammermpc

sammermpc
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 25 July 2006 - 05:47 PM

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.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 July 2006 - 06:54 PM

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().
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 July 2006 - 11:58 PM

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().

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(..)';

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?

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 CONCAT.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 July 2006 - 01:39 AM

Yeah, LineString() function, that's what I meant; the logic remains the same.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users