Search code examples
mysqlsqlpostgresqlgeolocationpostgis

Get logs within a given radius with MySql or Postgres

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

Solution

  • 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

    enter image description here

    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 ;)

    Further reading: