Paul Ramsey

Open source software developer and information technology professional.
Occasional blowhard.

MySQL Snark

12 Jun 2009

OK, this one I have to share. Here’s two queries, the first with a syntax error in the WKT (oops!) and the second one correct.

First, as processed by MySQL:

mysql> select count(*) from tiger_roads_texas 
  where mbrintersects(geom, 
    GeomFromText('LINESTRING(452284 -1651542, 452484 -1651342'));
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from tiger_roads_texas 
  where mbrintersects(geom, 
    GeomFromText('LINESTRING(452284 -1651542, 452484 -1651342)'));
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.06 sec)

Now as processed by PostGIS:

tiger=# select count(*) from tiger_roads_texas 
where geom && 
  GeomFromText('LINESTRING(452284 -1651542, 452484 -1651342',2163);
ERROR:  parse error - invalid geometry
HINT:  "...RING(452284 -1651542, 452484 -1651342" <-- parse error at position 43 within geometry
CONTEXT:  SQL function "geomfromtext" statement 1

tiger=# select count(*) from tiger_roads_texas 
  where geom && 
    GeomFromText('LINESTRING(452284 -1651542, 452484 -1651342)',2163);
 count 
-------
     1
(1 row)

Can you spot the difference? Snark! Another one for the list.

Recent Posts