3

I have a table of LineStringM geometries. Some of lines have points with same measure (M coordinate). This is a bug in initial data, that I want to clean somehow. So I need to: 1. Select all records, containing linestrings with vertexes having duplicating M values. 2. Correct theese values automatically, using M values from their neighbor vertexes. How can this be done?

1

To answer the first part of the question. Could be a one-liner, but I don't yet know how to add a counter to selection.

--Getting all vertexes with measures   
create table public.vertex_tab as
select
     lin_id 
     ,st_npoints(geom) as pnt_count
     ,generate_series(1, ST_NPoints(geom)) as pnt_num
     ,ST_PointN(geom, generate_series(1, ST_NPoints(geom))) as pnt_geom
     ,ST_M(ST_PointN(geom, generate_series(1, ST_NPoints(geom)))) as pnt_measure
     ,ST_X(ST_Transform(ST_PointN(geom, generate_series(1, ST_NPoints(geom))),4326)) as lon
     ,ST_Y(ST_Transform(ST_PointN(geom, generate_series(1, ST_NPoints(geom))),4326)) as lat
    from 
    public.lin_tab;

--Numbering vertexes
alter table public.vertex_tab add column gid serial not null;

--Selecting duplicates
select 
     a.gid
     ,a.lin_id
     ,a.pnt_geom
     ,a.pnt_measure
     ,a.pnt_num 
     ,a.lon
     ,a.lat
     ,b.gid as problem_with
     ,b.num as problem_num
from 
    public.vertex_tab as a
    ,public.vertex_tab as b
where 
    a.lin_id=b.lin_id and 
    NOT ST_Equals(a.pnt_geom, b.pnt_geom) and 
    a.gid!=b.gid  and --double check 
                      --another one is a.pnt_num!=b.pnt_num and
    a.pnt_measure=b.pnt_measure;
  • You can make this terser and faster using ST_DumpPoints() (also includes vertex identifying info) – Paul Ramsey Jan 23 '14 at 21:58

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.