How to store longitude & latitude as a geography in sql server 2014?
You can convert lat and long to a point and save it in table.
Declare @geo Geography, @Lat varchar(10), @long varchar(10)
SET @Lat = '34.738925' SET @Long = '-92.39764'
SET @geo= geography::Point(@LAT, @LONG, 4326)
How can i use the longitute and latitute to store location within a geography column?(because it's supposed to be only one geographic point not two right? not one for longitute and one for latitute?)
You can use geography::STPointFromText
/ geography::Point
to store longitude and latitude in a geography datatype.
SELECT geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
or
SELECT geography::Point(Latitude, Longitude , 4326)
Reference Link:
Update Geography column in table
Now that I've got the geography points, how can i select all the rows within a specific distance(in my case 2km)?
You can use STDistance
like this.
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34720 47.65100)', 4326);
SELECT @g.STDistance(@h);
Reference Link:
Distance between two points using Geography datatype in sqlserver 2008?
Insert Query
DECLARE @GeoTable TABLE
(
id int identity(1,1),
location geography
)
--Using geography::STGeomFromText
INSERT INTO @GeoTable
SELECT geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326)
--Using geography::Point
INSERT INTO @GeoTable
SELECT geography::Point(47.65100,-122.34720, 4326);
Get Distance Query
DECLARE @DistanceFromPoint geography
SET @DistanceFromPoint = geography::STGeomFromText('POINT(-122.34150 47.65234)', 4326);
SELECT id,location.Lat Lat,location.Long Long,location.STDistance(@DistanceFromPoint) Distance
FROM @GeoTable;