Mysql – Find points within a distance using MySQL

MySQLspatial

I have a MySQL table

Users(State, City, Zip, Lat, Long)

I would like to get a list of user who are inside the circle or square of a given latitude and longitude with given distance. For example my input Lat= 78.3232 and Long = 65.3234 and distance = 5 kilometers. I would like to get the list of users who are inside 5 kilometers distance from the point 78.3232 and 65.3234. Is it possible to solve this with single query?

Best Answer

Using GIS

Assuming MySQL 8+

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326), 
    ST_SRID(Point(u.lon,u.lat),4326)
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000), 
  ST_SRID(Point(u.lon,u.lat),4326)
);

If you're not using MySQL 8+ you can't use ST_SRID(geom,SRID). Also, if you store GEOMETERY types on the table, rather than silly lat and long columns, this looks a lot cleaner.

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326), 
    u.geom
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000), 
  u.geom
);

Related Solutions

MySQL Syntax – Searching Points Within Polygons Using GeomFromText

I may be wrong, but I believe that MBRWithin(GeomFromText(geo),@zone), should be MBRWithin(geo, @zone) -no need to convert anything, it is stored already in spatial format.

When functions like these get bad-type operands, they return null.

Mysql – How to compare all rows in a table and extract data using triggers

Supposing id is primary_key and auto increment column for both t1 and t2, I think this is what you need:

CREATE TRIGGER trig1
AFTER INSERT ON t1
FOR EACH ROW 
BEGIN
  IF @latitude = NEW.latitude AND @longitude = NEW.longitude THEN
     SET @end_time = NEW.received_time;
     SET @iddle_time = TIMEDIFF(NEW.received_time, @start_time);
     UPDATE t2 SET iddle_time = @iddle_time, end_time = @end_time
               WHERE id = @last_id_t2;
  ELSE

     INSERT INTO t2 VALUES (NULL, NEW.uid, NEW.latitude, NEW.longitude, 
                            NULL, NEW.imno, NEW.received_time, NEW.received_time);       
     SET @last_id_t2 = LAST_INSERT_ID();
     SET @latitude = NEW.latitude; SET @longitude = NEW.longitude;
     SET @iddle_time = NULL;
     SET @start_time = NEW.received_time; SET @end_time = NEW.received_time;
  END IF;
END;

You can see the whole scheme here : http://sqlfiddle.com/#!2/a70ac/1

Best Answer

Using GIS

Related Solutions

MySQL Syntax – Searching Points Within Polygons Using GeomFromText

Mysql – How to compare all rows in a table and extract data using triggers

Related Question