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,
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 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
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
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
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.