[postgis-users] linestring aggregation

[Nicolas Gillet - MARKET-IP]

Hello,

Thanks for the tips I did not think about using my table twice.

I search a bit around your suggested query

SELECT r1.name, r1.code, geometry_sum(r1.the_geom)
FROM road r1, road r2
WHERE ST_Touches(r1.the_geom, r2.the_geom)
GROUP BY r1.name, r1.code

Unfortunately I did not get what I want that way.
It doesn't respect my touching condition.

I made an small test on 16 piece of linestrings representing two parallel
lines (of 8 pieces each)
I named them A and B like this (all the items on the same line are
connected)

B1 B2 B3 B4 A5 A6 A7 A8
A1 A2 A3 A4 B5 B6 B7 B8

As a result I should have had 4 records
(B1 B2 B3 B4)
(A5 A6 A7 A8)
(A1 A2 A3 A4)
(B5 B6 B7 B8)
But I actually got 2 records, all the "A" together and all the "B" together.
(I put my test table at the end of this message if someone wants to give a
try)

Maybe there could be something special to do in the aggregate declaration to
put a condition on the aggregation but I did not find anything like this :s

Nicolas


Test table
CREATE TABLE road
(
  id integer NOT NULL,
  "name" character varying(50) NOT NULL,
  code integer NOT NULL,
  the_geom geometry NOT NULL,
  CONSTRAINT roads PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

INSERT INTO road (id, name, code, the_geom) VALUES
(1, 'Road 1', 1, ST_GeometryFromText('LINESTRING(0 0, 2 0, 4 0)', 4326)),
(2, 'Road 1', 1, ST_GeometryFromText('LINESTRING(4 0, 6 0, 8 0)', 4326)),
(3, 'Road 1', 1, ST_GeometryFromText('LINESTRING(8 0, 10 0, 12 0)', 4326)),
(4, 'Road 1', 1, ST_GeometryFromText('LINESTRING(12 0, 14 0, 16 0)', 4326)),

(5, 'Road 2', 1, ST_GeometryFromText('LINESTRING(16 0, 18 0, 20 0)', 4326)),
(6, 'Road 2', 1, ST_GeometryFromText('LINESTRING(20 0, 22 0, 24 0)', 4326)),
(7, 'Road 2', 1, ST_GeometryFromText('LINESTRING(24 0, 26 0, 28 0)', 4326)),
(8, 'Road 2', 1, ST_GeometryFromText('LINESTRING(28 0, 30 0, 32 0)', 4326)),

(9, 'Road 2', 1, ST_GeometryFromText('LINESTRING(0 1, 2 1, 4 1)', 4326)),
(10, 'Road 2', 1, ST_GeometryFromText('LINESTRING(4 1, 6 1, 8 1)', 4326)),
(11, 'Road 2', 1, ST_GeometryFromText('LINESTRING(8 1, 10 1, 12 1)', 4326)),
(12, 'Road 2', 1, ST_GeometryFromText('LINESTRING(12 1, 14 1, 16 1)',
4326)),

(13, 'Road 1', 1, ST_GeometryFromText('LINESTRING(16 1, 18 1, 20 1)',
4326)),
(14, 'Road 1', 1, ST_GeometryFromText('LINESTRING(20 1, 22 1, 24 1)',
4326)),
(15, 'Road 1', 1, ST_GeometryFromText('LINESTRING(24 1, 26 1, 28 1)',
4326)),
(16, 'Road 1', 1, ST_GeometryFromText('LINESTRING(28 1, 30 1, 32 1)',
4326));

-----Message d'origine-----
De : postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] De la part de Sean
Envoyé : mardi 29 décembre 2009 17:21
À : postgis-users at postgis.refractions.net
Objet : Re: [postgis-users] linestring aggregation

I think you have to pull it out in the WHERE clause.

I'm not sure if this will work, I'm not good at doing SQL in my head
and can't test it right now:

SELECT name, code, geometry_sum(r1.the_geom)
FROM roads r1, roads r2
WHERE ST_Touches(r1.the_geom, r2.the_geom)
GROUP BY name, code

and you'll probably have to UNION this with a query that gets the
pieces that don't touch.

   Sean