MySQL Forums
Forum List  »  GIS

Re: [MySQL 5.0.51a] Making a SELECT over Spatial information
Posted by: Barry Hunter
Date: January 21, 2009 03:42PM

That wont work (as you found!), GeomFromText expects a string, and doesnt resolve column references;

you could probably make it work with

GeomFromText(CONCAT('Point(',e.lat,' ',e.lon,')'))

which makes a string from the references.



However the performace of this is likely to be appalling as it can't use indexes.

You should do something like...

ALTER TABLE `event` ADD `point_ll` point NOT NULL;

UPDATE TABLE `event` SET `point_ll` = GeomFromText(CONCAT('Point(',`lat`,' ',`lon`,')'))

ALTER TABLE `event` ADD SPATIAL KEY(point_ll);

then your query can just do...

MBRContains(GeomFromText('Polygon((40.7 ..... -74.02))'),point_ll))

which should use the index.

(and dont forget to set point_ll when you update/insert into the table ;)

However I am not certain that InnoDB tables can do contain spatial indexes, might require MyISAM.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: [MySQL 5.0.51a] Making a SELECT over Spatial information
3629
January 21, 2009 03:42PM


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.