[GIS] PostGIS distance between multiple points

distancepostgis

I have a data set in a PostGIS data base (table name is animals) with the following structure:

id (int, pk) | latitude (double) | longitude (double) | type (char) | geom (srid: 4326)

In the type-column there are currently values of 1 and 2, where 1 stands for, let's say, lions and 2 for zebras.

I've managed to calculate the distances between all points with

SELECT ST_Distance_Sphere(a.geom, b.geom)
FROM animals a, animals b
WHERE a.typ=' 1' AND b.typ=' 2';

However, I want to select all the attributes where the distance is smaller than 500 m. Something like 

SELECT * FROM animals WHERE (ST_Distance_Sphere(a.geom, b.geom)
FROM animals a, animals b
WHERE a.typ=' 1' AND b.typ=' 2') < 500;

Best Answer

SELECT *
FROM animals a, animals b
WHERE a.typ=' 1' AND b.typ=' 2'
AND ST_Distance_Sphere(a.geom, b.geom) < 500;
Related Question