Hi, >> We're using MySQL 5.*, so the current lat, lon field in the user table >> could be migrated to a geometry point column to allow bbox queries of >> users. > > Not usefully it couldn't. The users table is an InnoDB table and > you can't have geo indexes on point columns in Inno tables.
Slightly offtopic here but since we're at this... for experimenting, I have created a current_nodes table with an extra "point" column and matching spatial index (after converting it all to MyISAM and issuing a heartbreakingly ugly statement that somehow textually concatenated the existing lat/lon values to feed them into some function expecting WKT). However I somehow fail to be able to use this column in any kind of bounding box query: mysql> set @bbox='polygon(8 50,8 51,9 51,9 50,8 50)'; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from current_nodes where Intersects(pt, GeomFromText(@bbox)); +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) But as you see, there are nodes that should match: mysql> select AsText(pt) from current_nodes limit 10; +------------------------+ | AsText(pt) | +------------------------+ | POINT(8.3024 50.136) | ... I then played around with various functions and found them all wanting: mysql> set @point='point(8.5 50.5)'; Query OK, 0 rows affected (0.00 sec) mysql> select Contains(GeomFromText(@bbox), GeomFromText(@point)); +-----------------------------------------------------+ | Contains(GeomFromText(@bbox), GeomFromText(@point)) | +-----------------------------------------------------+ | NULL | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select @bbox; +-----------------------------------+ | @bbox | +-----------------------------------+ | polygon(8 50,8 51,9 51,9 50,8 50) | +-----------------------------------+ 1 row in set (0.00 sec) mysql> select @point; +-----------------+ | @point | +-----------------+ | point(8.5 50.5) | +-----------------+ 1 row in set (0.00 sec) mysql> select Disjoint(GeomFromText(@bbox), GeomFromText(@point)); +-----------------------------------------------------+ | Disjoint(GeomFromText(@bbox), GeomFromText(@point)) | +-----------------------------------------------------+ | NULL | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select Intersects(GeomFromText(@bbox), GeomFromText(@point)); +-------------------------------------------------------+ | Intersects(GeomFromText(@bbox), GeomFromText(@point)) | +-------------------------------------------------------+ | NULL | +-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select Overlaps(GeomFromText(@bbox), GeomFromText(@point)); +-----------------------------------------------------+ | Overlaps(GeomFromText(@bbox), GeomFromText(@point)) | +-----------------------------------------------------+ | NULL | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select Within(GeomFromText(@bbox), GeomFromText(@point)); +---------------------------------------------------+ | Within(GeomFromText(@bbox), GeomFromText(@point)) | +---------------------------------------------------+ | NULL | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select Contains(GeomFromText(@bbox), GeomFromText(@point)); +-----------------------------------------------------+ | Contains(GeomFromText(@bbox), GeomFromText(@point)) | +-----------------------------------------------------+ | NULL | +-----------------------------------------------------+ 1 row in set (0.00 sec) At least ONE of these should have returned a non-NULL value... I must be doing something wrong at a very elementary level. Can someone tell me what it is? Bye Frederik _______________________________________________ talk mailing list [email protected] http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/talk
