[postgis-users] [OT] How to construct and float8[][] array?

[Stephen Woodbridge]

Hi all,

This is a little bit off topic but I'm trying to create a nxn array like 
the following in SQL:

{{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}

select array_ndims('{{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}'::float[][]);
-- 2

select array_dims('{{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}'::float[][]);
-- [1:4][1:4]

select pg_typeof('{{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}'::float[][]);
-- double precision[]

So I came up with the following:

select array_agg(arow) as dm from (
   select i, array_agg(dist) as arow from (
     select a.source_id as i,
            b.source_id as j,
            st_distance(st_makepoint(a.x, a.y),
            st_makepoint(b.x, b.y)) as dist
       from tsp_00 a, tsp_00 b
      order by a.source_id, b.source_id
   ) as foo group by i order by i
) as bar;

But I get the following error:

ERROR:  could not find array type for data type double precision[]

********** Error **********

ERROR: could not find array type for data type double precision[]
SQL state: 42704

So the question is how can I construct float[][] object like the 
constant using sql?

I would like to use this in pgRouting to build a distance matrix and 
pass it to our TSP solver, like:

select * from pgr_tsp(
   (select array_agg(arow) as dmatrix from (
     select i, array_agg(dist) as arow from (
       select a.source_id as i,
              b.source_id as j,
              st_distance(st_makepoint(a.x, a.y),
              st_makepoint(b.x, b.y)) as dist
         from tsp_00 a, tsp_00 b
        order by a.source_id, b.source_id
     ) as foo group by i order by i
   ) as bar),
   0
);


Thanks,
   -Steve