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

Question

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.

I am using pgAdmin 4.

Answer 1

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
)