Use haversine formula in java or some advanced postgis queries?

Question

imagine that we have a table with latitude and longitude (numeric) columns, range column, name column, etc..

We have an input - coordinates latitude & longitude

I want to construct a query to get only rows from this table, which distance (in meters) against input coordinates is less than the value from range column.

The question is, how to do this the most efficient way?

I can imagine to do it this way: Firstly, do some "first cut" of million records (driver_users) - I will apply classic query to select coordinates BETWEEN min&max... so, we will reduce amount of gathered rows and after that we will apply haversine formula on each row and compare it with the value in range column. Question is also how to compute the min&max values for cut off. Let's say max 50 kilometers from input coordinates - could be also square I think because it is only initial select to reduce the count of haversine formula use.

But i am not sure, whether this is really efficient. I am using java (spring boot), hibernate/jpa and postgresql. It is also possible to completely remake the SQL table if it is not good. Thank you.

CREATE TABLE public.driver_users
(
    username character varying(100) NOT NULL,
    longitude numeric,
    latitude numeric,
    range integer NOT NULL,
    UNIQUE (username),
    FOREIGN KEY (username)
        REFERENCES public.users (username) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT driver_users_pkey PRIMARY KEY (username)
);

Answer 1

Using PostGIS will let you use many functions, including distance computation, projection handling, spatial index etc.

For your task, you would create a geography column and put a spatial index on it.

SELECT * 
FROM myTable
WHERE ST_DWITHIN(geog, ST_SetSRID(ST_Point(-70.123, 40.456), 4326)::geography,50000)

or using a variable distance stored in the driver_range column

SELECT * 
FROM myTable
WHERE ST_DWITHIN(geog, ST_SetSRID(ST_Point(-70.123, 40.456), 4326)::geography,driver_range)