GeoFile: Getting the distance using Redis and PostgreSQL

GeoFile is a series dedicated to looking at geographical data, its features and uses. In this article, we discuss getting the distance between two points on a map and how Redis and the PostgreSQL extension PostGIS approach this problem.

The most common reason why we use maps is to calculate the distance from one point to another. Redis and PostGIS come with commands, GEODIST and ST_Distance, that allow us do this easily, but there are reasons why choosing one database over the other may be a better choice for your use case.

GEODIST in Redis

Let's create a set of Cities and add three cities (Seattle, New York, and San Francisco) with their longitude and latitude coordinates. To do this, we will use the GEOADD command like:

GEOADD Cities -122.3321 47.6062 "Seattle"  
-74.0059 40.7128 "New York" 
-122.4194 37.7749 "San Francisco"

Redis will store your geodata and automatically set up the geospatial index for us.

If you want to check what's stored in the Cities set, use the GETPOS command with the set name and the city name(s) you want. This will give you the longitude and latitude coordinates of each city like this:

GEOPOS Cities Seattle  
1) 1) "-122.33210116624832153"  
   2) "47.60619876995696131"

GEOPOS Cities Seattle "New York" "San Francisco"  
1) 1) "-122.33210116624832153"  
   2) "47.60619876995696131"
2) 1) "-74.00589913129806519"  
   2) "40.71279898695150479"
3) 1) "-122.41940170526504517"  
   2) "37.77490001056577995"

Now that the coordinates are stored, we can use the GEODIST command that allows us to get the distance between any two members in a sorted set. To get the distance between Seattle and New York, we can write the following query which will give us the distance between the cities in meters:

GEODIST Cities Seattle "New York"  
"3866626.6659"

By default, if you don't include the unit of measurement at the end of the GEODIST command, it will give you a distance in meters. You can also append to the query specific measurement units like meters, kilometers, feet, and miles.

For example, if we wanted our output in miles, we'd append mi to the command we just ran, which would give us the number of miles between Seattle and New York:

GEODIST Cities Seattle "New York" mi  
"2402.6164"

The only problem with GEODIST is that it assumes your points are on a perfect sphere. You cannot change the projection to one that uses a spheroid (for more information on projections, spheres and spheroids see here). The downside of calculating distances using a sphere is that it can produce an error of 0.5%, which is not ideal for every application, especially those that require a precise measurement over long distances.

ST_Distance in PostGIS

If you need to use a spheroid, or want to define another projection for your geodata, using PostGIS is a better option. It will allow you to define the projection you want to use, but all your measurements will return in meters.

To construct a basic query in SQL to find the distance between two points, use the ST_Distance function. ST_Distance is flexible in that you can pass in geometry or geography type object (see here for more information on these object types).

You are given the choice to set the projection to your requirements by either manually setting the projection and providing the longitude/latitude coordinates in the query, or you can use a geometry or geography value from your geom column.

To quickly set up a query, you could write the following, which will unfortunately give you the distance in degrees and not in meters:

SELECT ST_Distance(  
  ST_SetSRID(
    ST_MakePoint(-122.33210116624832153, 47.60619876995696131), 
  4326), 
  ST_SetSRID(
    ST_MakePoint(-74.0058991312980651, 40.71279898695150479), 
  4326)
);

   st_distance    
------------------
 48.8153742553631

Therefore, it's important that you set a boolean parameter at the end of the ST_Distance function to either true or false depending on whether your coordinates are located on a spheroid or sphere, respectively. You can view the differences between the calculations in the query below:

SELECT ST_Distance(  
  ST_SetSRID(
    ST_MakePoint(-122.33210116624832153, 47.60619876995696131), 
  4326), 
  ST_SetSRID(
    ST_MakePoint(-74.0058991312980651, 40.71279898695150479), 
  4326),
  true
) as spheroid, 
ST_Distance(  
  ST_SetSRID(
    ST_MakePoint(-122.33210116624832153, 47.60619876995696131), 
  4326), 
  ST_SetSRID(
    ST_MakePoint(-74.0058991312980651, 40.71279898695150479), 
  4326),
  false
) as sphere;

    spheroid     |     sphere      
------------------+-----------------
 3875620.66137305 | 3865541.3370621

If you already have a geom column in your table that's populated with a geography value, then you can select the value from your table without setting the projection, since it's already encoded in the geometry value. To set up the query, you could write the following:

SELECT ST_Distance(  
  (select geom from cities where id=1), 
-- 0101000020E6100000973D642541955EC0EE6F1AEC97CD4740
  (select geom from cities where id=2), --
-- 0101000020E6100000DA649EA6608052C08DCF64FF3C5B4440
  true
) as spheroid;

     spheroid     
------------------
 3875620.66515256

What's best?

So, there are benefits to using either Redis or PostGIS. If you want to do a quick look up on the distance between two locations with rather short distances, then Redis will allow you to write a query fast and give you a pretty accurate result without thinking about various projections. However, if you want options to have more control over your projections in addition to many other functions that will allow you to manipulate geodata, then PostGIS will give you the full power to transform and define SRIDs and the projections you need.

Next time we'll cover getting points within a radius in PostGIS.