ST_MakeLine From Query Returns Empty

Question

I am trying to get the length of a line between two selected points. The points reside in table subs and are utilizing srid 4236.

I can verify the selection exists...

SELECT id, name, geom FROM subs where  name = 'DALLAM COUNTY'or name = 'DALHART';

Which returns the expected results...

However when I try to construct line with the same selection I get empty results?

SELECT ST_Length(ST_Transform(ST_MakeLine(geom), 4236)) FROM
    subs where  name = 'DALLAM COUNTY'or name = 'DALHART';

The sql returns empty results.

What am I missing?

Answer 1

It seems you are attempting to find the distance between two geometries in the same table. Try this:

SELECT ST_Distance(a.geom::geography, b.geom::geography) AS dist_m
FROM subs a, subs b
WHERE a.name='DALLAM COUNTY' AND b.name='DALHART';

The result from ST_Distance will be the minimum distance of a geodesic between the two geometries in metres.

Answer 2

From your screenshot it looks as though your geometry is of type multipoint, so naturally it doesn't have a length.

It also appears that the transform is unnecessary as the data is already in 4326 projection.