Let´s calculate distance with PostgreSQL

Hello everyone, how are you?

This is my first article in English, so, I´m sorry for my mistakes.

In this article, I want help for everyone who needs to calculate distance between two points using latitude and longitude.

Recently, I studied how I can calculate this distance, and initially I was creating a service from the PHP language, calculating origin and destination, obtaining distance in miles from haversine formule.

But a friend suggested calculating in SQL, and I found some forums that do this and decided to do it too.

So, below I am sharing how to do this.

It's simple. First, you need to install the extensions into your database.

After, u can make you select query.

to_char: to convert and return parsed with 2 decimals format (FM)

float8: to cast type in float.

point: is a function of postgres to computes the distance in statute miles between two points on the Earth's surface.

This example is with two fixed points, but you can use latitude and longitude from your table when you have defined it, example:

and then, you can use these attributes within your query assembled in your application.

The result is the distance from the user's location to the address defined in your table.

Ok, this will return the distance in miles.

Thanks. I hope help you.

See you later.

Reference: https://www.postgresql.org/docs/current/earthdistance.html