Geolocations Using Earthdistance and Cubes
How to use the Postgres earthdistance module and cube calculations to work with geolocations. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
(Note: This is a condensed transcript without code snippets. For the full transcript, visit www.pgcasts.com)
Hey everyone, today we're going to look at how to use the Postgres earthdistance module and cube based earth distances to work with geolocations.
---
A common requirement in a database using latitude and longitude geolocations is querying for locations within a certain radius or distance from another location. There are multiple approaches for easily managing such queries. You can use the Postgres earthdistance module with either points or cubes, or you can use the third-party PostGIS extension. In this episode, we're going to focus on using the earthdistance module with point data types.
The earthdistance module is an officially supported Postgres extension that must be manually enabled. It is dependent upon the Postgres cube extension, so I'm going to quickly install both.
For this example, we're going to be working with an addresses table, which has latitude and longitude columns of type float8, or double precision, and a few entries.
We're going to look at two examples for how to use the cube-based calculations in the earthdistance module. The first is determining the distance between locations. The second is finding locations within a certain radius of another location.
Let's start with calculating the distance between two locations. I'm going to use the "\e" metacommand to use vim as my query editor.
To calculate the distance between the Hashrocket Jacksonville office and all of the other addresses in our table, we're going to select from addresses and do a lateral join to get the Hashrocket Jacksonville address as a location to compare the other locations to. With that set up, we can complete our select statement to determine the distance between the Hashrocket Jacksonville geolocation and our other geolocations.
For determining the distance between two latitude/longitude coordinates, we're going to use the "earth\_distance" function. This function expects to receive two three-coordinate locations to use to compare distance, and returns the distance in meters between the locations. Since we only have two coordinates, latitude and longitude, we need to convert our data. We can do this with the helper method "ll\_to\_earth", passing first latitude, then longitude as our arguments.
We can see from our output that we now have the distance in meters of each address from the Hashrocket Jacksonville office. If we wanted to instead see it in miles, we can do a quick division on our earth_distance, using the conversion value for meters to miles, which is 1609.344.
To only find locations within a certain distance of of Jacksonville office, we can make a quick modification to our existing query. Since we already know how to get the earth distance between our two locations, we can reuse the same logic passing it now as part of our where clause, and using it in an inequality to check that the distance is less than a specific value, say 100 miles, meaning we can just move the decimal point from our earlier conversion.
With that change, we can see from our output that we are now only including addresses that are less than 100 miles from our Hashrocket Jacksonville address.
It's important to remember that the Postgres earthdistance module assumes that the Earth is a perfect sphere, which is not completely accurate. If you need extreme accuracy when working with geolocations, Postgres recommends that you consider the PostGIS extension.
Thanks for watching!
Watch video Geolocations Using Earthdistance and Cubes online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 26 December 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,485 once and liked it 24 people.