Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
2D LineString representing a road, that contains elevation - so that is 3D - use ST_Force_3D for your data -… – Mapperz Sep 25 '12 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 '12 at 8:04

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.

share|improve this answer

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
  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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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