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 > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
