Spatial Index and ORDER BY on GeomFromText ....

[Mariella Di Giacomo]

Hi All,

I have been running MySQL 5.1.19 on MacPro Intel Xeon and I have been playing a little bit with spatial indexes.
The reason why I am using spatial indexes is the following:

I need to calculate all the people that are located inside a specified polygon (The polygon values are generated based on a given lat1/long1 value and adding/sub (+-) a predefined distance.

E.g. The SQL query would look like
SELECT id, AsText(g) FROM spatial_coord WHERE MBRContains(GeomFromText('Polygon((34.13 -118.66 , 34.23 -118.66, 34.23 -118.56, 34.13 -118.56, 34.13 -118.66))'),g);

Question:

If I use the modifier
.... ORDER BY GeomFromText('Polygon((34.13 -118.66 , 34.23 -118.69, 34.23 -118.56, 34.13 -118.59, 34.13 -118.69))') ASC LIMIT 30
is there any way to get results ordered, so that first 30 values returned (coordinates values, e.g. POINT(34.20111 -118.59722), ....) are those that would generate the smallest polygons inside the original polygon (root polygon)
Polygon((34.13 -118.66 , 34.23 -118.66, 34.23 -118.56, 34.13 -118.56, 34.13 -118.66)) ?

E.g.
SELECT id, AsText(g) FROM spatial_coord WHERE MBRContains(GeomFromText('Polygon((34.13 -118.66 , 34.23 -118.66, 34.23 -118.56, 34.13 -118.56, 34.13 -118.66))'),g) ORDER BY GeomFromText('Polygon((34.13 -118.66 , 34.23 -118.69, 34.23 -118.56, 34.13 -118.59, 34.13 -118.69))') ASC LIMIT 30

Would that do what I would like to achieve ?
If that is not possible or I am mistaken something, could you please help ?


Thanks in advance for your help,

Mariella