How to Store Longitude & Latitude as a Geography in SQL Server 2014

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

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;

What's the best way to store co-ordinates (longitude/latitude, from Google Maps) in SQL Server?

Take a look at the new Spatial data-types that were introduced in SQL Server 2008. They are designed for this kind of task and make indexing and querying much easier and more efficient.

More information:

• MS TechNet: SQL Server 2008 Spatial Data Types,

• MSDN: Working with Spatial Data (Database Engine).

Update Geography column in table

You don't need a temp table @Temp. You can use geography::Point directly on your table Location.Cities.

Something like this.

Update Location.Cities
set Geo = geography::Point(Latitude, Longitude , 4326)

If you want to use geography::STGeomFromText, you can use it like this.

    Update Location.Cities
        set Geo = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(30),Longitude ) + ' ' + CONVERT(VARCHAR(30),Latitude) + )',4326)

How do I select Lat & Long coordinates back out of SQL Server Geography data type field

Select convert(varchar(max),Coordinates) as Coordinates from SpatialZonePolygons ;

take a look here for more information - SQL Server Geography Data Type

How to find near stores from store location (latitude, longitude) from store table?

Spatial queries are better handled using the extension PostGIS. It has loads of really handy functions that make spatial queries very easy to write and to maintain. My suggestion:

Install Postgis (see this other answer)

Add a geometry column to your table, e.g.

SELECT AddGeometryColumn ('public','store','geom',4326,'POINT',2);

Create point geometries based on your latitude and longitude values:

UPDATE store SET geom = ST_MakePoint(longitude,latitude);

Index it (to make queries faster)

CREATE INDEX idx_store_geom ON store USING gist (geom);

After that, this is how a query to list the nearest neighbours of a given point would look like:

SELECT * FROM store
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(92.215,111.12),4326)

Or if you want the nearest store to each store ..

SELECT * FROM store mds,
LATERAL (SELECT store_name,ST_Distance(geom,mds.geom) FROM store
         WHERE id <> mds.id
         ORDER BY geom <-> mds.geom
         LIMIT 1) c (closest_store,distance);

• The operator <-> stands for distance, so using it in the ORDER BY clause with LIMIT 1 selects only the record that is closest to a reference geometry.
• 4326 stands for the spatial reference system WGS84. It might vary depending on your coordinates.

Demo: db<>fiddle

Get latitude/longitude from BigQuery Geography Point

Use the Geography functions ST_X and ST_Y.

SELECT 
  ST_X(locationCoords) as longitude,
  ST_Y(locationCoords) as latitude 
FROM 
  dataTable

Geography Functions DOC

Optimizing SQL WHERE for calculating distance between latitude and longitude positions

A good rule of thumb is that the execution time of a database query depends on the number of disk pages that have to be read. The CPU time can usually be ignored.

According to this rule, your proposed optimization will improve the execution time if it makes a difference for the number of disk pages. This will be the case if there is an index on latitude and longitude that will allow to skip many table rows and therefore many disk pages. If that's the case, the optimizer will certainly evalute that part of the WHERE clause before the distance.

If there's no index that helps with those two columns, I doubt you will see a big difference.

SQL Geography point STDistance ~25-50% off from Google?

And in the end, they were actually in the wrong order (Lat Long versus Long Lat).

So point takes it (long, lat), and google takes it lat, long.

---

Related Topics