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;
}

@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
– Omid, Commented Jul 21, 2019 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
– Erent, Commented Jul 21, 2019 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
– Omid, Commented Jul 21, 2019 at 20:42
@Erent look at this question This is how you should implement it. — Omid
– Omid, Commented Jul 21, 2019 at 21:06

JGH · Accepted Answer · 2019-07-21 20:08:09Z

5

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, 2019 at 20:08

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Erent Over a year ago

Im not sure I understand the haversine formula and how to pass my current coordinates and query longitude and latitude from my db

Omid Over a year ago

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.

JGH Over a year ago

@Omid fair enough... though without PostGIS I am very curious to know how it could be leveraged by the framework

Omid Over a year ago

@JGH Well it's not possible without postgis.

JGH Over a year ago

@Omid :-/ so it's either an important change for the OP (for the best), or the provided raw SQL alternative

|

DvixExtract · Accepted Answer · 2019-07-30 18:51:54Z

3

+100

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

answered Jul 30, 2019 at 18:51

DvixExtract

1,33515 silver badges26 bronze badges

1 Comment

Bram Luyten Over a year ago

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

grindlewald · Accepted Answer · 2019-07-30 12:54:13Z

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, 2019 at 12:54

grindlewald

3383 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related