2

According with this link http://postgis.net/workshops/postgis-intro/advanced_geometry_construction.html I'm creating lines that describe routes. I have a table stopcentroids4 like in figure with the following fields: gid, routes, geom Table stopcentroids4

and i want to draw a line for each route.

Adjusting the query with my table name (see the code beloww) as described at the link above i get that the query runs but with a wrong output. For example the line drawn for routes='1' connects all stopcentroids points whose associated routes contain the character '1' (that are also 1A, 16, 21B and so on) as you can see in the picture with the wrong route.. Wrong route that connects points associated to routes that contains in their text the character '1' like '1', 'A12", or '116' I want the route that connects points associated only to the same character '1' (and not points associated to 11, 21, 12A and so on)as shown in the picture with the right route. Right route that connects points associated to the route whose text is only '1'

How do I correct this query?

This is my function

> 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 strpos(s.routes::text,$2) != 0 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);
1

At a glance the problem I think is you are using strpos(s.routes::text,$2) != 0 to do your matching. You should be using one of the regular expression functions instead.or split to array. Something like (which means if route contains your input route)

string_to_array(s.routes,',') @> ARRAY[$2]
  • compliments LR1234567 and thank you! you jumped at the heart of the matter! Now the selection is almost perfect. A centroid that there should not be included remains in the paths as 2,5,6,7. This centroid is the lowest and right point (where you read the routes 47,57,76A, 92C) which becomes the starting point of these routes. – Fabio Jan 2 '15 at 11:48

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.