topic Re: Get sum of M-values using SQL in ArcGIS Pro Questions

Get sum of M-values using SQL

Bud — Fri, 31 Mar 2023 05:14:24 GMT

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?

Re: Get sum of M-values using SQL

Bud — Fri, 08 Apr 2022 11:09:55 GMT

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.

Re: Get sum of M-values using SQL

KimberlyGarbade — Fri, 08 Apr 2022 11:37:04 GMT

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 🙂

Re: Get sum of M-values using SQL

Bud — Fri, 08 Apr 2022 11:48:12 GMT

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.

Re: Get sum of M-values using SQL

KimberlyGarbade — Fri, 08 Apr 2022 19:14:02 GMT

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

result