Ordering in an aggregate -- points to paths - Mailing list pgsql-sql

OK, I know relying on ordering in an aggregate is sinful, but I don't know
if it's mortal or venial.

Long explanation, please bear with me, here's the background:

---
CREATE TABLE "foo" (       "a" point,       "b" int
);
INSERT INTO ...

SELECT * FROM foo;   a    | b
---------+---(1,1)   | 1(1,2)   | 3(1.5,3) | 5(4,4)   | 2(-1,-2) | 4
(5 rows)

---

So I want to create paths from the points in field a ordered by b.  The
first step is the rather laborious construction of an append_point function
(am I missing something, BTW? -- seems like an obvious function to have but
I couldn't find a built-in).

---
CREATE FUNCTION "path" (point) RETURNS path AS 'select
path_add_pt(''[(0,0)]''::path,$1)' LANGUAGE 'sql';


CREATE FUNCTION "append_point" (path,point) RETURNS path AS 'select case
WHEN $1 is null THEN path($2)
WHEN $2 is null THEN $1
ELSE path_add($1,path_add_pt(''[(0,0)]''::path,$2))
END' LANGUAGE 'sql';

---

and then the aggregate itself follows in the obvious way

---
CREATE AGGREGATE create_path ( BASETYPE = point, SFUNC = append_point, STYPE
= path);

SELECT create_path(a) FROM foo;            create_path
-------------------------------------[(1,1),(1,2),(1.5,3),(4,4),(-1,-2)]
(1 row)

---

and moreover, with subselect for ordering following examples from this
mailing list

---
SELECT create_path(c.a) FROM (SELECT a FROM foo ORDER BY b) c;            create_path
-------------------------------------[(1,1),(4,4),(1,2),(-1,-2),(1.5,3)]
(1 row)

---

So far so good. Now for the real data.  The points are an ordered (by
seq_no) set of latitude, longitude pairs defining a "fir", the boundary of a
region on the surface of the earth.  (fir_ident, fir_indicator, seq_no) is
unique.

---
CREATE TABLE "fir_coords" (       "node" point,
...       "fir_ident" character(4),       "fir_indicator" character(4),       "seq_no" character(4),
);

SELECT c.fir_ident, c.fir_indicator, create_path (c.node) AS fir_edge      INTO fir_e      FROM      (SELECT fir_ident,
fir_indicator,node              FROM fir_coords              ORDER BY fir_ident, fir_indicator,seq_no) c
 
GROUP BY fir_ident, fir_indicator;
---

The fir_e table should contain the paths for the fir. And for simple shapes
(a few dozen points) it works fine.

But the problem is that e.g. Austria's fir is defined by 1577 points, and
the path that I construct appears to be in the wrong order.

foo=# SELECT fir_ident, fir_indicator, seq_no, node  FROM fir_coords WHERE
fir_ident = 'LOVV'  LIMIT 10;fir_ident | fir_indicator | seq_no |                 node
-----------+---------------+--------+---------------------------------------LOVV      | B             | 0005   |
(0.241534175928771,0.851255253839368)LOVV     | B             | 0010   | (0.241844456684681,0.851240709428934)LOVV
|B             | 0015   | (0.242135344893347,0.851167987376768)LOVV      | B             | 0020   |
(0.242368055460279,0.851022543272435)LOVV     | B             | 0025   | (0.242571677206345,0.850935276809835)LOVV
|B             | 0030   | (0.242862565415011,0.850717110653336)LOVV      | B             | 0035   |
(0.24312436480281,0.850528033317703)LOVV     | B             | 0040   | (0.243327986548876,0.850368044802937)LOVV
|B             | 0045   | (0.243560697115809,0.850324411571637)LOVV      | B             | 0050   |
(0.243633419167975,0.850208056288171)

whereas my path fir_edge looks like:


((0.268140750748062,0.854920445268556),(0.195244165656432,0.819810238482603)
,(0.238688319620658,0.812014434490362),(0.286597607587902,0.82373722929959),
(0.184975811890532,0.817861287484543),(0.192917059987107,0.816959534037679),
(0.244753338771338,0.849015414632642),(0.298204047113664,0.838528894710242),
(0.277478262246232,0.852418806674031),...

The ordering has gone awry. And since I'm going to draw the fir by 'joining
the dots' that's a Bad Thing.

Is this a problem with my functions, or is there something going on in the
internals that makes it dangerous to rely on the
ordered-subselect-with-aggregate construction above?

Thanks

Julian Scarfe