Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The bigger picture: tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each pipeline into segments of maximum 1 meter distance. Then, for each segment I am doing a spatial query that selects the closest street (in a maximum 20 meter range). Then, by finding out which street appears the most in all the segments that belong to that specific pipeline(by id), I am inserting the found street name into the pipeline feature.

In a trigger (on insert, update) the function is making a join based on the unique id column.

Having tableX (id, name)

the function that gets triggered looks like this:

CREATE or REPLACE function T_function() RETURNS trigger AS $T_function$
BEGIN
NEW.name =(SELECT ......... WHERE other_table.id = NEW.id .....);
RETURN NEW;
END;
$T_function$ LANGUAGE plpgsql;

As you can see, the function does a join based on the newly created id. It works only on update, probably because the NEW.name does not use the new id. How can I enforce saving/using/generating NEW.id before NEW.name ..?


later edit describing the tables (with postgis geometry) and what I am trying to do:

CREATE TABLE tableX (
    id integer, 
    name varchar, 
    shape geometry
);
--   geometry - linestrig

CREATE TABLE tableY (
    id integer, 
    name varchar, 
    shape geometry
);
-- geometry - linestring


CREATE or REPLACE function T_function RETURNS trigger AS 
$T_function$
BEGIN
    NEW.id = NEW.id;
    NEW.name = (
        SELECT name FROM (
            SELECT id, name, cnt
            FROM (
                SELECT 
                    id, 
                    name, 
                    COUNT(*) AS cnt, 
                    RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk
                FROM (
                    SELECT loc.id, r.name, ST_Distance(r.shape, loc.shape) AS dist
                    FROM (
                        SELECT id, (st_dumpPoints(st_segmentize(shape, 1))).geom AS shape 
                        FROM tableX
                    ) as loc 
                    INNER JOIN tableY AS r ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE loc.id = NEW.id
                    ORDER BY loc.id, ST_Distance(loc.shape, loc.shape)
                ) AS coco 
                GROUP BY id, name
            ) AS tg
            WHERE rnk = 1
        ) as b
    );
    RETURN NEW;
END;
$T_function$ 
LANGUAGE plpgsql
;

CREATE TRIGGER T_function_trigger 
BEFORE INSERT OR UPDATE ON tableX 
FOR EACH ROW EXECUTE PROCEDURE T_function()
;

I am trying to do a spatial query and to fill tableX.name column with the name from tableY.name column based on the nearest neighbor approach. It only works on update for now.

share|improve this question
    
You should add the definition of the two tables involved, with possibly the foreign key(s) between them. Without that there could be too many scenarios and answers wouldn't be much more than guesses. –  dezso Dec 17 '12 at 8:01
add comment

1 Answer

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 := (...)
share|improve this answer
    
The id is unique (I specified it) and I already knew the problem. But what is the solution? As the NEW.id is generated in the trigger function, how can I make use of it? –  Tudor Dec 17 '12 at 11:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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