0

I am not so into database and I am working on a MySql geographical query that have to find all the points of interest that are into a specified distance range from a center point.

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

So I have this table:

create table Points (
    name VARCHAR(20) PRIMARY KEY,
    location Point NOT NULL,
    description VARCHAR(200),
    SPATIAL INDEX(location)
);

where my points of interest are stored. The Point field contains the geographical coordinates.

Then I have this query that should find all the points that are into a distance range (represented by the @radious value) from a center 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 of interest that have distance (from the center point) less than the defined radius:

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;

My doubt is: what exactly is the unit of measurement of the @radius value?

I thought that it could be km but doing some test it seems to me that it couldn't be because setting a very litle value of @radius I find point that are very distant from the setted center.

So I think that the unit of measurement have to be something else. Is it my reasoning correct or is it this query wrong?

It the query is correct what exactly is the unit of measurement used for @radius? How can I convert it in km? (the user insert the radius value in km and should be converted into the correct value used by this query)

AndreaNobili
  • 40,955
  • 107
  • 324
  • 596
  • Radius is in degrees with one degree approximately equal to 111km. But you are following a horribly outdated tutorial – e4c5 Nov 07 '16 at 11:04
  • ST_Distance can replace much of your hand written code – e4c5 Nov 07 '16 at 11:05
  • @e4c5 1) So do you mean that if a divide the value of radius for 111 value I will obtain the correct km value associated to this radius? 2) Why this tutorial is so horrible? Can you suggest me some better way to implement this query in MySql (from what I know MySql not yet implement the distance function to calculate the distance between 2 points). What could be a smarter solutin? Tnx so much – AndreaNobili Nov 07 '16 at 11:07
  • 2
    as mentioned in my previous comment it has it and it's called ST_Distance – e4c5 Nov 07 '16 at 11:11
  • @e4c5 Ok, now I try to study it. A last information: it return me the value un km? – AndreaNobili Nov 07 '16 at 11:30

0 Answers0