Пиксельные значения растровых записей для вставки в таблицу в виде столбцов

У меня есть таблица со следующими столбцами:

(ID, row_num, col_num, pix_centroid, pix_val1).

У меня более 1000 записей. Я вставляю свои данные, используя:

insert into pixelbased (row_num, col_num, pix_centroid, pix_val)
select
    (ST_PixelAsPolygons(rast, 1)).x as X,
    (ST_PixelAsPolygons(rast, 1)).y as Y,
    (ST_Centroid((ST_PixelAsPolygons(rast, 1)).geom)) as geom,
    (ST_PixelAsPolygons(rast, 1)).val as pix_val1
from mytable 
where rid=1`

Теперь я пытаюсь вставить все остальные записи в виде столбца, и для меня важен столбец _pix_val1_. Все остальные столбцы останутся прежними. Другими словами, я хочу, чтобы в итоговой таблице были следующие столбцы:

(ID, row_num, col_num, pix_centroid, pix_val1, pix_val2, pix_val3, ....)

Есть ли способ сделать это?


postgresql pivot postgis crosstab raster
person f.ashouri    schedule 30.10.2012    source источник
comment
Краткая версия: нет, если вы заранее не знаете, сколько pix_val, тогда вы можете использовать crosstab. В противном случае вы можете использовать array_agg для получения массива pix_val.   -  person Craig Ringer    schedule 31.10.2012
comment
Я знаю точное количество записей   -  person f.ashouri    schedule 31.10.2012
comment
продолжение размещено здесь: stackoverflow.com/questions/13149421/   -  person Craig Ringer    schedule 31.10.2012
comment
Кстати, если вы хотите сохранить растровое изображение, это невероятно ужасный способ сделать это. Вы действительно должны преобразовать его в bytea и сохранить как массив байтов. Если вы должны иметь его в SQL, используйте двумерный массив.   -  person Craig Ringer    schedule 31.10.2012

Ответы (1)


arrow_upward
2
arrow_downward

Я хотел бы сохранить эти данные в виде растрового изображения в байтах, если это возможно. Вот как взять ряд байтовых значений и превратить его в bytea:

WITH bytes(b) AS (SELECT x % 256 FROM generate_series(1,53000) x)
SELECT ('\x'||string_agg(lpad(to_hex(b),2,'0'),''))::bytea FROM bytes;

Вы можете получить доступ к полям или диапазонам массива байтов, используя функцию substr. Этот bytea организован как линейный массив пикселей, но вы можете найти более полезным организовать его в более традиционный формат растрового изображения. Кроме того, если ваши пиксели состоят из более чем одного байта, вам может потребоваться справиться с обратным порядком байтов и прямым порядком байтов. Вы можете сделать это в SQL, но, вероятно, это будет намного проще в процедурном языке, таком как PL/Perl.

В противном случае разумным выбором будет многомерный массив.

Используя оператор generate_series вместо поля pix_val для удобного тестирования, этот запрос создает двумерный массив целых чисел с использованием двух проходов агрегирования:

SELECT ('{'||string_agg(subarray, ',')||'}')::integer[] AS arr
FROM (
   SELECT array_agg(x order by x)::text 
   FROM generate_series(1,53000) x
   GROUP BY width_bucket(x, 1, 53001, 100)
) a(subarray);

Неудачное использование формы строкового литерала двумерного массива необходимо из-за того факта, что array_agg не может агрегировать массивы. На мой взгляд, это настоящая проблема PostgreSQL; в общем, его многомерные массивы непривычны для работы и несовместимы с тем, как большинство приложений и языков реализуют массивы.

Вы можете получить поля из массива, проиндексировав его. Пример:

regress=> SELECT ('{'||string_agg(subarray, ',')||'}')::integer[] AS arr INTO test FROM (SELECT array_agg(x order by x)::text from generate_series(1,53000) x GROUP BY width_bucket(x, 1, 53001, 100)) a(subarray);

regress=> \d test

      Table "public.test"
 Column |   Type    | Modifiers 
--------+-----------+-----------
 arr   | integer[] |

test содержит один массив с двумя измерениями:

regress=> \x
regress=> select array_dims(test.arr), array_ndims(test.arr), array_length(test.arr,1), array_length(test.arr,2) FROM test;
-[ RECORD 1 ]+---------------
array_dims   | [1:100][1:530]
array_ndims  | 2
array_length | 100
array_length | 530

Я могу получить элементы с двухуровневой индексацией:

regress=> SELECT test.arr[4][4] FROM test;
 arr  
------
 1594
(1 row)

или «столбец» с нарезкой:

regress=> SELECT test.arr[4:4][1:530] FROM test;

Как ни странно, это по-прежнему двумерный массив, верхнее измерение имеет глубину всего в один элемент. Вы можете сгладить его (неэффективно) с помощью unnest и array_agg, если вам нужно.

Двумерные массивы в PostgreSQL, как видите, несколько странны, но именно это вы и пытаетесь сделать.

person Craig Ringer    schedule 31.10.2012