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 Answer
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) Jan 23, 2014 at 21:58