[MySQL 5.0.51a] Making a SELECT over Spatial information
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) ,
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!!