How to query a postgres database to get all points within a 10km radius of certain coordinates

Question

I have a database of shops and in that database I have coordinates for these shops saved. I would like to get the list of shops in a 10km radius however I am not sure how to write the Postgres query since I am using Postgres database.

My database :

I am trying to add the query to a springboot geolocation microservice :

Repository code :

@Repository
public interface SellerGeolocationRepository extends CrudRepository<Seller_Geolocation, Long> {

    @Query(value="SELECT * FROM seller_geolocation_ms WHERE 
   // get coordinates that are in the vicinity
", nativeQuery = true)
        public Set<Seller_Geolocation> findAllSellersInRange(double longitude, double latitude);

    }

SellerObject :

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "seller_geolocation_ms")
public class Seller_Geolocation {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private static final long serialVersionUID = 2L;

    private double latitude;
    private double longitude;
    private String country;
    private String city;
    private String zipCode;
    private String town;
    private String address;
    private Long sellerId;
}

https://gis.stackexchange.com/questions/77072/return-all-results-within-a-30km-radius-of-a-specific-lat-long-point — ipave, Jul 21 '19 at 20:06
@Erent Why aren't you using classes like `com.vividsolutions.jts.geom.Point` to persist geospatial data instead of simply persisting two double numbers? In that case you could easily make geospatial queries — Omid, Jul 21 '19 at 20:29
I wasn't aware of com.vividsolutions.jts.geom.Point, if I were to use it how would I make the query — Erent, Jul 21 '19 at 20:33
You need to use `hibernate-spatial` and install `postgis`. I'm going to post an answer using these in spring-boot — Omid, Jul 21 '19 at 20:42
@Erent look at this [question](https://stackoverflow.com/questions/33501246/cant-execute-spatial-query-with-spring-and-hibernate-5-0) This is how you should implement it. — Omid, Jul 21 '19 at 21:06
@Omid do u have an example of how to use hibernate-spatial and install postgis, I have installed them but I can't seem to get a good explanation on how to use it — Erent, Jul 22 '19 at 14:30
Have you tried using Criteria Queries? (https://www.baeldung.com/hibernate-criteria-queries) — Nikhil, Jul 29 '19 at 15:30
Here's some background information to the mathematics: https://www.movable-type.co.uk/scripts/latlong.html — Janos Vinceller, Jul 30 '19 at 13:30

score 3 · Answer 1 · answered Jul 21 '19 at 20:08

3

You can use the haversine formula, using your current coordinates (target_latitude_deg/longitude) and the column name (latitude_deg, longitude_deg), both expressed in degrees

SELECT * 
FROM myTable
WHERE acos(
       sin(radians(target_latitude_deg)) 
         * sin(radians(latitude_deg)) 
       + cos(radians(target_latitude_deg)) 
         * cos(radians(latitude_deg)) 
         * cos( radians(target_longitude_deg)
           - radians(longitude_deg))
       ) * 6371 <= 10;

Alternatively, have a look at the PostGIS extension

SELECT * 
FROM MyTable 
WHERE ST_Dwithin(geom, st_point(target_longitude, target_latitude,10000);

answered Jul 21 '19 at 20:08

JGH

15,928
4
31
48

Im not sure I understand the haversine formula and how to pass my current coordinates and query longitude and latitude from my db – Erent Jul 21 '19 at 20:27
1

This is not a good solution to the problem in this case. The point of using frameworks like spring boot combined with hibernate-spatial is not to get involved in low level details of how geo-spatial formulas work. OP should use built-in features of those frameworks not a raw sql query. – Omid Jul 21 '19 at 20:33
@Omid fair enough... though without PostGIS I am very curious to know how it could be leveraged by the framework – JGH Jul 21 '19 at 20:38
@JGH Well it's not possible without postgis. – Omid Jul 21 '19 at 20:43
1

@Omid :-/ so it's either an important change for the OP (for the best), or the provided raw SQL alternative – JGH Jul 21 '19 at 20:49
@JGH what is geom in the last query – Erent Jul 22 '19 at 14:31
the name of the column (geometry type) that you would have created/populated – JGH Jul 22 '19 at 14:46
To avoid doing a full search, first search for the points in a bounding box around the certain point with the length of 20 km. This search can use normal indices and will return a much smaller set of records the database will evaluate afterwards with this expression. – aschoerk Jul 24 '19 at 22:30

score 2 · Accepted Answer · answered Jul 30 '19 at 18:51

For this to work you will need to install postgis :

brew install postgis

afterwards you will need to install postgis on your postgres database "seller_geolocation_ms":

   1. $ sudo -i -u postgres
   2. $ psql
   3. postgres=# \c seller_geolocation_ms;
   4. seller_geolocation_ms=# CREATE EXTENSION postgis;
   5. \q

After these steps postgis should be installed on your database. then the next thing to is to write the query on your Springboot repository interface like this:

@Repository
public interface SellerGeolocationRepository extends CrudRepository<Seller_Geolocation, Long> {

    @Query(value="SELECT * FROM seller_geolocation_ms WHERE ST_DWithin(cast(seller_geolocation_ms.location as geography),ST_SetSRID(ST_Point(?2, ?1),4326), 10000);", nativeQuery = true)
    public Set<Seller_Geolocation> findAllSellersInRange(double longitude, double latitude);

}

To get a more indepth understanding of how ST_DWithin please follow this link :here

Thank you very much for this answer @DivxExtract !! In its current form, it didn't work for me, but it DID work once I switched out the ?2, ?1 parameter binding to named parameters :longitude :latitude — Bram Luyten, Aug 22 '20 at 09:53

score 0 · Answer 3 · answered Jul 30 '19 at 12:54

0

Take a look at Postgres earthdistance, ther's also an example mentioned here However, you can use Postgis which has rich geospatial functions. If you want to calculate at business logic, use Haversine formula as explained in @JGH answer above.

answered Jul 30 '19 at 12:54

grindlewald

323
3
10

How to query a postgres database to get all points within a 10km radius of certain coordinates

3 Answers3