[postgis-users] Is there a generic function to find coincident vertex?

[Simon SPDBA Greener]

Here is a simple wrapper SQL function.

It checks that the geometry type is a LineString. Other types could be 
supported by extension of the SQL.

create or replace function STNumDuplicateVertices( p_geometry geometry, 
p_tolerance numeric )
returns integer
language sql
as
$$
select SUM(isDuplicate) as duplicateCount
   from (select case when 
ST_Distance(ST_PointN(p_geometry,b.*),lead(ST_PointN(p_geometry,b.*)) 
over (order by b.*)) < p_tolerance
                     then 1
                     else 0
                 end as isDuplicate
           from generate_series(1,ST_NumPoints(p_geometry),1) as b
         where ST_GeometryType(p_geometry) = 'ST_LineString'
        ) as f;
$$

select STNumDuplicateVertices(ST_GeomFromText('LINESTRING(0 0 1.1,1 
1.001 1.2,1 1.0005 1.3,1 1 1.1,2 2 1.2)',0),0.01);
select STNumDuplicateVertices(ST_GeomFromText('POLYGON((0 0,1 0,1 0,1 
1,0 1,0 0))',0),0.01);