10

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?

2
  • 2D LineString representing a road, that contains elevation - so that is 3D - use ST_Force_3D for your data - postgis.refractions.net/documentation/manual-1.5SVN/…
    – Mapperz
    Sep 25, 2012 at 13:37
  • A 0 z coordinate is not correct, and does not represent the same value as the data source. ST_Force_3D will not work for us. The idea is to be able to have a correct bidirectional mapping between the data source and our database.
    – relet
    Sep 26, 2012 at 8:04

2 Answers 2

3

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
1
  • Worth pointing out that 'nan' does not error but that #QNAN, which is a special nan for 32 and 64 bit numbers, does. We discovered a bizarre case where we had #QNAN in the M of a PointZM, and Geoserver was requesting the geom from Postgis using encode(ST_AsBinary(......, AS base64) with all kinds of resulting performance issues. Dec 8, 2021 at 8:52
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.