Simple PostGIS ST_Distance query for two points from the same geometry table?

Question

Note: This question was published at Stackoverflow at first.

I have a geometry table points_table with SRID 4326 and a few points in it.

I need to find a distance between a pair of points (features) from this table.

I would like to select one point with id = X and another point with id = Y and then find a distance between them with ST_Distance function.

I know how to query these two points from the database:

select * from points_table where id = X or id = Y;

But this query returns a result, which I do not know how to pass to ST_Distance, because ST_Distance takes 2 parameters.

I have tried Select ST_Distance(select the_geom from points_table where id = X or id = Y);

But it is obviously wrong.

How can I find the distance between two geometry points (features) from a table?

P.S. All the examples I have found so far show the ST_Distance usage with points hardcoded into the arguments of the ST_Distance function. I have not found an example, where ST_Distance is used to calculate a distance between two points from the same table.

Answer 1

You need to reference your table twice, giving it different aliases:

SELECT ST_Distance(a.geom, b.geom)
FROM points_table a, points_table b
WHERE a.id='x' AND b.id='y';