[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Raster data questions
From: Rémi_Cura <remi.cura () gmail ! com>
Date: 2014-12-08 9:20:44
Message-ID: CAJvUf_u_ZG0fx8tydcCCx0f=fT9iGO5u9x63Bb-_oiZK7Nj7LQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
You are very welcome,
it is the work of Paul Ramsey though ;-)
Cheers,
Rémi-C
2014-12-08 0:40 GMT+01:00 George Merticariu <merticariug@gmail.com>:
> Hello Remi,
>
> Your suggestion with point clouds worked perfectly.
>
> Thank you!
>
> Best regards,
> George
>
> On Wed, Dec 3, 2014 at 1:21 PM, Rémi Cura <remi.cura@gmail.com> wrote:
>
> > 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@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@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@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@lists.osgeo.org
> > > > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> > > > >
> > > >
> > > >
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users@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@jacobs-university.de
> > > merticariug@gmail.com
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users@lists.osgeo.org
> > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> > >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >
>
>
>
> --
> George Merticariu
>
> Jacobs University Bremen
> M.Sc. Computer Science
> Class of 2016
>
> g.merticariu@jacobs-university.de
> merticariug@gmail.com
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr"><div><div>You are very welcome, <br></div>it is the work of Paul \
Ramsey though ;-)<br></div>Cheers,<br>Rémi-C<br></div><div \
class="gmail_extra"><br><div class="gmail_quote">2014-12-08 0:40 GMT+01:00 George \
Merticariu <span dir="ltr"><<a href="mailto:merticariug@gmail.com" \
target="_blank">merticariug@gmail.com</a>></span>:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">Hello Remi,<div><br></div><div>Your suggestion \
with point clouds worked perfectly. </div><span class=""><div><br></div><div>Thank \
you!</div><div><br></div><div>Best regards,</div><div>George</div></span></div><div \
class="gmail_extra"><div><div class="h5"><br><div class="gmail_quote">On Wed, Dec 3, \
2014 at 1:21 PM, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" \
target="_blank">remi.cura@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div><div><div><div>Hey,<br></div><div>I'm \
afraid you don't have given the most important information<br>, thus not allowing \
to choose between a value per row or a file per row!<br><br></div><div>So I discuss \
both :<br></div><div> first hypothesys : you'll have few files at a \
time,<br></div><div> - number of values is not very high (few \
millions)<br></div><div> - you want to get parts of the data based on index \
(1D,2D,3D) or maybe value<br></div><div>You will store each value of the file into a \
row.<br></div><div>Thus a file with a char array of size N will yield a table of N \
rows.<br></div><div>CREATE TABLE one_value_per_row (<br></div><div>gid int, \
--contains 1,2,..N , ordered by x,y,z<br>X int,<br></div><div>Y int,<br></div><div>Z \
int,<br></div><div>value smallint --or somehting like \
that<br></div><div>);<br></div><div> if you want your point ordered with original \
array index<br>SELECT *<br>FROM one_value_per_row<br></div><div>WHERE gid BETWEEN 2 \
AND 45; <br></div><div>More interesting, you can select 1-2-3D line/square/cube like \
this<br>SELECT *<br>FROM one_value_per_row<br>WHERE X BETWEEN 2 AND 3 <br></div><div> \
AND Y BETWEEN 56 AND 89<br></div><div> AND Z BETWEEN 21 AND \
42;<br><br></div><div>of course for this kind of querry to beb efficient, you need \
some index<br></div><div>CREATE INDEX ON one_value_per_row (gid);--the same for all \
columns<br></div><div><br></div><div>This approach __won't__ \
scale<br></div><div><br></div>Second hypothesis : you have a great many of files \
(over the hundred range)<br></div><div><b>you will store each file in one \
row.</b><br></div><div>Now you have at least 3 options.<br><div>* You store the data \
as a postgres type, the simplest is a postgres array<br></div> each row would \
contain a smallint[] (for instance)<br></div><div> This may not perform well \
with millions of values<br></div><div> You access your data like you would for \
a C char[] : value[35%12]<br></div><div> I think it is a pretty bad idea \
<br></div><div><br></div><div> * You ask the database to store the original binary, \
and you have to create C/python function in the database to access the \
data<br></div><div> (like \
get_values(Xmin=1,XMax=3,YMin=56,YMax=89,Zmin=21,ZMax=42))<br></div><div> this is \
an approach identical to the <a href="https://github.com/pgpointcloud/pointcloud" \
target="_blank">pg_pointcloud</a> project<br> For instance, your data could be \
stored as a 3D numpy array. slicing it would be then efficient and very easy (one \
line)<br></div><div> <br>* You don't want to redevelop, and use pg_pointcloud \
out of the box : <br> You consider that each value of your original array is \
a 3D point<br> Then each file is a point cloud stored on one row of the \
database.<br> 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<br></div><div> This is the easiest and most efficient \
out-of-the-box solution <br> (see <a \
href="http://boundlessgeo.com/wp-content/uploads/2013/10/pgpointcloud-foss4-2013.pdf" \
target="_blank">this introduction presentation</a> , and <a \
href="https://github.com/Remi-C/Postgres_Day_2014_10_RemiC/raw/master/presentation/A%20PostgreSQL%20Server%20for%20Point%20Cloud%20Storage%20and%20Processing.pdf" \
target="_blank">this simple to complex presentation</a> )<br></div> <br></div><div> \
you would get your data like this<br></div><div> SELECT \
pc_get(pt,'X'),pc_get(pt,'Y'),pc_get(pt,'Z'),pc_get(pt,'value'),</div><div>FROM \
one_file_per_row,<br> PC_FilterBetween(<br> PC_FilterBetween(<br> \
PC_FilterBetween(point_cloud, 'X',2,3)<br><div> \
,'Y',56,89)<br></div> ,'Z',21,42) as patch<br></div><div> \
, pc_explode(patch) AS pt<br></div><div>WHERE file_name = \
....<br><br><br></div><div>Choosing a solution depends on a lot of \
factors,<br></div><div>pondering performances, storage, usage, facility to create, \
facility ot maintain, etc \
etc.<br></div><div><br></div><div>Cheers,<br>Rémi-C<br></div></div></div><div><div><div \
class="gmail_extra"><br><div class="gmail_quote">2014-12-02 21:07 GMT+01:00 George \
Merticariu <span dir="ltr"><<a href="mailto:merticariug@gmail.com" \
target="_blank">merticariug@gmail.com</a>></span>:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">Hello!<div><br></div><div>Thank you for your \
answer! </div><div><br></div><div>Please find below more \
information:</div><div><br></div><div>The type of data which I will insert is an 1D \
array of values between 0 and 255. </div><div>This array should be mapped to \
different dimensions: </div><div>1D - grey string</div><div>2D - grey \
image</div><div>3d - grey cube</div><div><br></div><div>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.</div><div><br></div><div>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?</div><div><br></div><div>Best,</div><div>George <br></div></div><div \
class="gmail_extra"><div><div><br><div class="gmail_quote">On Tue, Nov 25, 2014 at \
5:56 PM, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" \
target="_blank">remi.cura@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div><div><div>Hey,<br>you don't even need \
postgis for this, you could store a cube per line,<br></div><div>with a x,y,z number, \
indexes on it,then querry like<br>SELECT *<br>FROM my_cube_table<br></div><div>WHERE \
x BETWEEN 100 AND 200<br></div><div>AND y BETWEEN 23 AND 45<br></div><div>AND z \
BETWEEN 45 AND 67<br></div><div>You could alos use postgres range \
type.<br></div><div>Of course you could store cube as meshes, and use pure 3D \
function (postgis so )<br></div><div>etc etc<br><br></div>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 \
...<br><br>Cheers,<br></div></div>Rémi-C<br></div><div class="gmail_extra"><br><div \
class="gmail_quote"><div><div>2014-11-25 17:32 GMT+01:00 George Merticariu <span \
dir="ltr"><<a href="mailto:merticariug@gmail.com" \
target="_blank">merticariug@gmail.com</a>></span>:<br></div></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div><div><div dir="ltr"><div dir="ltr" \
style="font-family:arial,sans-serif;font-size:13px">Hello!<div><br></div><div>I want \
to use PostGIS for handling 3D cubes but I couldn't figure out how to do it from \
the manual. </div><div><br></div><div>The main tasks I want to accomplish \
are:</div><div><br></div><div>1. Import a 1D char array file (grey cube) into a 3D \
cube.</div><div>2. Retrieve sections from the cube, where a section is defined by a \
domain.</div><div><br></div><div>Example:</div><div><br></div><div>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):</div><div><ul><li \
style="margin-left:15px"> [100:200, 100:200, 0:100]</li><li \
style="margin-left:15px"> [0:1, 0:1023, 0:1023] \
<br></li></ul></div><div><br></div><div>Is this possible using PostiGIS? If yes, are \
there any detailed tutorials which explain how to do \
that?</div><div><br></div><div>Thank you!</div><div><br></div><div>Best \
regards,</div><div>George Merticariu</div><div><br></div></div><div><div \
dir="ltr"></div></div> </div>
<br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br><br \
clear="all"><div><br></div></div></div><span><font color="#888888">-- <br><div><div \
dir="ltr">George Merticariu<div><br></div><div>Jacobs University \
Bremen</div><div>B.Sc. Computer Science </div><div>Class of \
2014</div><div><br></div><div><a href="mailto:g.merticariu@jacobs-university.de" \
target="_blank">g.merticariu@jacobs-university.de</a></div><div><a \
href="mailto:merticariug@gmail.com" \
target="_blank">merticariug@gmail.com</a></div></div></div> </font></span></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br><br \
clear="all"><div><br></div>-- <br></div></div><div><div dir="ltr"><div><div \
dir="ltr"><div><div class="h5">George Merticariu<div><br></div><div>Jacobs University \
Bremen</div></div></div><div>M.Sc. Computer Science </div><div>Class of \
2016</div><div><br></div><div><a href="mailto:g.merticariu@jacobs-university.de" \
target="_blank">g.merticariu@jacobs-university.de</a></div><div><a \
href="mailto:merticariug@gmail.com" \
target="_blank">merticariug@gmail.com</a></div></div></div></div></div> </div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic