栅格记录的像素值将作为列插入表中 [英] Pixel values of raster records to be inserted in the table as columns

问题描述

我有一个包含以下列的表格:

I have a table with following columns:

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

我有1000多个记录.我正在使用以下数据插入数据:

I have more than 1000 records. I am inserting my data using:

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_列对我来说很重要.所有其他列将保持不变.换句话说，我希望决赛桌有这些专栏:

Now I am trying to insert all the other records as a column and _pix_val1_ column is important for me. All the other columns will remain the same. In the other word, I want the final table to have these columns:

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

有办法吗?

推荐答案

如果可能的话，我希望将此数据作为位图存储在bytea中.以下是获取一系列字节值并将其转换为字节的方法:

I would want to store this data as a bitmap in a bytea if possible. Here's how to take a series of byte values and turn it into a 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函数访问字节数组的字段或范围.该字节结构被组织为线性像素阵列，但是您可能会发现将其组织为更传统的位图格式更为有用.另外，如果您的像素超过一个字节，则可能需要处理big-endian与little-endian.您可以在SQL中执行此操作，但是使用PL/Perl之类的过程语言可能要容易得多.

You can access fields or ranges of the byte array using the substr function. This bytea is organized as a linear pixel array, but you may find it more useful to organize it into a more traditional bitmap format. Also, if your pixels are more than one byte you may need to cope with big-endian vs little-endian. You could do that in SQL, but it's likely to be much easier in a procedural language like PL/Perl.

否则，多维数组将是一个比较合理的选择.

Failing that, a multidimensional array would be a somewhat reasonable choice.

使用generate_series语句代替pix_val字段进行方便的测试，此查询使用两次聚合过程生成一个二维整数数组:

Using a generate_series statement as a substitute for your pix_val field for convenient testing, this query produces a two-dimensional array of integers using two aggregation passes:

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中是一个真正的疣.总的来说，它的多维数组与大多数应用程序和语言实现数组的方式很奇怪并且不一致.

The unfortunate use of the string literal form of the two dimensional array is made necessary by the fact that array_agg cannot aggregate arrays. In my view this is a real wart in PostgreSQL; in general its multidimensional arrays are odd to work with and inconsistent with how most applications and languages implement arrays.

您可以通过索引将字段移出数组.示例:

You can get fields out of the array by indexing it. Example:

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包含一个二维的数组:

test contains a single array with two dimensions:

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

我可以获得具有两级索引的元素:

I can get elements with two-level indexing:

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对其进行平坦化(效率低下).

Oddly, this is still a two-dimensional array, the top dimension is just one element deep. You can flatten it (inefficiently) with unnest and array_agg if you need to.

您可以看到，PostgreSQL中的二维数组有些奇怪，但是您尝试做的也是如此.

Two-dimensional arrays in PostgreSQL are somewhat weird, as you can see, but so is what you're trying to do.

这篇关于栅格记录的像素值将作为列插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！