2

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.

  • Looks like you can add LIMIT 1 to your query to reduce the number down to one (the nearest) – Mapperz May 19 '17 at 19:22
  • Threre are not 3 lines in the result set. I copied result only for 1 point. Real result is about 50 lines. – Дмитрий Шибуня May 20 '17 at 11:05
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.