How to store longitude & latitude as a geography in sql server 2014?

Question

I have locations in longitude and latitude coordinates. My goal is eventually to be able to select all rows from myTable Where distance is less than 2km.

1. How can one use the longitude and latitude to store location within a geography column?(because it's supposed to be only one geographic point not two right? not one for longitude and one for latitude?)

2. Now that I've got the geography points, how can i select all the rows within a specific distance(in my case 2km)?

Sublapsarianism

geography::Point(). – Submissive

Answer 1

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;

Dunne

Okay so using this works, how would i insert a way to select all rows within a specific distance? I've tried something similar to WHERE @DistanceFromPoint > 400 but it's obviously not the way to do it since it gives me errors, thanks again for the answer! – Sublapsarianism

you would use WHERE location.STDistance(@DistanceFromPoint) > 400 – Dunne

Okay great, thanks alot! when it says 400 for instance, is that in km or in miles? – Sublapsarianism

it's in meters – Dunne

Answer 2

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)

Kinesthesia

Answer 3

Addition to the above Answer @ughai

Adding a column

 ALTER TABLE [dbo].[Landmark]
 ADD [GeoLocation] GEOGRAPHY
 GO

Convert the Longitude and latitudes to Geography

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] 
AS VARCHAR(20)) + ' ' + 
                CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GO

Finding Places in the radius of 2kms

DECLARE @Origin GEOGRAPHY,
    -- distance defined in meters
    @Distance INTEGER = 2000;        


 SET @Origin = GEOGRAPHY::STGeomFromText('POINT(17.482477 78.546871)', 4326);

 -- return all rows from events in 2km radius
SELECT *,GeoLocation.STDistance(@Origin) Distance FROM dbo.Locations WHERE @Origin.STDistance(GeoLocation) <= @Distance;

it worked for me to get the places within the distance

Reimburse

Answer 4

In working with GeoPoints in sprocs and selects, it became tiring to always compute the GeoPoint so instead I found it convenient to create a computed column which generates a GeoPoint from the lat/long FLOAT fields when the row is selected.

[GeoPoint]  AS ([geography]::Point([Latitude],[Longitude],(4326))) PERSISTED,

Mooney