Get the Nearest Longitude and Latitude from Mssql Database Table

Get the nearest longitude and latitude from MSSQL database table?

I wrote a blog a couple years ago that explains how this can be done without using the spatial data types. Since it appears as though you have a table of longitude/latitude values, this blog will likely help a lot.

SQL Server Zipcode Latitude Longitude Proximity Search

Same page saved from Archive.org

How to find nearest location using latitude and longitude from SQL database?

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

sql query to get first closest locations by latitude and longitude provided as feilds in table

Your code in your question isn't doing anything like calculating distance between two points. It would be a good idea to do a little research on how to calculate distance between two points - that's where you'll need to start. You should be able to adapt my answer to your scenario, but it would be a good idea to understand the math involved if you'll be working with coordinates.

There's lots of info on Stack Overflow about calculating the distance between two points, even specifically to PostgreSQL. Here's an example adapted from another Stack Overflow artical (PostgreSQL calculate distance between two points without using PostGIS):

SELECT
    id,
    longitude,
    latitude,
    (select SQRT(POW(69.1 * (latitude::float -  10::float), 2) + 
        POW(69.1 * (longitude::float - 15::float) * COS(latitude::float / 57.3), 2)
    )) AS Distance
FROM destination
ORDER BY Distance

I have hardcoded values of 10 for the latitude of the origin point, and 15 for the longitude. Those will need to be replaced by the lat/long of the point you're trying to compare to.

This will return all of the rows in your destination table, including a new column that is the distance (in miles) of the row from your origin point. It will be ordered by that distance as well, closest to farthest.

find the nearest location in ms-sql

Use this function

CREATE FUNCTION dbo.DictanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
RETURNS FLOAT 
AS
BEGIN

    RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END

You may order by this function, BUT on large datasets it will be very slow, so try to prefilter the recordset

UPD:

Using @chopikadze's test data:

declare @lat float, @lng float
select @lat = 41.0186, @lng = 28.964701

declare @Location table(Latitude float, Longtitude float, Name nvarchar(50))
insert into @Location(Latitude, Longtitude, Name) values (41.0200500000, 40.5234490000, 'a')
insert into @Location(Latitude, Longtitude, Name) values (41.0185714000, 37.0975924000, 'b')
insert into @Location(Latitude, Longtitude, Name) values (41.0184913000, 34.0373739000, 'c')
insert into @Location(Latitude, Longtitude, Name) values (41.0166667000, 39.5833333000, 'd')
insert into @Location(Latitude, Longtitude, Name) values (41.0166667000, 28.9333333000, 'e')

SELECT ABS(dbo.DictanceKM(@lat, Latitude, @lng, Longtitude)) DistanceKm, * FROM @Location
ORDER BY ABS(dbo.DictanceKM(@lat, Latitude, @lng, Longtitude))

Assuming that the Earth is NOT a geoid, but the round ball, if you need under 1m exact formula - I can find it, don't have it with me

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

Find Nearest Place By Using My SQL Record Of LATITUDE And LONGITUDE

Use below query:

SELECT
    Place_id,
    PlaceName,
    (
        3959 
        * acos(
            cos( radians(37) ) 
            * cos( radians( Place_LATITUDE ) ) 
            * cos( radians( Place_LONGITUDE ) - radians(-122) ) 
            + sin( radians(37) ) 
            * sin( radians( Place_LATITUDE ) ) 
        ) 
    ) AS distance 
FROM tbl_places 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20;

NOTE - Here latitude = 37 & longitude = -122

Also, check here for more reference.

---

Related Topics