Search code examples
postgresqlpostgis

store array of 3d points in postgresql

I have to store logical 3d coordiantes of object in postgres database. Each object typicaly has from 50-1000 points and probably never exceed 10000. My intension is to use column of type real [][] in postgres.

I looked also postGis extension and wonder if it is suitable solution, but could not answer myself of several questions:

  1. Which spatial reference should i use - only need logical coordinates x,y,z could i specify left or right coordinate system - this is the part that mostly confuses me? 2.How should orgnaize data - line geometry seems natural way to me?
  2. Would be posible to find distance between two points in the array (line geometry)?
Solution

  • It would be natural to use the PostGIS geometry(pointz)[] as data type, an array of three-dimensional points.

    Here is an example that shows a constant of that type and calculates the distance between the points:

    WITH x(p) AS (
   SELECT '{POINT Z (1 2 3):POINT Z (3 0 2)}'::geometry(pointz)[]
)
SELECT st_3ddistance(p[1], p[2]) FROM x;

 st_3ddistance 
---------------
             3
(1 row)