Radius from lat, long

1

Hi,

I'm trying to generate a list of GPS coordinates within 5 meters radius of a location. My latitude and longitude are stored as FLOAT, but after research, I have noticed that I need to work with GEORGRAPHY datatype? So what I'm doing is creating an additional column of that type, and then working with that. I have learned of a few functions (ST_Within, ST_SetSRID, etc). The problem I'm facing is that any function I use returns a 'function not recognized error'. Am I on the right track?

Any help you could provide would be greatly appreciated. My table is called 'gpstemp'. Fields are 'latitude', 'longitude', and gps (my created field of geography datatype).

Thank you so much.

2

is this for Microsoft SQL Server? if so could you please provide some sample data and the sql code that is causing the error. We cant just guess what your code is. Well we could :slight_smile: and give you the wrong solution :slight_smile:

3

Thank you. Yes, for SQL SERVER 2008.

I'm currently creating a temporary table with a subset of data from a view just to get the process down. I can't alter the view as it's a third-party database.

select latitude,longitude into gpstemp from avl.vwsc_DeviceLatLongHistory
where perno = 'ECSO02PER000093'

--Then create the geography field
alter table gpstemp add gps geography

--Calculate the field
update gpstemp set gps = ST_SetSRID(ST_MakePoint(Longitude,Latitude), 4326)

--Execute query to get all coordinates within 5 meters radius
SELECT *
FROM gpstemp
WHERE ST_DWithin(gps, ST_SetSRID(ST_MakePoint(-72.657, 42.0657), 4326), 5)

4

so in order to help you we need sample data from you or give us access to your SQL Server so we can see what is in gpstemp :slight_smile:
no to the latter of course so please provide sample data like this

create table #sample( perno varchar(50), long geography, lat geography)

insert into #sample
select 'ECSO02PER000093', 87.66.77.8, 56.56.66

5

It's just latitude and longitude data, along with the calculated geography field.

latitude longitude gps
30.4064807891846 -87.2722015380859 0xE610000000010CF2D2
30.4064807891818 -87.2722015386914 0xE610000000010CF2D2

6

please provide the data type of three columns. latitude longitude gps
and now please provide the code that is causing the error. is it tsql code? you are almost there!

7

SELECT *
FROM gpstemp
WHERE ST_DWithin(gps, ST_SetSRID(ST_MakePoint(-87.2350830923442, 30.4425428616669), 4326), 5)

'ST_MakePoint' is not a recognized built-in function name.

8

those are not SQL Server functions. but there are geo function in SQL Server.
Given the sample data your provided what is the final result you want.

9

A listing of all GPS coordinates within 5 meters radius of specified coordinates.

10

then please provide some more sample of data within and outside of 5 meters radius

11

Thanks for your help so much. But a listing of everything within 5 meters is what I'm trying to fin dout.

12 
/*
	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