MySQL Forums
Forum List  »  GIS

[MySQL 5.0.51a] Making a SELECT over Spatial information
Posted by: Matteo De Martino
Date: January 21, 2009 02:37PM

Hi everybody,
I have a table with the following structure:

CREATE TABLE IF NOT EXISTS `progettoMB`.`event` (
`id` INT NOT NULL AUTO_INCREMENT ,
`titolo` VARCHAR(45) NOT NULL ,
`descrizione` VARCHAR(255) NULL ,
`indirizzo` VARCHAR(100) NULL ,
`lat` DOUBLE(10,7) NOT NULL ,
`lon` DOUBLE(10,7) NOT NULL ,
`categoria` INT NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX cat (`categoria` ASC) ,
CONSTRAINT `cat`
FOREIGN KEY (`categoria` )
REFERENCES `progettoMB`.`categoria` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB

Lat and Long clearly are coordinates of the event and, from a PHP script I have to make a query which select only those event in a particular area.
I tried with something like this:

SELECT e.*, c.descrizione FROM event e, categoria c where c.id = e.categoria and MBRContains(GeomFromText('Polygon((40.7 -74.02, 40.7 -73.93, 40.724999 -73.93, 40.724999 -74.02,40.7 -74.02))'),GeomFromText('Point(e.lat e.lon)'))=1;

...but it doesn't work. It returns nothing, even if I know there are events in that area. I gues the problem is in "GeomFromText('Point(e.lat e.lon)')" becuase I tried even to put a fixed number and it works properly...
I need to select those entry which are contained in an area...does anyone have any idea how can I do it?
I hope I explained myself properly.....
Thank you VERY much!!
Bye

Options: ReplyQuote


Subject
Views
Written By
Posted
[MySQL 5.0.51a] Making a SELECT over Spatial information
5876
January 21, 2009 02:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.