Get logs within a given radius with MySql or Postgres

Question

I have the following table, where the latitude and longitude fields specify a point on the map.

|id   |latitud         |longitud         |
| --- | ------ --------| ---------------- | 
| 001 |19.4400570537131|-99.1270470974249 | 
| 002 |19.437904276995 |-99.1286576775023 |
| 003 |19.4360705910348|-99.1297865731994 |
| 001 |19.4424869116657|-99.1238332599196 |

I need to make a query that returns me the number of records that are around the point (19.4400570537131, -99.1270470974249) in a radius of 1000 meters.

If it is not possible with MySQL, I can use PostgreSQL

[st_distance_sphere](https://dev.mysql.com/doc/refman/8.0/en/spatial-convenience-functions.html) — danblack, Mar 26 '21 at 10:53
What are the units of your latitud (194400570537131) and longitud (-991270470974249 ) values? Please [edit] your question. They don't look like decimal degrees or some sort of UTM coordinate system. In the meantime [here is a writeup](https://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/) on the general problem. — O. Jones, Mar 26 '21 at 11:12
I had omitted the comma. Thank you very much for the appreciation — Matias Lucero, Mar 26 '21 at 11:24
It seems that this question has already been answered here: https://stackoverflow.com/questions/1727137/sql-query-for-performing-radius-search-based-on-latitude-longitude; and here: https://gis.stackexchange.com/questions/31628/find-features-within-given-coordinates-and-distance-using-mysql — Nike, Mar 26 '21 at 13:13

Jim Jones · Accepted Answer · 2021-03-27T19:41:03.813

In PostgreSQL (with PostGIS) the function you're looking for is called ST_DWithin. To use it with metres you either have to ST_Transform your coordinates to a SRS that has metre as unit or use geography instead of geometry. The example below creates a point with ST_MakePoint in query time, cast it to geography and applies the filter with ST_DWithin and the point mentioned in your question within a radius of 1000 metres.

WITH j (id,lat,lon) AS ( VALUES
  (001,19.4400570537131,-99.1270470974249),
  (002,19.437904276995 ,-99.1286576775023),
  (003,19.4360705910348,-99.1297865731994),
  (001,19.4424869116657,-99.1238332599196)
) 
SELECT 
  id,
  ST_Distance(
    ST_MakePoint(lon,lat)::geography,
    ST_MakePoint(-99.1270470974249,19.4400570537131)::geography) AS distance,
  ST_MakePoint(lon,lat)::geography AS geom
FROM j 
WHERE ST_DWithin(
        ST_MakePoint(lon,lat)::geography,
        ST_MakePoint(-99.1270470974249,19.4400570537131)::geography,1000);

 id |   distance   |                        geom                        
----+--------------+----------------------------------------------------
  1 |            0 | 0101000020E6100000781F268A21C858C067123E94A7703340
  2 | 292.22521599 | 0101000020E61000001C5069ED3BC858C0D878A47E1A703340
  3 |   526.781174 | 0101000020E61000007CD6576C4EC858C0EA3D7F52A26F3340
  1 |  431.5655003 | 0101000020E6100000C16056E2ECC758C021837ED246713340

Note: I strongly suggest you to store these points in a geometry or geography column, and to properly index them. Creating geometries out of separated latitude and longitude values in query time creates an unnecessary overhead, and it might slow down your queries significantly. Also, in case you're not working in the microscopy realm, consider reducing the precision of your points ;)

Get logs within a given radius with MySql or Postgres

1 Answers1