Search
 
SCRIPT & CODE EXAMPLE
 

SQL

calculate distance between two latitude longitude points sql

CREATE OR REPLACE FUNCTION calculate_distance(lat1 float, lon1 float, lat2 float, lon2 float, units varchar)
            RETURNS float AS $dist$
                DECLARE
                    dist float = 0;
                    radlat1 float;
                    radlat2 float;
                    theta float;
                    radtheta float;
                BEGIN
                    IF lat1 = lat2 OR lon1 = lon2
                        THEN RETURN dist;
                    ELSE
                        radlat1 = pi() * lat1 / 180;
                        radlat2 = pi() * lat2 / 180;
                        theta = lon1 - lon2;
                        radtheta = pi() * theta / 180;
                        dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);
                        IF dist > 1 THEN dist = 1; END IF;
                        dist = acos(dist);
                        dist = dist * 180 / pi();
                        dist = dist * 60 * 1.1515;
                        IF units = 'K' THEN dist = dist * 1.609344; END IF;
                        IF units = 'N' THEN dist = dist * 0.8684; END IF;
                        RETURN dist;
                    END IF;
                END;
            $dist$ LANGUAGE plpgsql;


//Usage:
SELECT calculate_distance(41.311081, 69.240562, 39.7681, 64.4556, 'M');
SELECT calculate_distance(41.311081, 69.240562, 39.7681, 64.4556, 'K');
SELECT calculate_distance(41.311081, 69.240562, 39.7681, 64.4556, 'N');
Comment

distance calculator from lat long sql query

COALESCE(
  	( 
      6371 * acos( cos( radians({$latitude}) ) * cos( radians( `latitude` ) ) * cos( radians( `longitude` ) - radians({$longitude}) ) + sin( radians({$latitude}) ) * sin( radians( `latitude` ) ) ) 
    )
  , 0) AS distance
Comment

PREVIOUS NEXT
Code Example
Sql :: % Wildcard in SQL 
Sql :: oracle select partition 
Sql :: sql trim 
Sql :: how to create foreign key in sql server management studio 
Sql :: in condition in sql 
Sql :: sql joining 3 tables 
Sql :: oracle sql first day of quarter 
Sql :: delete duplicate data from table 
Sql :: flask sqlalchemy session delete 
Sql :: deletar banco de dados mysql 
Sql :: select all users sql 
Sql :: multiple tricky query in sql server 
Csharp :: how to lock and hide a cursor unity 
Csharp :: vb.net yes no cancel 
Csharp :: convert system.byte a string c# 
Csharp :: regions unity 
Csharp :: c# find start and end of month from object date 
Csharp :: unity how to convert to byte 
Csharp :: vue.createapp is not a function 
Csharp :: unity get distance between two objects 
Csharp :: c# random enum 
Csharp :: unity android quit application 
Csharp :: camera follow player unity smooth 
Csharp :: current directory in sln file c# 
Csharp :: c# random boolean 
Csharp :: unity deactive code from code 
Csharp :: unity inspector how to get larger field for string text 
Csharp :: c# reverse string 
Csharp :: defaultconnection appsettings.json 
Csharp :: c# datediff minutes 
ADD CONTENT
Topic
Content
Source link
Name
6+2 =