# Creating 3D geometry column by combining existing 2D geometry column with "elevation" column?

I want to create a table that contains 3D data. I currently have 2D data stored as EWKT, which is multipoint geometry in one column, and another column that has elevation from sea level in another column. Is there a way that I can combine these two columns and output a single column with 3D data?

Example:

Geom column ("010400000001000000010100000000001038011323410000E9631AFC5641")

Elevation column ("1346.9495")

My goal is to combine the Geom column with the Elevation column, to create a 3D geometry column.

My thought is to possibly use subquerries, but I don't know how.

• could you add some more detail about that data, e.g. geometry type and how elevation data can be linked to it. all basic geometry types support Z dimension, and there are specific 3D object types. Commented Sep 10, 2019 at 9:40
• Possible duplicate of How to define Z dimension on table with PostGIS for import in TileMill?
– JGH
Commented Sep 10, 2019 at 11:41
• There is certainly a way. But it will change from one geometry type to another. You should provide more informations Commented Sep 10, 2019 at 12:46
• Is the 2D grid coincident with the elevations, or do you need to interpolate in some way. Commented Sep 10, 2019 at 14:08

With the elements we have here, I'll show a way to transform MultiPoints and Point geometrie and add the Z information in your geometry.

create table public.multi_test as (
SELECT ST_GeomFromText('MULTIPOINT((-71.160281 42.258729),(-71.160837 42.259113),(-71.161144 42.25932))') as geom,
1515 as elevation)

I will simulate your dataset as follow, geometry as MultiPoints geometry in your geom column and an "elevation" column with the Z information

At this point, you need to Dump your dataset to split your multipoints in single points and keep the z information at the same time. You can run the following

select St_asText((ST_Dump(multi_test.geom)).geom) as geom
from public.multi_test

Create table public.point_dump as (
select (ST_Dump(multi_test.geom)).geom as geom,
multi_test.elevation as elevation
from public.multi_test)

So we create a dataset of points with the z information in your column "elevation" from here we can make a new geometry with the Z information by extracting info from your geometry and adding the Z from the column. You can extract X and Y information from a geometry with ST_X, ST_Y. St_MakePoint will create the new point with the Z information

Create table public.points_3d as (
select St_MakePoint(ST_X(geom),ST_Y(geom),elevation) as geom
from public.point_dump)

select St_asText(geom) from public.points_3d

You can resume all these queries in a single query but as we don't have full information, I prefer to explain the way to go. Similar things are possible with Polygons and Linestrings.

Edit : with a CTE you can do this single query

Create table public.points_3d_v2 as (
with points as (
select
(ST_Dump(multi_test.geom)).geom as geom,
multi_test.elevation as elevation
from public.multi_test
)
select ST_MakePoint(ST_X(geom),ST_Y(geom),elevation) as geom,
elevation from points
)
• Thank you so much for your reply, it has been extremely useful in answering my question. One thing I would like to ask is how would I structure the query so that different values for elevation could be used. For a data set that is similar to: INSERT INTO public.multi_test (geom, elevation) VALUES (st_geomfromtext('LINESTRING(-140.654 20.78,-139.725 26.33,-139.754 32.916)'), 1350), (st_geomfromtext('LINESTRING(-141.342 22.978,-143.782 26.33,-144.633 34.936)'), 1350) Commented Sep 13, 2019 at 3:36
• Where there are two different elevation values? The data I am working with has multiple rows of either POINTS or LINESTRINGS, and each row has a different elevation. Commented Sep 13, 2019 at 3:37
• You should accept this answer as it answers your original question, as stated. If you have a different question around interpolation, please ask it. Commented Sep 13, 2019 at 13:31
• It does not answer my question though. I need to be able to insert different "elevation" values for different points. It answers part of the question, but entirely. Commented Sep 23, 2019 at 5:33
• what do you mean by different elevation values for different points ? if you have a column with any elevation values associated with the 2D points, it will do the trick. Commented Sep 24, 2019 at 15:02