How do you treat partial 3d features in PostGIS?

Question

We have features from survey data that contain partial 3d information.

The most common example would be a 2D LineString representing a road, that contains elevation information in certain points where it was surveyed. Other examples include roof shapes - A MultiLineString where some key points have an assigned elevation from the building plan, but not all.

Using PostGIS, which data model would you recommend to store this kind of information, to keep it as accessible as possible, without losing or generating interpolated information?

Answer 1

You could store the non-measured Z values as 'nan'::float8. For example:

SELECT ST_AsText(g), ST_X(g), ST_Y(g), ST_Z(g), ST_Z(g) <> 'nan'::float8 AS has_z
FROM (
  SELECT ST_MakePoint(1, 2, 'nan'::float8) AS g
  UNION SELECT ST_MakePoint(4, 5, 6) AS g
) AS f;

       st_astext       | st_x | st_y | st_z | has_z
-----------------------+------+------+------+-------
 POINT Z (1 2 1.#QNAN) |    1 |    2 |  NaN | f
 POINT Z (4 5 6)       |    4 |    5 |    6 | t
(2 rows)

However, this could get you in trouble since NaN values are not always tested or handled by software developers. E.g., PostGIS can't parse the WKT version of the above

SELECT 'POINT Z (1 2 1.#QNAN)'::geometry;

ERROR:  parse error - invalid geometry
LINE 1: SELECT 'POINT Z (1 2 1.#QNAN)'::geometry;
               ^
HINT:  "POINT Z (1 2 1.#Q" <-- parse error at position 17 within geometry

Answer 2

Create a secondary geometry column with three dimensions to hold the vertexes of linestring that has the three-ordinate( triple ) values. For this schema to work the following assumptions are asssumed:

• the linestring is valid, it does not contains duplicated points
• geometries are linestrings
• there must be at least two vertexes with 3d coordinates into a given geometry for it be elegible to be stored into a secondary geometry column
• a trigger will fill the secondary geometry column to keep it ACID.

The geometry been valid should be enough to not allow duplicated points in linestrings and no self intersection. So each coordinate will behave like a primery key to identify the vertex in source geometry.

This is correct from relational model too:

• there will be no redudance, vertex without info did not appear into secondary geometry column
• changes on source data will be propagated to derived data by the trigger.
• only information that is considered be the truth will be stored in database, no artificial data created.

For the multilinestring case things can be a little harder since now must have an additional table with a composite primary key been:

• the rowid( gid, a unique identifieer ) of source geometry
• the geometryN position inside the given MultiGeometry that must be check that is inside the interval [1-N]
• a foreing key to the related table rowid( gid )
• a trigger/check function to ensure the interval is valid

The primary key above will prevent inserts of duplicated geometry indexes for a given geometry. The trigger/check will prevent invalid indexes. Also rows here must be from source data given the foreign key. All previous rules applies.

An simplification would be the use of on additional column but not of kind geometry but of the same type of Z value declared as array.