PostGIS calculate distances from one point to multiple points

In PostgreSQL 12.1 64bit and PostGIS 3.0, I have two small tables of points. I want to determine the distance from one point in one table to multiple points in the other table. The tables both have the same SRID: 26910.

The WKT for the table with multiple points,ebd_sampl, has these coordinates:

ID           geom
'S6485705'  'POINT(473697.203011859 5107286.1114417)'
'S16187642' 'POINT(473388.313557094 5107381.77541894)'
'S21737009' 'POINT(473304.975396136 5107449.75936742)'
'S27967642' 'POINT(472992.535028299 5107506.05876625)'
'S30893534' 'POINT(472638.34972214 5107604.73782347)'
'S35264483  'POINT(473106.919925334 5107464.0012127)'

The WKT point from the other table,grid_sampl, is: 'POINT(473144.563851624 5107073.33440939)'

I can write an inner query with a WHERE clause to select one point in the first inner query and get one distance, between one point in each table. I thought by taking out the WHERE clause in the first inner query, that query would be more general and return a table with distances for all points in first table to the selected point in the second table. But I don't know how to write the SQL for that.

I tried:

SELECT ST_Distance(
    (SELECT geom FROM p_loc.ebd_sampl),
    (SELECT geom FROM p_loc.grid_sampl WHERE data = '441062')
);

And this is the result:

ERROR:  more than one row returned by a subquery used as an expression

I'd like a table like (the distance values are made up here):

ID          distance
'S6485705'  400
'S16187642' 550
'S21737009' 633
'S27967642' 401
'S30893534' 700
'S35264483' 501

What SQL should I use so that the respective distances for each point in the first table to the one selected point in the second table are returned?

Solution

Try passing both tables in the FROM clause and filtering the values you want in the WHERE clause, e.g.

SELECT id,ST_Distance(t.geom,grid_sampl.geom) 
FROM t,grid_sampl
WHERE grid_sampl.data = '441062' ;

Example

CREATE TABLE t (id TEXT, geom GEOMETRY);
INSERT INTO t VALUES
('S6485705',  'SRID=26910;POINT(473697.203011859 5107286.1114417)'),
('S16187642', 'SRID=26910;POINT(473388.313557094 5107381.77541894)'),
('S21737009', 'SRID=26910;POINT(473304.975396136 5107449.75936742)'),
('S27967642', 'SRID=26910;POINT(472992.535028299 5107506.05876625)'),
('S30893534', 'SRID=26910;POINT(472638.34972214 5107604.73782347)'),
('S35264483', 'SRID=26910;POINT(473106.919925334 5107464.0012127)');

CREATE TABLE grid_sampl (data TEXT, geom GEOMETRY);
INSERT INTO grid_sampl VALUES ('441062','SRID=26910;POINT(473144.563851624 5107073.33440939)');

SELECT id,ST_Distance(t.geom,grid_sampl.geom) 
FROM t,grid_sampl
WHERE grid_sampl.data = '441062' ;

    id     |   st_distance    
-----------+------------------
 S6485705  | 592.185871921898
 S16187642 | 393.128191928728
 S21737009 | 409.179193802818
 S27967642 | 458.653608010902
 S30893534 | 733.922566341344
 S35264483 | 392.476262206356
(6 Zeilen)