3

I am attempting to import linestring data with or without m values, then test whether the data has m values, then add m values to the geometry if they are missing. So far, I have working tests for the m values, and I can create the new geometry with m values included when using a select statement. However, I am trying to use this test in an alter table/alter column statement, which doesn't allow subqueries. I needed the subquery that I am using in order to convert a linestringz to a linestringm. I thought there would be a postgis function to swap coordinates between z and m, like st_force3dm(geom, st_z(geom)), which obviously doesn't work because st_z requires a point. Is there a way to swap a linestringz to linestringm using the z values as the new m values, and within an alter table statement?

This is the code I have so far. It tests whether m values exists, whether they are all 0, whether z values exist and whether they are all 0. If it finds m values that are not all 0, it returns the original geometry. If it finds no z or m values, or z and m values are all 0, it uses the geometry length to create m values. If it finds z values that are not all 0, it creates a linestringm using the z values as the m values. This last part doesn't work in an alter table statement because I have used a subquery to assign the z values to m values. I'm open to suggestions about better ways to achieve this task.

Doesn't work due to subquery in alter table:

alter table mt_altis_statewide_routes alter column geom type geometry(multilinestringm,4326) using 
    case when ((st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
        and ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_endpoint(geom)) is null) 
        then st_addmeasure(geom,0,length_miles(geom)) 
    when ((st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
        and (st_z(st_startpoint(geom)) != 0 or st_z(st_endpoint(geom)) != 0) and st_z(st_startpoint(geom)) is not null 
        then (select st_setsrid(st_makeline(st_makepointm(st_x(geom),st_y(geom),st_z(geom)) order by st_z(geom)),4326) from (select (st_dumppoints(geom)).geom) as f) 
    when ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_startpoint(geom)) is null) 
        and (st_m(st_startpoint(geom)) != 0 or st_m(st_endpoint(geom)) != 0) and st_m(st_startpoint(geom)) is not null 
        then geom end;

Works as select statement:

select 
    case when ((st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
        and ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_endpoint(geom)) is null) 
        then st_addmeasure(geom,0,length_miles(geom)) 
    when ((st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
        and (st_z(st_startpoint(geom)) != 0 or st_z(st_endpoint(geom)) != 0) and st_z(st_startpoint(geom)) is not null 
        then (select st_setsrid(st_makeline(st_makepointm(st_x(geom),st_y(geom),st_z(geom)) order by st_z(geom)),4326) from (select (st_dumppoints(geom)).geom) as f) 
    when ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_startpoint(geom)) is null) 
        and (st_m(st_startpoint(geom)) != 0 or st_m(st_endpoint(geom)) != 0) and st_m(st_startpoint(geom)) is not null 
        then geom end geom 
from mt_state_maintained_routes msmr ;

Also, both of these methods destroy the m values between the start and end, due to re-interpolating the values with st_addmeasure.

2
  • 1
    Just put your logic into a function and call that. Make sure it returns the full type definition, e.g. RETURNS GEOMETRY(MULTILINESTRINGM, 4326).
    – geozelot
    Jun 14, 2022 at 6:46
  • I suppose that the original geometry type is multilinestringZM because you need to analyze the coordinates, right?
    – user30184
    Jun 14, 2022 at 7:01

2 Answers 2

1

As said in the comments it would be necessary put your conversion code in a function like this:

CREATE OR REPLACE FUNCTION make_multilinestringm(geometry) RETURNS geometry(multilinestringm,4326) AS $$
DECLARE 
    geom geometry;  
BEGIN
    geom = $1;
   case  
       when (( st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
            and ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_endpoint(geom)) is null) 
            then return st_addmeasure(geom,0,length_miles(geom));        
       when ((st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
            and (st_z(st_startpoint(geom)) != 0 or st_z(st_endpoint(geom)) != 0) and st_z(st_startpoint(geom)) is not null 
            then return (select st_setsrid(st_makeline(st_makepointm(st_x(geom),st_y(geom),st_z(geom)) order by st_z(geom)),4326) from (select (st_dumppoints(geom)).geom) as f); 
       when ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_startpoint(geom)) is null) 
            and (st_m(st_startpoint(geom)) != 0 or st_m(st_endpoint(geom)) != 0) and st_m(st_startpoint(geom)) is not null 
            then return geom; 
    end case;    
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;

and then execute alter sentence using it

alter table mt_altis_statewide_routes alter column geom type geometry(multilinestringm,4326) using make_multilinestringm(geom);
1
  • I'm sorry, but if you use this function, it will destroy the m values you have between the start and end m values. They will be interpolated between the start and end m values and will change. This is not a valid solution.
    – jbalk
    May 10 at 17:38
0

I was able to solve this problem by first converting my geometry to 4D, which keeps the Z coordinates intact and provides the coordinate dimension required for the next step. I discovered that there is a postgis function to swap ordinates (St_SwapOrdinates). Chaining these together allowed me to swap m and z values for the lines, and it works in an alter table statement without the need for a custom function. This also keeps the original m values intact.

alter table mt_altis_statewide_routes alter column geom type geometry(multilinestringm,4326) using 
    case when ((st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
        and ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_endpoint(geom)) is null) 
        then st_addmeasure(geom,0,length_miles(geom)) 
    when ((st_m(st_startpoint(geom)) = 0 and st_m(st_endpoint(geom)) = 0) or st_m(st_startpoint(geom)) is null) 
        and (st_z(st_startpoint(geom)) != 0 or st_z(st_endpoint(geom)) != 0) and st_z(st_startpoint(geom)) is not null 
        then st_force3dm(st_swapordinates(st_force4d(geom),'zm')) 
    when ((st_z(st_startpoint(geom)) = 0 and st_z(st_endpoint(geom)) = 0) or st_z(st_startpoint(geom)) is null) 
        and (st_m(st_startpoint(geom)) != 0 or st_m(st_endpoint(geom)) != 0) and st_m(st_startpoint(geom)) is not null 
        then geom end;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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