Had some spare time today so I used it to finally get around to a small project I had in mind. The intention is just to do it and not really to achieve a purpose if that makes sense.
The project I had in mind was to write a T-SQL code that would plot the trajectory of a point in space, it’s a simple physics problem we learn in school but I always wanted to try it out in SQL now that we have the geometry and geography datatypes.
Modified further it could be used to predict the path of a falling object in space taking into account wind direction , weight , drag and other parameters and could create a flight plan for the object. I envision one day we would device a cannon that could be used to shoot out humanitarian aid and have it land safely in the target area via parachutes. Much like artillery but this time using Sacks of Rice.
Here is the code and a screenshot of the output , the line segment represents the path of the parachute once deployed.
if exists (select 1 from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME like'%#trajectory%')
BEGIN
DROP TABLE #trajectory
END
create table #trajectory (
trackid int identity(1,1) ,
x varchar(128) ,
y varchar(128),
points geometry)
declare @v float = 1035.0 --- Initial velocity in m/s
declare @angle float = 45 --- Launch angle in degrees
declare @yo float = 0 --Initial height in meters
declare @intervals int = 500 -- number of points to be plotted
declare @d float -- place holder to store range value
declare @xmin varchar(128) = 78465.3 -- min range for parachute
declare @ymin varchar(128) = 22431.1 --min height for parachute
set @angle = @angle *0.01745329252
select @d= ((@v*cos(@angle))/9.8)*(@v*sin(@angle) + SQRT(Square(@v*sin(@angle))+ 2*9.8*@yo))
declare @step float = 0
while @step < = @d
BEGIN
declare @y float = @yo+ @step*tan(@angle) -((9.8*square(@step))/(2*square((@v*cos(@angle)))))
insert into #trajectory
select cast(@step as varchar(128)),cast( @y as varchar(128)),NULLset @step =@step +@d/@intervals
ENDupdate #trajectory
set points = geometry::Parse('POINT('+x+' '+y+' null null)')insert into #trajectory (points)
select geometry::STGeomFromText('LINESTRING('+@xmin+' '+@ymin+',' +@xmin+' 0.000, '+@xmin+' '+@ymin+')', 0)select x , y , points
from #trajectory--select geometry::STGeomFromText('LINESTRING('+cast(@xmin as varchar(20))+' '+ cast(@ymin as varchar(20))+','+ cast(@xmin as varchar(20))+' 0'')', 0);Here is a link to an older blog post where I had created my Company logo using the geometry data type.
PS:- this would be a cool way to avoid customs 🙂
Please Consider Subscribing
