[postgis-users] Raster data questions

Wed Dec 3 04:21:55 PST 2014

Hey,
I'm afraid you don't have given the most important information
, thus not allowing to choose between a value  per row or a file per row!

So I discuss both :
 first hypothesys : you'll have few files at a time,
  - number of values is not very high (few millions)
  - you want to get parts of the data based on index (1D,2D,3D) or maybe
value
You will store each value of the file into a row.
Thus  a file with a char array of size N will yield a table of N rows.
CREATE TABLE one_value_per_row (
gid int, --contains 1,2,..N , ordered by x,y,z
X int,
Y int,
Z int,
value smallint --or somehting like that
);
 if you want your point ordered with original array index
SELECT *
FROM one_value_per_row
WHERE gid BETWEEN 2 AND 45;
More interesting, you can select 1-2-3D line/square/cube like this
SELECT *
FROM one_value_per_row
WHERE X BETWEEN 2 AND 3
  AND Y BETWEEN 56 AND 89
  AND Z BETWEEN 21 AND 42;

of course for this kind of querry to beb efficient, you need some index
CREATE INDEX ON one_value_per_row (gid);--the same for all columns

This approach __won't__ scale

Second hypothesis : you have a great many of files (over the hundred range)
*you will store each file in one row.*
Now you have at least 3 options.
* You store the data as a postgres type, the simplest is a postgres array
    each row would contain a smallint[] (for instance)
    This may not perform well with millions of values
    You access your data like you would for a C char[] :  value[35%12]
    I think it is a pretty bad idea

 * You ask the database to store the original binary, and you have to
create C/python function in the database  to access the data
  (like get_values(Xmin=1,XMax=3,YMin=56,YMax=89,Zmin=21,ZMax=42))
   this is an approach identical to the pg_pointcloud
<https://github.com/pgpointcloud/pointcloud> project
   For instance, your data could be stored as a 3D numpy array. slicing it
would be then efficient and very easy (one line)

* You don't want to redevelop, and use pg_pointcloud out of the box :
     You consider that each value of your original array is a 3D point
     Then each file is a point cloud stored on one row of the database.
    you define each value of your initial array as a point X,Y,Z being the
3D coordinate. you store the value in an attribute, You can also store the
original array index
     This is the easiest and most efficient out-of-the-box solution
        (see this introduction presentation
<http://boundlessgeo.com/wp-content/uploads/2013/10/pgpointcloud-foss4-2013.pdf>
, and this simple to complex presentation
<https://github.com/Remi-C/Postgres_Day_2014_10_RemiC/raw/master/presentation/A%20PostgreSQL%20Server%20for%20Point%20Cloud%20Storage%20and%20Processing.pdf>
)

   you would get your data like this
 SELECT pc_get(pt,'X'),pc_get(pt,'Y'),pc_get(pt,'Z'),pc_get(pt,'value'),
FROM one_file_per_row,
  PC_FilterBetween(
       PC_FilterBetween(
         PC_FilterBetween(point_cloud, 'X',2,3)
       ,'Y',56,89)
      ,'Z',21,42) as patch
  , pc_explode(patch) AS pt
WHERE file_name = ....

Choosing a solution depends on a lot of factors,
pondering performances, storage, usage, facility to create, facility ot
maintain, etc etc.

Cheers,
Rémi-C

2014-12-02 21:07 GMT+01:00 George Merticariu <merticariug at gmail.com>:

> Hello!
>
> Thank you for your answer!
>
> Please find below more information:
>
> The type of data which I will insert is an 1D array of values between 0
> and 255.
> This array should be mapped to different dimensions:
> 1D - grey string
> 2D - grey image
> 3d - grey cube
>
> There will be a lot of reads from the arrays and only one write at insert.
> The size of the array will be kept constant and will be given by the file
> size.
>
> Is there a way to import directly the 1D binary file into arrays of higher
> dimensions or do I need to convert it first and then import it?
>
> Best,
> George
>
> On Tue, Nov 25, 2014 at 5:56 PM, Rémi Cura <remi.cura at gmail.com> wrote:
>
>> Hey,
>> you don't even need postgis for this, you could store a cube per line,
>> with a x,y,z number, indexes on it,then querry like
>> SELECT *
>> FROM my_cube_table
>> WHERE x BETWEEN 100 AND 200
>> AND y BETWEEN 23 AND 45
>> AND z BETWEEN 45 AND 67
>> You could alos use postgres range type.
>> Of course you could store cube as meshes, and use pure 3D function
>> (postgis so )
>> etc etc
>>
>> It is very hard to answer you if you don't explain what you want to do
>> with this cubes , how many you will have, ifyou read it more or write it
>> more, what kind of data it contains, if the size of each cube may change,
>> if you want to convert this to geo types ...
>>
>> Cheers,
>> Rémi-C
>>
>> 2014-11-25 17:32 GMT+01:00 George Merticariu <merticariug at gmail.com>:
>>
>>> Hello!
>>>
>>> I want to use PostGIS for handling 3D cubes but I couldn't figure out
>>> how to do it from the manual.
>>>
>>> The main tasks I want to accomplish are:
>>>
>>> 1. Import a 1D char array file (grey cube) into a 3D cube.
>>> 2. Retrieve sections from the cube, where a section is defined by a
>>> domain.
>>>
>>> Example:
>>>
>>> Given a file of 1024*1024*1024 bytes, I want to import it into a cube
>>> with the domain [0:1023, 0:1023, 0:1023]. Then, select the sub-domains
>>> (examples):
>>>
>>>    -  [100:200, 100:200, 0:100]
>>>    -  [0:1, 0:1023, 0:1023]
>>>
>>>
>>> Is this possible using PostiGIS? If yes, are there any detailed
>>> tutorials which explain how to do that?
>>>
>>> Thank you!
>>>
>>> Best regards,
>>> George Merticariu
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
> --
> George Merticariu
>
> Jacobs University Bremen
> B.Sc. Computer Science
> Class of 2014
>
> g.merticariu at jacobs-university.de
> merticariug at gmail.com
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141203/32de7689/attachment.html>