postgresql array transpose

Question

I have a query in SQL that results in 2 columns which are coordinates:

{lat1,lat2,lat3} and {lon1,lon2,lon3}

in such a way that elements in the same position will refer to the same objects (referred in another column as an array, again).

The this looks like this:

 objects          latitudes        longitudes
----------------------------------------------
 {1,2,3}      | {lat1,lat2,lat3}  | {lon1,lon2,lon3}

What I'd like to do is to have lat/long couple as this:

 objects          coords
----------------------------------------------
 {1,2,3}      | {{lat1,lon1},{lat2,lon2},{lat3,lon3}}

or even something like:

 objects          coords
----------------------------------------------
 {1,2,3}      | {{1,lat1,lon1},{2,lat2,lon2},{3,lat3,lon3}}

How can I accomplish this in postgresql?

Tomasz Siorek · Accepted Answer · 2013-01-26 00:00:28Z

You can use query as follows (after changing data types) :

select
array_cat
(
    array_cat
    (
        array [ [ objects[1], latitutes[1], longitudes[1] ] ]
    ,   array [ objects[2], latitutes[2], longitudes[2] ]
    )
,   array [ objects[3], latitutes[3], longitudes[3] ]
)
from examplary_table ;

which gives such a result:

{{1,111,121},{2,112,122},{3,113,123}}

For testing purposes:

with examplary_table as
(
    select * from
    (
    values
        (
            '{1,2,3}'::int[]
        ,   '{111,112,113}'::int[]
        ,   '{121,122,123}'::int[]
        )
    ) a (objects, latitutes, longitudes)
)
select
array_cat
(
    array_cat
    (
        array [ [ objects[1], latitutes[1], longitudes[1] ] ]
    ,   array [ objects[2], latitutes[2], longitudes[2] ]
    )
,   array [ objects[3], latitutes[3], longitudes[3] ]
)
from examplary_table ;

Peon · Accepted Answer · 2013-01-21 15:51:56Z

0

I think the easiest way would be to read them, do an explode and then putting them back again in a new array.

$def = array( '{1,2,3}', '{lat1,lat2,lat3}', '{lon1,lon2,lon3}' );

$new = array();
$i=0;
foreach( $def as $value ) {
    $list = explode( ",", str_replace( array("{", "}"), null, $value ) );
    $k=0;
    foreach( $list as $item ) {
        $new[$k][$i] = $item;
        $k++;
    }
    $i++;
}

Output:

Array
(
    [0] => Array
        (
            [0] => 1
            [1] => lat1
            [2] => lon1
        )

    [1] => Array
        (
            [0] => 2
            [1] => lat2
            [2] => lon2
        )

    [2] => Array
        (
            [0] => 3
            [1] => lat3
            [2] => lon3
        )

)

edited Jan 21, 2013 at 15:51

answered Jan 21, 2013 at 15:38

Peon

8,0417 gold badges63 silver badges104 bronze badges

1 Comment

mgm Over a year ago

I am reading it now, thanks. I already solved using a create type at the creating of the table, thought. I still need to figure out why having something like array_agg(row(latitude,longitude)::coordinate) will return something like {"(lat1,lon2)"} instead of {(lat1,lon2)}. I defined coordinate as create type coordinate as (latitude real, longitude real); Ideas? :)

Collectives™ on Stack Overflow

postgresql array transpose

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related