AndreaNobili - 2 months ago 13

MySQL Question

I am working on a MySql geographical query.

So, following this tutorial: http://howto-use-mysql-spatial-ext.blogspot.it/2007/11/using-circular-area-selection.html

I have implemented this query that finds all the points having a specific distance from a specified point:

`SET @center = GeomFromText('POINT(10 10)');`

SET @radius = 30;

SET @bbox = CONCAT('POLYGON((',

X(@center) - @radius, ' ', Y(@center) - @radius, ',',

X(@center) + @radius, ' ', Y(@center) - @radius, ',',

X(@center) + @radius, ' ', Y(@center) + @radius, ',',

X(@center) - @radius, ' ', Y(@center) + @radius, ',',

X(@center) - @radius, ' ', Y(@center) - @radius, '))'

);

By this code I am defining the center point and the radious.

Finnally I perform the query that finds all the points having the distance reprensented by the radius from this setted center point:

`SELECT name, AsText(location)`

FROM Points

WHERE Intersects( location, GeomFromText(@bbox) )

AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius;

It seems to works fine.

My doubt is: what is the

In this example

Tnx

Answer

There are no default unit of measure for distances. Actually coordinates in MySQL are are dimensionless. But for the Cartesian coordinates you may pretend, that all distances have the same base unit of measure. It will work properly until all data is interpreted using the same unit.

For example, you may choose meters as the unit distance. In this case, `GeomFromText('POINT(10 10)')`

is 14.14m away from the origin. When you need to provide the radius, just remember, that all distances are measured in meters, so `@radius = 30`

means that radius is 30m.

For other types of the coordinates, the required unit measure will depend on the used formula for distance calculation. For example, if you are using spherical coordinates (longitude and latitude) the distance formula from the Wikipedia, will give you a distance on the unit sphere. So the `@radius`

should be measured in *Earth radii*. Therefore the unit for `@radius`

will be 6371km.