Calculate distance/length of linestring

1

Hi, first post . My SQL skills are average but mostly adequate for what I need, except for now.
Currently I export data from SQL DB to Mapinfo and do length calc in MAPINFO. But I now wish to do this length calc in the database to negate the MAPINFO requirement for the end user.
I have a simple view created using statement below

SELECT TOP (100) PERCENT ItemDescription, sNotes, [STREET ADDRESS], [HIGH RISK], REVISIT_REQUIRED, ACCESS, ACTIVITY, NOTES, [USER], Date, GEOMETRYWKT, ITMGEOMETRY, ITMCENTROID, sStatus, [Asset No]
FROM dbo.v_mosi_ITM_Visit
WHERE (ACTIVITY <> 'dummy') AND (GEOMETRYWKT LIKE 'line%')
ORDER BY Date DESC

It returns records that are linestrings. What I need is to calculate the length of the linestrings using column GEOMETRYWKT (or column ITMGEOMETRY, both are identical) and add a cloumn to store that result. I have tried adding in STLength() to the statement above but it returns error "STLength is not a recognised built in Function name". I'm sure this task isn't to tricky, but I'm a bit lost as to where the statement needs to be and also adding column for results.

Any assistance appreciated.

2

It might be

SELECT GEOMETRYWKT.STLength()
FROM ...
4

What prompt did you use in Copilot that resulted in this irrelevant response?

2 Likes
5

Hi, Thanks. Tried this below, but no luck. Error was - "Cannot find either column "GEOMETRYWKT" or the user-defined function or aggregate "GEOMETRYWKT.STLength", or the name is ambiguous"

SELECT TOP (100) PERCENT GEOMETRYWKT, GEOMETRYWKT.STLength() AS SprayDist
FROM dbo.v_mosi_ITM_Visit
WHERE (ACTIVITY <> 'dummy') AND (GEOMETRYWKT LIKE 'line%')
ORDER BY Date DESC

Thanks

6

Does this test work for you?

CREATE TABLE #test (wkt geometry);

INSERT INTO #test (wkt) 
  VALUES (geometry::STGeomFromText('LINESTRING (10 100, 15 200, 20 300)', 0))

SELECT * FROM #test;

SELECT *, wkt.STLength() AS Length FROM #test;
7

Yes, that works fine and returns answer of 200.24984.............and 2 columns wkt & Lenght
Just leaving work now. I think my answer is in there somewhere. Will give it a go tomorrow.
Thank you again.

8

Hi SQL Hippo, sincere apologies, I should have been using column of ITMGEOMETRY not GEOMETRYWKT. Your very first suggestion works perfectly if I use the correct column.
Thank you again.

9

Glad you got it worked out!