SQL Server Spatial & ArcSDE Chris Ebright - Whiting Oil & Gas Corporation

1. SQL Server Spatial & ArcSDEChris Ebright - Whiting Oil & Gas Corporation

2. Whiting GIS Environment • SQL Server 2008 R2 • SQL Server Management Studio 10.50.2500.0 • ArcGIS Server 10.0 • ArcSDE10.0 • ArcGIS Desktop 10.0 • Python 2.6 (additional modules from standard library)

3. Why Use SQL Server Spatial Datatypes? • Existing Processing on large datasets too slow • Previous processing done with Toolbox and/or Python • Datasets being analized growing, cannot run all processes overnight • SQL Server procedures can be much faster if used correctly • 2) 3rd party access to spatial data • Infrastructure of multiple SQL Server Databases • Existing reports could access spatial data within existing procedures • Flexibility for future data access from unknown sources • 3) Whiting in-house expertise in SQL Server • Leverage in-house experience in Query optimization • Backup; Procedures are stored on server and can be understood by multiple employees. I am only Python programmer, no backup

4. Challenges of SQL Server Spatial Datatypes • Data Management is more complicated • No re-projection support with out of the box tools • (SQL Server queries only, ArcGIS will still handle re-projections) • Must manage OBJECTIDs if Inserting data • May require a new set of skills to interact in SQL Server environment if no SQL Server experience

5. Creating Feature Class Using SQL Server Geometry ArcCatalog– Configuration Keyword Only one step different than default feature class creation Drop down options will vary Based on which RDBMS is installed (SQL Server, Oracle, PostGIS, etc.)

6. GEOMETRY vs. GEOGRAPHY • GEOMETRY • Cartesian based • Can store Z and M values, but SQL Spatial functions do not use • ArcGIS will handle the Z and M values • Can be cast to GEOGRAPHY for crucial calculations (large features) • More ST functions • GEOGRAPHY • Ellipsoidal based • Can store Z and M values, but SQL Spatial functions do not use • ArcGIS will NOT handle Z and M values (will error on feature class creation)

7. Creating Feature Class Using SQL Server SDE Binary SQL Server MGMT Studio – Business Table Query Results

8. Creating Feature Class Using SQL Server SDE Binary SQL Server MGMT Studio – SDE Geometry Lookup LINESTRING_SDE_BINARY joins to f1203 to get spatial data

9. Creating Feature Class Using SQL Server SDE Binary SQL Server MGMT Studio – SDE Feature Table Query Results LINESTRING_SDE_BINARY joins to f1203 to get spatial data

10. Creating Feature Class Using SQL Server Geometry SQL Server MGMT Studio – Query Results LINESTRING_GEOMETRY has spatial data in business table

11. Creating Feature Class Using SQL Server Geometry SQL Server MGMT Studio - Spatial Results Tab SQL Server MGMT Studio shows spatial results for SQL Geometries

12. Editing Feature Class Using SQL Server Geometry ArcMap - Editing

13. Notes on Data Management in SQL Server Lessons Learned Along the Way • If Adding data to feature class, must manage OBJECTIDs • Must update feature class extents in sde_layers table • Layer will plot in ArcMap, but may not display in Map Service • Must update sde_column_registry table if altering table schema in SQL Server • Recommend doing schema updates through ArcGIS interface

14. What has been tested • Tested • Editing in ArcMap with no additional versions • Adding/Updating features from SQL Server side • Creating feature class in ArcGIS first, using configuration keyword • Not Tested • Editing in ArcMap with multiple versions • Advanced geodatabase functionality: Topology, Networks, etc. • Creating table in SQL Server first and registering with SDE command line

15. SQL Server Spatial Queries Examples • Example: • Non ArcGIS spatial data access • Scenario: • Users managing wellborepaths in ArcMap. • Other users use spatial attributes in SQL Server.

16. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries Wellbores – PolylineZM Feature Class stored in SQL Server GEOMETRY 3D View

17. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries SELECT UWI ,SHAPE FROM database.dbo.PATHS WHERE UWI LIKE '3306100513%'

18. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries SELECT UWI ,SHAPE ,SHAPE.STAsText() AS LINESTRING_TEXT FROM database.dbo.PATHS WHERE UWI LIKE '3306100513%'

19. OGC Well Known Text POINT WKT: POINT(-100.5 44.1) GEOMETRY: STGeomFromText( ‘POINT(-100.5 44.1)’, 4267 ) LINESTRING WKT: LINESTRING(-100.5 44.1, -100.6 44.05, -100.7 44.0) GEOMETRY: STGeomFromText( ‘LINESTRING(-100.5 44.1 , -100.6 44.05, -100.7 44.0)’, 4267 ) POLYGON WKT: POLYGON((-100.5 44.1, -100.0 44.1, -100.0 43.8, -100.5 43.8, -100.5 44.1)) GEOMETRY: STGeomFromText( ‘POLYGON((-100.5 44.1, -100.0 44.1, -100.0 43.8, -100.5 43.8, -100.5 44.1))’, 4267 )

20. SQL Server 2008 R2 OGC Methods (GEOMETRY) STArea STAsBinary STAsText STBoundary STBuffer STCentroid STContains STConvexHull STCrosses STDifference STDimension STDisjoint STDistance STEndpoint STEnvelope STEquals STExteriorRing STGeometryN STGeometryType STInteriorRingN STIntersection STIntersects STIsClosed STIsEmpty STIsRing STIsSimple STIsValid STLength STNumGeometries STNumInteriorRing STNumPoints STOverlaps STPointN STPointOnSurface STRelate STSrid STStartPoint STSymDifference STTouches STUnion STWithin STX STY

21. SQL Server 2008 R2 Extended Methods AsGml(geometry Data Type) AsTextZM(geometry Data Type) BufferWithTolerance(geometry Data Type) InstanceOf(geometry Data Type) Filter (geometry Data Type) IsNull(geometry Data Type) M (geometry Data Type) MakeValid (geometry Data Type) Reduce (geometry Data Type) ToString (geometry Data Type) Z (geometry Data Type)

22. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries SELECT UWI ,SHAPE.AsTextZM() AS LINESTRING_TEXT_ZM FROM database.dbo.PATHS WHERE UWI LIKE '3306100513%' Z and M Values

23. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries SELECT UWI ,SHAPE ,SHAPE.STLength() AS Length ,SHAPE.STNumPoints() AS Num_Points ,SHAPE.STStartPoint() AS SHL ,SHAPE.STEndPoint() AS BHL FROM database.dbo.PATHS WHERE UWI LIKE '3306100513%' Native Unit of Geometry (Degrees) (only 2D length, Z value not used) Binary Represenation of Geometry

24. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries SELECT UWI ,SHAPE ,CAST(geography::STGeomFromText(SHAPE.STAsText(),SHAPE.STSrid).STLength() * 3.28084 AS INT) AS Length_Feet ,SHAPE.STNumPoints() AS Num_Points ,SHAPE.STStartPoint().STX AS SHL_LON ,SHAPE.STStartPoint().STY AS SHL_LAT ,SHAPE.STEndPoint().STX AS BHL_LON ,SHAPE.STEndPoint().STY AS BHL_LAT FROM database.dbo.PATHS WHERE UWI LIKE '3306100513%' Conversion to feet using Using GEOGRAPHY datatype Float represenation of coordinates

25. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries SELECT UWI ,SHAPE ,CAST(geography::STGeomFromText(SHAPE.STAsText(),SHAPE.STSrid).STLength() * 3.28084 AS INT) AS Length_Feet ,SHAPE.STEndPoint().M AS Total_Depth ,CAST(SHAPE.STEndPoint().Z AS INT) AS TVDSS_at_TD ,SHAPE.STNumPoints() AS Num_Points FROM database.dbo.PATHS WHERE UWI LIKE '3306100513%' M value storing MD value Z value storing TVDSS value Length Calc without Z input

26. SQL Server Spatial Queries Examples – SQL Server MGMT Studio Queries SELECT t1.[UWI] ,t1.SHAPE.STAsText() AS WellBore_Geometry ,t2.SHAPE.STAsText() AS Basin_Geometry ,t2.Name AS Basin_Name FROM database.dbo.PATHS AS t1 JOIN database.dbo.BASINSAS t2 ON t1.SHAPE.STIntersects(t2.SHAPE) = 1 WHERE UWI LIKE '3306100513%' Attribute of Intersected Geometry

27. SQL Server Spatial Queries Examples – Simple Well Paths Example: Simple wellbore path from SHL and BHL Python vs. SQL Server

28. SQL Server Spatial Queries Examples – Simple Well Paths

29. Python Script ~336,000 wells in 48 min. import myLogger import urllib2 import datetime import time import arcpy import sys import os import math fileAbsolutePath = os.path.abspath(__file__) myLog = myLogger.Log(fileAbsolutePath) myLog.reportStart() arcpy.env.overwriteOutput = 1 #Set workspace arcpy.env.workspace = r'DatabaseConnections\server.database.sde' env = arcpy.env.workspace fc_well_paths = r'DatabaseConnections\server.database.sde\database.DBO.WELL_PATHS' #Check if feature already exists, and if so, delete if arcpy.Exists(fc_well_paths): print "exists" #arcpy.Delete_management(fc) arcpy.DeleteFeatures_management(fc_well_paths) else: print "doesn't exist" #Create spatial reference sr = arcpy.SpatialReference() sr.factoryCode = 4267 #Create feature class based on template arcpy.CreateFeatureclass_management(env, fc_well_paths, 'POLYLINE', '', '', '', sr) if len(arcpy.Describe(fc_well_paths).fields) > 2: pass else: arcpy.AddField_management(fc_well_paths, 'UWI', 'text') arcpy.AddField_management(fc_well_paths, 'WELL_NAME', 'text') #Create Search cursor on source table SearchCursor = arcpy.SearchCursor(r'DatabaseConnections\server.database2sde\database2.dbo.WELLS_SURF', \ 'BOTTOM_HOLE_LATITUDE IS NOT NULL AND \ BOTTOM_HOLE_LONGITUDE IS NOT NULL AND \ BOTTOM_HOLE_LONGITUDE <> 0 AND \ BOTTOM_HOLE_LATITUDE <> 0 AND \ SURFACE_LONGITUDE <> 0 AND \ SURFACE_LATITUDE <> 0 AND \ BOTTOM_HOLE_LATITUDE <> SURFACE_LATITUDE AND \ BOTTOM_HOLE_LONGITUDE <> SURFACE_LONGITUDE', '', '', '') #Create Insert cursor on destination table InsertCursor = arcpy.InsertCursor(fc_well_paths) ic_row = InsertCursor.newRow() pointSHL = arcpy.Point() pointBHL = arcpy.Point() pointArray = arcpy.Array() try: for row in SearchCursor: try: ic_row.setValue('UWI', row.UWI) if row.WELL_NAME == None: ic_row.setValue('WELL_NAME', '') elifrow.WELL_NUMBER == None: if row.WELL_NAME == None: ic_row.setValue('WELL_NAME', '') else: ic_row.setValue('WELL_NAME', row.WELL_NAME) else: ic_row.setValue('WELL_NAME', str(row.WELL_NAME) + ' ' + str(row.WELL_NUMBER)) pointArray.removeAll() pointSHL.X = row.getValue('SURFACE_LONGITUDE') pointSHL.Y = row.getValue('SURFACE_LATITUDE') pointBHL.X = row.getValue('BOTTOM_HOLE_LONGITUDE') pointBHL.Y = row.getValue('BOTTOM_HOLE_LATITUDE') pointArray.add(pointSHL) pointArray.add(pointBHL) newLine = arcpy.Polyline(pointArray) ic_row.setValue('SHAPE', newLine) InsertCursor.insertRow(ic_row) except Exception, e: myLog.reportError(e) del InsertCursor, ic_row, pointSHL, pointBHL, pointArray except Exception, e: myLog.reportError(e) myLog.reportToDatabase('FALSE', e) #sys.exit() arcpy Cursors and Loops SLOWER

30. SQL Server Script ~336,000 wells in 2 min. DELETE FROM database.dbo.PATHS_SIMPLE INSERT INTO database.dbo.PATHS_SIMPLE( OBJECTID ,SHAPE ,WELL_NAME ) ( SELECT ROW_NUMBER() OVER(ORDER BY COMPLETION_DATE) ,geometry::STGeomFromText( 'LINESTRING(' + STR(SURFACE_LONGITUDE, 25, 25) + ' ' + STR(SURFACE_LATITUDE, 25, 25) + ',' + STR(BOTTOM_HOLE_LONGITUDE, 25, 25) + ' ' + STR(BOTTOM_HOLE_LATITUDE, 25, 25) + ')' , 4267) ,WELL_NAME + ' ' + WELL_NUM FROM database2.dbo.WELL WHERE BOTTOM_HOLE_LATITUDE IS NOT NULL AND BOTTOM_HOLE_LONGITUDE IS NOT NULL AND BOTTOM_HOLE_LONGITUDE <> 0 AND BOTTOM_HOLE_LATITUDE <> 0 AND SURFACE_LONGITUDE <> 0 AND SURFACE_LATITUDE <> 0 AND BOTTOM_HOLE_LATITUDE <> SURFACE_LATITUDE AND BOTTOM_HOLE_LONGITUDE <> SURFACE_LONGITUDE ) Set based SQL Query FASTER

31. Python Integration Code Snippets import pyodbc Open Connection to SQL Server table conn = pyodbc.connect('DRIVER={SQL Server};SERVER=<server>;DATABASE=<database>') c = conn.cursor() Execute SELECT statement on table cursor.execute('SELECT MAX(OBJECTID) FROM featureClass') oid= cursor.fetchall()[0][0] Execute SELECT statement on table cursor.execute('SELECT COUNT(*) FROM featureClassWHERE ID = ?', var_id) count = cursor.fetchall()[0][0] Execute INSERT statement on table sql= "INSERT INTO featureClass(OBJECTID, SHAPE) (SELECT %d, %s)" % (oid, geometry) cursor.execute(sql) conn.commit() Execute UPDATE statement on table cursor.execute("UPDATE featureClassSET ID = oid+ '_' + subID") conn.commit()

32. SQL Server Spatial Queries Examples – Spatial Analysis for Wellbores in Fields

33. SQL Server Spatial Queries Examples – View From Spatial Query SELECT t1.UWI ,t1.SHAPE.STStartPoint().STY AS SHL_LAT ,t1.SHAPE.STStartPoint().STX AS SHL_LON ,t1.SHAPE.STEndPoint().STY AS BHL_LAT ,t1.SHAPE.STEndPoint().STX AS BHL_LON ,t1.SHAPE.STEndPoint().M AS TD ,t1.SHAPE.STEndPoint().Z AS TVDSS_TD ,t2.name AS Basin ,t3.name AS Field FROM database.dbo.PATHSAS t1 JOIN database.dbo.BASINSAS t2 ON t1.SHAPE.STIntersects(t2.SHAPE) = 1 JOIN database.dbo.FIELDSAS t3 ON t1.SHAPE.STIntersects(t3.SHAPE) = 1 WHERE t1.UWI LIKE '%33061%' Dynamic Intersect of wellbores and field outlines

34. SQL Server Spatial Queries Examples – Excel Linking to SQL Server view Dynamic link to SQL Server View Updates on feature class update

35. SQL Server Spatial Queries Examples – View From Spatial Query SELECT t2.name AS Field ,COUNT(t1.SHAPE) AS Total_Wellbores ,MAX(CAST(geography::STGeomFromText(t1.SHAPE.STAsText(),t1.SHAPE.STSrid).STLength() * 3.28084 AS INT) ) AS Longest_Wellbore ,AVG(CAST(geography::STGeomFromText(t1.SHAPE.STAsText(),t1.SHAPE.STSrid).STLength() * 3.28084 AS INT) ) AS Avg_Wellbore ,SUM(t1.SHAPE.STEndPoint().M) AS Total_Feet_Drilled FROM database.dbo.PATHSAS t1 JOIN database.dbo. FIELDS AS t2 ON t1.SHAPE.STIntersects(t2.SHAPE) = 1 WHERE t1.UWI LIKE '%33061%' GROUP BY t2.name ORDER BY Avg_Wellbore DESC

36. SQL Server Spatial Queries Examples – Excel Linking to SQL Server view Dynamic link to SQL Server View Updates on feature class update

37. Find Psuedo Midpoint of Linestring Update Procedure ;WITH PATHS_CTE(rownumber ,UWI ,ROW_CHANGED_DATE ,midpoint ) AS ( SELECT ROW_NUMBER() OVER(ORDER BY UWI) ,[UWI] ,ROW_CHANGED_DATE ,SHAPE.STEnvelope().STCentroid().STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001).STIntersection(SHAPE).STStartPoint() AS midpoint FROM database.dbo.PATHS WHERE ROW_CHANGED_DATE > @maxDate ) MERGE INTO database.dbo.PATHS_MIDPOINTAS t1 USING PATHS_CTE AS t2 ON t2.UWI = t1.UWI WHEN MATCHED THEN UPDATE SET UWI = t2.UWI ,t1.ROW_CHANGED_DATE = GETDATE() ,t1.SHAPE = t2.midpoint WHEN NOT MATCHED THEN INSERT ( [OBJECTID] ,[UWI] ,ROW_CHANGED_DATE ,[SHAPE] ) VALUES ( rownumber+ @oid ,[UWI] ,GETDATE() ,midpoint ) ; Using MERGE statement allows efficient updating and inserting of data into feature classes in one process by filtering on ROW_CHANGED_DATE

38. SQL Server Spatial Queries Examples Example: True Midpoint on Line (User defined function) vs. Pseudo Midpoint on Line (pure ST Functions) (useful for plotting wellbore specific attributes) WORK IN PROGRESS

39. SQL Server Spatial Queries Symbolizing Production Plotted at Surface Hole Location Production Plotted at Wellbore Midpoint

40. SQL Server Non-Spatial Queries SQL Server MGMT Studio – Midpoint on Linestring USE database IF OBJECT_ID('tempdb..#NDIC_HORZ_LIST') IS NOT NULL DROP TABLE #NDIC_HORZ_LIST SET NOCOUNT OFF -- Create temp table to hold the OBJECTID and apply filter on wells without enough points SELECT lineID = identity (int,1,1), OBJECTID INTO #NDIC_HORZ_LIST FROM database.dbo.API12_PATHS CREATE CLUSTERED INDEX idx_NDIC_HORZ_LIST_lineid ON #NDIC_HORZ_LIST(lineID) CREATE INDEX idx_NDIC_HORZ_LIST_objectid ON #NDIC_HORZ_LIST(OBJECTID) DECLARE @midPoint geometry DECLARE @point1X numeric(25,20) DECLARE @point1Y numeric(25,20) DECLARE @point2X numeric(25,20) DECLARE @point2Y numeric(25,20) DECLARE @xDiff numeric(25,20) DECLARE @yDiff numeric(25,20) DECLARE @workingLineLength1 numeric(25,20) = 0 DECLARE @workingLineLength2 numeric(25,20) = 0 DECLARE @lineLength numeric(25,20) DECLARE @iint DECLARE @lineIDint DECLARE @lineOID INT DECLARE @line geometry DECLARE @workLine geometry DECLARE @lastSegment geometry DECLARE @lineFromString NVARCHAR(MAX) SET @lineID = 1 -- Outer loop to iterate over lines in table, using the counts from the temp table WHILE @lineID <= (SELECT COUNT(*) FROM #NDIC_HORZ_LIST) BEGIN -- Assign OBJECTID FROM Temp table to variable SET @lineOID = (SELECT OBJECTID FROM #NDIC_HORZ_LIST WHERE lineID = @lineID) -- Assign SHAPE From table to geometry variable SET @line = (SELECT SHAPE FROM database.dbo.API12_PATHS WHERE OBJECTID = @lineOID) SET @lineLength = @line.STLength() / 2 SET @lineFromString = 'LINESTRING(' SET @i = 1 -- Loop through points in line until the working length is longer than half the line length WHILE @workingLineLength2 < @lineLength BEGIN -- For first point add start point from line IF @i = 1 BEGIN SET @lineFromString = @lineFromString + CAST(@line.STStartPoint().STX AS VARCHAR(10)) + ' ' + CAST(@line.STStartPoint().STY AS VARCHAR(10)) + ')' END ELSE BEGIN -- Strip the trailing ) from the line string text SET @lineFromString = LEFT(@lineFromString, LEN(@lineFromString) - 1) SET @lineFromString = @lineFromString + ',' + CAST(@line.STPointN(@i).STX AS VARCHAR(10)) + ' ' + CAST(@line.STPointN(@i).STY AS VARCHAR(10)) + ')' SET @workLine = geometry::STGeomFromText(@lineFromString, 4267) SET @lastSegment = geometry::STGeomFromText('LINESTRING(' + CAST(@line.STPointN(@i - 1).STX AS VARCHAR(10)) + ' ' + CAST(@line.STPointN(@i - 1).STY AS VARCHAR(10)) + ',' + CAST(@line.STPointN(@i).STX AS VARCHAR(10)) + ' ' + CAST(@line.STPointN(@i).STY AS VARCHAR(10)) + ')', 4267) SET @workingLineLength2 = @workLine.STLength() IF @workingLineLength2 > @lineLength BEGIN SET @point1X = @line.STPointN(@i - 1).STX SET @point1Y = @line.STPointN(@i - 1).STY SET @point2X = @line.STPointN(@i).STX SET @point2Y = @line.STPointN(@i).STY SET @xDiff = (@point2X - @point1X) * ((@lineLength - @workingLineLength1) / @lastSegment.STLength()) SET @yDiff = (@point2Y - @point1Y) * ((@lineLength - @workingLineLength1) / @lastSegment.STLength()) SET @midPoint = geometry::STGeomFromText('POINT(' + CAST(@point1X + @xDiff AS VARCHAR(25)) + ' ' + CAST(@point1Y + @yDiff AS VARCHAR(25)) + ')',4267) INSERT INTO database.dbo.API10_SHL(OBJECTID,SHAPE) SELECT ROW_NUMBER() OVER(ORDER BY t2.OBJECTID), @midPoint FROM database.dbo.API12_PATHS AS t2 END SET @workingLineLength1 = @workingLineLength2 END SET @i = @i + 1 END SET @lineID = @lineID + 1 END Multiple While Loop Operations (analogous to Python function) SLOWER Loop 1 Loop 2

41. SQL Server Non-Spatial Queries SQL Server MGMT Studio – Midpoint on Linestring Add each line segment until half line length + + + + + +

42. SQL Server Spatial Queries SQL Server MGMT Studio – Psuedo Midpoint on Linestring Set Based Operation FASTER SELECT SHAPE.STEnvelope(). STCentroid(). STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001). STIntersection(SHAPE). STStartPoint() FROM WELL_PATHS

43. Find Psuedo Midpoint of Linestring SELECT SHAPE.STEnvelope()

44. Find Psuedo Midpoint of Linestring SELECT SHAPE.STEnvelope(). STCentroid()

45. Find Psuedo Midpoint of Linestring SELECT SHAPE.STEnvelope(). STCentroid(). STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001)

46. Find Psuedo Midpoint of Linestring SELECT SHAPE.STEnvelope(). STCentroid(). STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001). STIntersection(SHAPE)

47. Find Psuedo Midpoint of Linestring SELECT SHAPE.STEnvelope(). STCentroid(). STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001). STIntersection(SHAPE). STStartPoint()

48. Find Psuedo Midpoint of Linestring Statistical Analysis STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001) Hard Coded Number -analyze validity End Point Start Point Distance of Intersection Intersection Buffer Polygon Well Path

49. Find Psuedo Midpoint of Linestring Statistical Analysis ;WITHCTE_PATHS_STATS(dist) AS ( SELECT geography::STGeomFromWKB( SHAPE.STEnvelope().STCentroid().STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001).STIntersection(SHAPE).STStartPoint().STAsBinary(),4267) .STDistance( geography::STGeomFromWKB( SHAPE.STEnvelope().STCentroid().STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000001).STIntersection(SHAPE).STEndPoint().STAsBinary(), 4267) ) FROM database.dbo.PATHS ) SELECT MAX(dist) AS max_dist_meter ,MIN(dist) AS min_dist_meter ,AVG(dist) AS avg_dist_meter ,COUNT(dist) AS count_dist ,(SELECT COUNT(*) FROM PATHS_STATS) AS count_total ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 1) AS GreaterThan1m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 5) AS GreaterThan5m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 10) AS GreaterThan10m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 25) AS GreaterThan25m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 50) AS GreaterThan50m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 100) AS GreaterThan100m FROM CTE_PATHS_STATS

50. Find Psuedo Midpoint of Linestring Statistical Analysis ;WITHCTE_PATHS_STATS(dist) AS ( SELECT geography::STGeomFromWKB( SHAPE.STEnvelope().STCentroid().STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000005).STIntersection(SHAPE).STStartPoint().STAsBinary(),4267) .STDistance( geography::STGeomFromWKB( SHAPE.STEnvelope().STCentroid().STBuffer(SHAPE.STEnvelope().STCentroid().STDistance(SHAPE) + .000005).STIntersection(SHAPE).STEndPoint().STAsBinary(), 4267) ) FROM database.dbo.PATHS ) SELECT MAX(dist) AS max_dist_meter ,MIN(dist) AS min_dist_meter ,AVG(dist) AS avg_dist_meter ,COUNT(dist) AS count_dist ,(SELECT COUNT(*) FROM PATHS_STATS) AS count_total ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 1) AS GreaterThan1m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 5) AS GreaterThan5m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 10) AS GreaterThan10m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 25) AS GreaterThan25m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 50) AS GreaterThan50m ,(SELECT COUNT(dist) FROM PATHS_STATS WHERE dist > 100) AS GreaterThan100m FROM CTE_PATHS_STATS