2

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;
0
SELECT *
FROM animals a, animals b
WHERE a.typ=' 1' AND b.typ=' 2'
AND ST_Distance_Sphere(a.geom, b.geom) < 500;
  • Thanks! Should have putten a little more effort in that myself, wasn't so difficult after all! – yuno May 11 '16 at 18:51
0

You can try using ST_DWithin for geography, e.g.:

SELECT *
FROM animals a, animals b
WHERE a.typ=' 1' AND b.typ=' 2'
AND ST_DWithin(a.geom::geography, b.geom::geography, 500.0, FALSE);

Where the last parameter is use_spheroid:=FALSE to use a faster sphere calculation method. This method can be optimized using geography indexes:

CREATE INDEX animals_geog_idx ON animals USING gist ((geom::geography));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.