1

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 units of measure of the @radius? Are km or what?

In this example @radius is setted with the value of 30. But what exactly represent this value?

halfer
  • 19,824
  • 17
  • 99
  • 186
AndreaNobili
  • 40,955
  • 107
  • 324
  • 596

1 Answers1

2

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.

kgeorgiy
  • 1,477
  • 7
  • 9
  • Tnx but I have not full understand what you are saying to me. I know that coordinates are dimensionless because they represent longitude and latitude. My doubt is related to the unit measure of the radius. So radius = 30 means 30m or 30km? The previous query retrieve all the point that have a distance less than 30m from the center point or what? – AndreaNobili Nov 06 '16 at 21:53
  • Initially I considered only Cartesian coordinates. Add a paragraph about another coordinate systems. – kgeorgiy Nov 06 '16 at 22:39
  • @AndreaNobili No, coordinates do not represent longitude and latidude as this would already implify that you use a geographic coordinate system (like WGS84)... the query you made simply doesn't define any coordinate system, so the unit of measurement is irrelevant as long as you take the same for all....if your Point is defined in kartesian coordinates (10 Meter x 10 Meter from a defined zero) your radius is also measured in Metres... if you use longitude,latitude, your radius is measured in degrees. – Jürgen Zornig Nov 07 '16 at 11:47
  • @JürgenZornig ok...but...how can I convert degrees into metres\km? Can I do it? – AndreaNobili Nov 07 '16 at 11:51
  • @AndreaNobili I'm not aware of the spatial data implementation in MySQL (I only know Oracle Spatial, SQL Server and Postgres/PostGIS implementations), but AFAIK MySQL only implements spatial data types (coordinates) and proper spatial indices, but not any other spatial procedures. To work with geographic coordinates and/or do spatial/geodetic operations on it, you'll need some piece of extra Software or switch to another RDBMS (i.e. PostGIS) which has the procedures to do this right at your fingertips. Perhaps you should decide first what you actually want to do and ask another question then – Jürgen Zornig Nov 08 '16 at 12:12
  • to further clarify (hard to put all aspects into 600 characters): MySQL and also your radius search query operate on a planar coordinate System (euclidic distance) rather than a geodetic one (on a spheroid). It just doesn't make much sense to use latitude and longitude coordinates and caclulate euclidic distances between them. For this purpose you need spatially aware (geodetic) functions. I need much more information about the characteristics of your coordinates to tell if it is possible to approximate them just by euclidic distances (like extend of the area, Location, and so on). – Jürgen Zornig Nov 08 '16 at 12:44