How can I ensure that in a insert trigger a column gets filled before other column?
If I understood correctly, this query will return the streets and counts you are looking for (for all pipelines and streets):

SELECT x.id, y.name, count(x.id) AS cnt
FROM
    (
        SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
        FROM 
            tableX x
            INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
    ) t
GROUP BY x.id, y.name
ORDER BY cnt DESC
FETCH FIRST ROW ONLY
;


Now let's pack it in a trigger procedure:

CREATE or REPLACE function T_function RETURNS trigger AS 
$T_function$
BEGIN
    SELECT name
    INTO NEW.name
    FROM 
        (
            SELECT x.id, y.name, count(x.id) AS cnt
            FROM
                (
                    SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
                    FROM 
                        tableX x
                        INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE x.id = NEW.id -- we count the relevant pipeline only
                ) t
            GROUP BY x.id, y.name
            ORDER BY cnt DESC     -- we don't care the other
            FETCH FIRST ROW ONLY  -- streets in the list
        )
    ;

    RETURN NEW;
END;
$T_function$ 
LANGUAGE plpgsql
;



the line NEW.id := NEW.id did nothing useful
I flattened the innermost subquery into a simple JOIN (no need for the subquery)
to decrease the level of nested parentheses, I used SELECT name INTO NEW.name instead of NEW.id := (...)