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

[Humberto Cereser Ibanez]

Hi Steve,

did you try execute the most nested subquery and the upper one to see
which one causes the ERROR?

There is also an array_cat function that support multidimensional
arrays.
http://www.postgresql.org/docs/9.1/static/arrays.html

best regards,

Humberto Cereser Ibanez


Em Sex, 2013-06-28 às 14:53 -0400, Stephen Woodbridge escreveu:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users