All GPS within radius

1

Hello everyone,

I have a view containing latitude and longitude but no geography column upon which to do SQL commands. I am not able to add a column to this already existing table, so I create a temporary table and import the pertinent data.

select perno, latitude,longitude into gpstemp from avl.vwsc_DeviceLatLongHistory
where perno = 'ECSO02PER000093' and updatedatetime > '4/30/2018' and updatedatetime < '5/10/2018'

alter table gpstemp add gpsnew as geography::STGeomFromText('POINT('+convert(varchar(20),Longitude)+' '+convert(varchar(20),Latitude)+')',4326)

This all works correctly. I can query the table afterwards and see the data showing up:

image

But this is where I'm now unsure. I need to query this table and return all coordinates that are within 5 miles of a certain location. I set my variables, and then execute my query. But I get an error stating 'Could not find property of field 'STDistant' for type 'Microsoft.SQLServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'.

declare @Longitude float = -87.05678
declare @Latitude float = 30.64520
declare @distance_in_meters int = 5
declare @point geography
set @point = geography::Point(@Latitude,@Longitude, 4326)

select Latitude, Longitude from gpstemp
where @point.STDistance <= @distance_in_metersc

I would greatly appreciate any assistance you could provide. Thank you so much.

2

Please dont spam with same question?

/*
	Microsoft SQL Server 2016 
	5 meters 16 foot

*/
create table #gps(locationname varchar(50), latitude float, longitude float, 
gps varchar(1500), GeoPoint geography)

insert into #gps(locationname, latitude,longitude, gps)
select 'Ferguson Airport', 30.397101,-87.348347, '' union
select 'Brownsville Middle School', 30.432645,-87.257409, 
'0xE610000000010CF2D2'union
select 'Warrington Middle School', 30.4064807891846,
 -87.2722015380859, '0xE610000000010CF2D2' union
select 'The Pines At Warrington', 30.4064807891818, 
-87.2722015386914, 
'0xE610000000010CF2D2' union
select 'Saufley Field', 30.460156,-87.340107, ''

Update #gps    Set GeoPoint = GEOGRAPHY::Point(latitude, longitude, 4326)

;with cteBoomBam
as
(
SELECT A.locationname _fromLocation, B.locationname _toLocation
       ,InMeters  = A.GeoPoint.STDistance(B.GeoPoint) 
       ,InMiles   = A.GeoPoint.STDistance(B.GeoPoint) / 1609.344
  FROM #gps A
  cross apply #gps B
  where a.locationname <> b.locationname
)    
select * From cteBoomBam where InMeters <= 5

--select @@VERSION

drop table #gps