[OSM-talk] Locating users

[Frederik Ramm]

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