I have multipart polylines in an ST_GEOMETRY feature class (in an Oracle 18c EGDB).
Using SQL, I want to get the sum of the M-values of each polyline.
For example, for the following multipart line, the calculation would be: 11.18 + 33.54 + 43.54 = 88.26
(The M-sum is not to be confused with the length of the line.)
How can I do that using SQL?
Reason: it’s a possible simplistic way to check M-values for issues:
Find polyline vertices where M-value is not cumulative length
The thought being: I could check the existing M-sum vs. the theoretical M-sum. If there is a difference, then that would tell me I have incorrect M-values.
Not near a computer but I would think something like
Select sum(geometey.M) FROM FeatureTable GROUP BY PolylineID
But, like I said, haven't tested it and it in SQL Server speak 🙂
In SQL Server, does “geometey.M” generate individual rows for each vertex/M-value?
I’m not aware of a simple way to generate rows for each vertex…when it comes to SDE.ST_GEOMETRY in Oracle. We don’t have a GetVertices function, etc.
Sorry. You are correct. That does not do what you want (should have tested it).
I had to write a customized function (I had to alter the original so ALTER rather than CREATE is shown):
ALTER FUNCTION [GISDC].[TotalM] (@geom geometry) RETURNS FLOAT WITH EXECUTE AS CALLER AS BEGIN DECLARE @x integer; DECLARE @y integer; DECLARE @z float; DECLARE @q float; set @x = @geom.STNumPoints(); set @y = 1; set @q = 0 WHILE @y <= @x BEGIN set @z = convert(float, geometry::STGeomFromText(@geom.STPointN(@y).ToString(),0).M); set @q = @q + @z set @y = @y + 1 END; RETURN(@q); END; GO
Input test line