How to find street for point PostGIS OSM?

Question

I need to select some information about point and street_id for this point from database. I can find minimal distance between point and street, but there are a lot of streets in result set. How can I output only one line for each point? This is my query:

SELECT  ST_Distance(ST_LineInterpolatePoint(street_line, ST_LineLocatePoint(street_line, house_loc))
    , house_loc) As distance
    , street_name
    , street_id
    , point_name
FROM
    (SELECT line.way As street_line,
        point.way as house_loc,
        line.name as street_name,
        point.name as point_name,
        line.osm_id as street_id
    FROM planet_osm_line as line, planet_osm_point as point 
    WHERE   point.amenity = 'cafe' AND 
        line.name notnull AND
         ST_Contains(ST_Transform(ST_GeomFromText('POLYGON((23.692064 52.092482, 23.693534 52.093365, 23.696163 52.090194, 23.693288 52.089858, 23.692064 52.092482))',4326),900913), point.way)) as foo 
WHERE ST_DWithin(street_line, house_loc, 100);

Result for first poin looks like this:

     distance     |   street_name   | street_id |      point_name       
------------------+-----------------+-----------+-----------------------
 14.5456161008274 | Советская улица | 431230474 | Метромилано
 41.7027205345511 | Советская улица | 430790594 | Метромилано
 40.0583523277134 | улица Будённого | 108887692 | Метромилано

I need to get only one line with minimum distance. I tried to select min(distance) but result was the same.

Answer 1

you could use a window function (assuming you are using a reasonably recent version of Postgresql) to number the streets according to their distance to each point (row_number() OVER(PARTITION by point_name ORDER BY ST_Distance()). Then you can embed your query into a simple select query with a where clause keeping only the closest ones (nb = 1), like this:

SELECT *
FROM (
SELECT  ST_Distance(ST_LineInterpolatePoint(street_line, ST_LineLocatePoint(street_line, house_loc))
    , house_loc) AS distance
    , street_name
    , street_id
    , point_name
    , row_number() OVER(PARTITION by point_name ORDER BY ST_Distance(ST_LineInterpolatePoint(street_line, ST_LineLocatePoint(street_line, house_loc))
    , house_loc)) nb
FROM
    (SELECT line.way AS street_line,
        point.way AS house_loc,
        line.name AS street_name,
        point.name AS point_name,
        line.osm_id AS street_id
    FROM planet_osm_line AS line, planet_osm_point AS point 
    WHERE   point.amenity = 'cafe' AND 
        line.name NOTNULL AND
         ST_Contains(ST_Transform(ST_GeomFromText('POLYGON((23.692064 52.092482, 23.693534 52.093365, 23.696163 52.090194, 23.693288 52.089858, 23.692064 52.092482))',4326),900913), point.way)) AS foo 
WHERE ST_DWithin(street_line, house_loc, 100) x
WHERE x.nb = 1
;

Hth.