How to address the alias of a sub-query in the where-clause in PostgreSQL/PostGIS?

Question

I have a problem with a Postgres query and can't find a solution in stackexchange or any SQL Documentation.

I have two tables in PostGIS, one for streets and one for house-numbers. I now want to find the closest given house number to a certain street (for an address search).

In a sub-query I calculated the distance, selected the street name and the number as well as the coordinates. In the where clause I tried to filter the data for the minimal distance. I addressed the subquery with its alias, but get the error that the "relation is not defined", although I just defined it. Any simple solution for that problem?

SELECT * FROM
(SELECT ST_Distance(ha.geom, sn.geom) AS cp
 , sn.nam AS name
 , ha.hnr AS nr
 , ST_AsText(ST_Transform(ha.geom,4326)) AS pt
FROM hausnummern_be AS ha, strassen_be AS sn
WHERE ha.hnr = '1' AND sn.nam LIKE 'Samariter%'
ORDER BY cp ASC) AS xy
WHERE cp = (SELECT min(cp) FROM xy);

Answer 1

You can only reference xy if it were a CTE. The reason is because it's born at the same time as you main query.

So for reference you'd have to do do this:

   WITH xy AS( (SELECT ST_Distance(ha.geom, sn.geom) AS cp
 , sn.nam AS name
 , ha.hnr AS nr
 , ST_AsText(ST_Transform(ha.geom,4326)) AS pt
FROM hausnummern_be AS ha, strassen_be AS sn
WHERE ha.hnr = '1' AND sn.nam LIKE 'Samariter%'
ORDER BY cp ASC)
SELECT * 
    FROM xy
     WHERE cp = (SELECT min(cp) FROM xy);

But as you said, this is probably not best way to solve this problem. DISTINCT ON would be better.

DISTINCT ON solution there are many one is KNN, but I'll post the version that works on most PostgreSQL and PostGIS. The ST_DWithin significantly speeds things up if you want to scale this to more than one search. Replace 10 with some value you feel that searching any further distance is unlikely to find an answer. e.g. I say a house should be no more than 50 units from it's closest street. The bigger you make it the slower, but safer you'll be.

  SELECT DISTINCT ON (ha.hnr)  ST_Distance(ha.geom, sn.geom) AS cp
, sn.nam AS name
, ha.hnr AS nr
, ST_AsText(ST_Transform(ha.geom,4326)) AS pt
 FROM hausnummern_be AS ha INNER JOIN strassen_be AS sn
      ST_DWithin(ha.geom, sn.geom, 50)
  WHERE ha.hnr = '1' AND sn.nam LIKE 'Samariter%'
     ORDER BY ha.hnr, cp ASC;