Find when Vehicle reaches which landmark

1

I have two table, in one, there is vehicle movement record with lat-long & time

in another table , Landmark details present. Lat-long may not match, but it will be treaded as reached when distance between two lat-long is less than 50 meter . I want to find out that Vehicle reaches which landmark at which time.

1
1750×862 118 KB

Plz guide . Thanks

CREATE TABLE [dbo].[VehicleMovement](
[latitude] nvarchar NULL,
[longitude] nvarchar NULL,
[VehicleTime] [datetime] NULL,
[VehicleID] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[LandMarkMaster](
[LandmarkId] [int] NOT NULL,
[LandmarkName] nvarchar NULL,
[Lattitude] varchar NULL,
[Longitude] varchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[VehicleMovement] ([latitude], [longitude], [VehicleTime], [VehicleID]) VALUES (N'12.9932816', N'77.7002633', CAST(N'2022-02-09T05:50:09.000' AS DateTime), 276)
INSERT [dbo].[VehicleMovement] ([latitude], [longitude], [VehicleTime], [VehicleID]) VALUES (N'12.99085', N'77.6987766', CAST(N'2022-02-09T05:51:07.000' AS DateTime), 276)
INSERT [dbo].[VehicleMovement] ([latitude], [longitude], [VehicleTime], [VehicleID]) VALUES (N'12.991875', N'77.6941183', CAST(N'2022-02-09T05:51:58.000' AS DateTime), 276)
INSERT [dbo].[VehicleMovement] ([latitude], [longitude], [VehicleTime], [VehicleID]) VALUES (N'12.9932099', N'77.6907633', CAST(N'2022-02-09T05:52:42.000' AS DateTime), 276)
INSERT [dbo].[VehicleMovement] ([latitude], [longitude], [VehicleTime], [VehicleID]) VALUES (N'12.9948933', N'77.6873916', CAST(N'2022-02-09T05:53:22.000' AS DateTime), 276)

INSERT [dbo].[LandMarkMaster] ([LandmarkId], [LandmarkName], [Lattitude], [Longitude]) VALUES (9689, N'BTM 2nd stage Tank Shore Rd', N'12.9138557', N'77.6157581')
INSERT [dbo].[LandMarkMaster] ([LandmarkId], [LandmarkName], [Lattitude], [Longitude]) VALUES (9690, N'btm lake road mazid', N'12.908209', N'77.6137269')
INSERT [dbo].[LandMarkMaster] ([LandmarkId], [LandmarkName], [Lattitude], [Longitude]) VALUES (9691, N'junipar san Residency btm lake road', N'12.902102', N'77.6089058')

2

The mis-spelling of latitude suggests this might be homework.

The length of strings should always be specified. For latitude and longitude varchar(18) should do although I would personally hold them as decimal(18, 15).

The simplest approach is to use the distance between geography points.

With the data in your insert statements, none of the vehicles ever come anywhere close to 50m of a landmark.

WITH Vehicles
AS
(
	SELECT VehicleID, VehicleTime
		,VehiclePoint = geography::STPointFromText('POINT(' + Latitude + ' ' + Longitude + ')', 4326)
	FROM dbo.VehicleMovement
)
,LandMarks
AS
(
	SELECT LandmarkId, LandmarkName
		,LandmarkPoint = geography::STPointFromText('POINT(' + Latitude + ' ' + Longitude + ')', 4326)
	FROM dbo.LandMarkMaster
)
SELECT V.VehicleID, V.VehicleTime
	,L.LandmarkId, L.LandmarkName
	-- SRID 4326 uses a unit of meters.
	,DistanceInMeters = V.VehiclePoint.STDistance(L.LandmarkPoint)
FROM Vehicles V
	CROSS JOIN LandMarks L;
4

Thanks a lot for the code, excellent code