0

I have a table stopcentroids4 like in figure with the following fields: gid, routes, geom Input table

and i want to draw a line for each route that runs through centroids. In my code there is a mistake. Actually the centroid on the lowest and right point of the picutre (where you can read the associated routes 47,57,76A, 92C) becomes the starting point of the routes 2,5,6,7. Example of the output of the route "2" that includes the wrong centroid, the lowest and right point of the picture

The postresql code follows below:

CREATE OR REPLACE FUNCTION walk_centers(bigint, text)   RETURNS geometry AS $BODY$ WITH RECURSIVE next_stop(geom, idlist) AS (
                 (SELECT geom AS geom,
                    ARRAY[gid] AS idlist
                   FROM stopcentroids4
                  WHERE gid = $1)
        UNION ALL
                ( SELECT s.geom AS geom,
                    array_append(n.idlist, s.gid) AS idlist
                   FROM stopcentroids4 s,
                    next_stop n
                  WHERE string_to_array(s.routes,',') @> ARRAY[$2] 
                  AND NOT n.idlist @> ARRAY[s.gid]
                  ORDER BY st_distance(n.geom, s.geom) ASC
                 LIMIT 1)
        )  SELECT ST_MakeLine(geom) AS geom FROM next_stop; 
        $BODY$   
        LANGUAGE sql VOLATILE   COST 100; 
        ALTER FUNCTION walk_centers(bigint, text)   OWNER TO postgres;

This is my code:


CREATE TABLE lines AS
-- Distinct route identifiers! WITH routes AS (   SELECT DISTINCT unnest(string_to_array(routes,',')) AS route   FROM stopcentroids4 ORDER BY route ),
-- Joined back to stops! Every route has all its stops! stops AS (   SELECT s.gid, s.geom, r.route   FROM routes r   JOIN stopcentroids4 s  ON (strpos(s.routes, r.route) <> 0) ),
-- Collects stops by routes and calculate centroid! centers AS (   SELECT ST_Centroid(ST_Collect(geom)) AS geom, route   FROM stops   GROUP BY route ),
-- Calculate stop/center distance for each stop in each route. stops_distance AS (   SELECT s.*, ST_Distance(s.geom, c.geom) AS distance   FROM stops s JOIN centers c   ON (s.route = c.route)   ORDER BY route, distance DESC ),
-- Filter out just the furthest stop/center pairs. CREATE TABLE brighton_lines AS
-- Distinct route identifiers! WITH routes AS (   SELECT DISTINCT unnest(string_to_array(routes,',')) AS route   FROM stopcentroids4 ORDER BY route ),
-- Joined back to stops! Every route has all its stops! stops AS (   SELECT s.gid, s.geom, r.route   FROM routes r   JOIN stopcentroids4 s  ON (strpos(s.routes, r.route) <> 0) ),
-- Collects stops by routes and calculate centroid! centers AS (   SELECT ST_Centroid(ST_Collect(geom)) AS geom, route   FROM stops   GROUP BY route ),
-- Calculate stop/center distance for each stop in each route. stops_distance AS (   SELECT s.*, ST_Distance(s.geom, c.geom) AS distance   FROM stops s JOIN centers c   ON (s.route = c.route)   ORDER BY route, distance DESC ),
-- Filter out just the furthest stop/center pairs. first_stops AS (   SELECT DISTINCT ON (route) stops_distance.*   FROM stops_distance )
-- Pass the route/stop information into the linear route generation function! SELECT   row_number() over () AS gid, -- QGIS likes numeric primary keys   route,   walk_centers(gid, route) AS geom FROM first_stops;

-- Do some housekeeping too ALTER TABLE lines ADD PRIMARY KEY (gid);

Maybe the problem is in the function included in the join: "strpos(s.routes, r.route) <> 0" and i should replace the regular function "string_to_array" but I'm getting syntax errors.

  • I converted your block quote into a code block. Next step: please manually reformat the code so we don't have to do any scrolling to read each line. Thanks. – Martin F Jan 7 '15 at 11:44
  • Thank you John, I tried to copy the code above erasing comments but i get the result that my code is too much long for a comment – Fabio Jan 12 '15 at 13:03

Your Answer

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

Browse other questions tagged or ask your own question.