3

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.

  • 1
    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. – ThingumaBob Sep 10 at 9:40
  • 1
  • There is certainly a way. But it will change from one geometry type to another. You should provide more informations – Maximilien jaffrès Sep 10 at 12:46
  • 1
    Is the 2D grid coincident with the elevations, or do you need to interpolate in some way. – John Powell Sep 10 at 14:08
  • 1
    Accept the correct answer if he has solved your question (activate the green checkmark) .. – Cyril Sep 13 at 13:23
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
)
  • 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) – Nic Pickers Sep 13 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. – Nic Pickers Sep 13 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. – John Powell Sep 13 at 13:31

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.