org.postgresql.util.PSQLException: ERROR: function st_dwithin(geometry, double precision, double precision) does not exist

Question

I would like to get all stores near a specific location however it seems that I am having a problem with the query string. I have checked the version of postgis is postgis 2.5.2_2. I have also checked to see if longitude and latitude have double precision.

My database has the following structure:

I have tried to rewrite the query Into a different query string but I still get the same error.

My Entity:

@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 cityName;
    private String zipCode;
    private String town;
    private String address;
    private Long sellerId;

    @JsonIgnore
    @Column(columnDefinition = "geometry")
    private com.vividsolutions.jts.geom.Point location;

}

My RepositoryInterface:

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

    @Query(value="SELECT * FROM seller_geolocation_ms WHERE ST_DWithin(location,?1,?2) = true", nativeQuery = true)
    public Set<Seller_Geolocation> findAllSellersInRange(double longitude, double latitude);

}

My Service Class I have this function :

    public Set<Seller_Geolocation> getAllSellersInLocation(Double longitude, Double latitude) {
        return sellerRepository.findAllSellersInRange(longitude, latitude);
    }

I get the following error :

ERROR: function st_dwithin(geometry, double precision, double precision) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

UPDATE :

The query string :

SELECT * 
FROM seller_geolocation_ms 
WHERE ST_DWithin(location::geography, ST_SetSRID(ST_Point(59.393181, 5.286147), 4326), 30000);

works in the postgres database but in the java app it returns an error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"

JGH JGH · Accepted Answer · 2019-07-24T12:48:23

ST_DWithin checks if two geometries are within a given distance of each others. It takes 2 geometries and a distance as parameters.

The row SQL would be

SELECT * 
FROM myTable
WHERE ST_DWithin(mytable.geom,ST_SetSRID(ST_Point(longitude,latitude),4326), distance_degrees);

Now this would take a distance in degrees. To use a distance in meters you can either reproject to a CRS whose unit is meter, or cast to geography

SELECT * 
FROM myTable
WHERE ST_DWithin(mytable.geom::geography,ST_SetSRID(ST_Point(longitude,latitude),4326)::geography, distance_meters);

Or using a different way of casting to geography:

SELECT * 
FROM myTable
WHERE ST_DWithin(cast(mytable.geom as geography),ST_SetSRID(ST_Point(longitude,latitude),4326)::geography, distance_meters);

org.postgresql.util.PSQLException: ERROR: function st_dwithin(geometry, double precision, double precision) does not exist

1 Answers