Java Community | Help. Code. Learn.•2mo ago

PostGIS distance calculation gives in-accurate results

    @Query(value = "SELECT ST_Distance(" +
            "ST_SetSRID(ST_MakePoint(:userLng, :userLat), 4326), " +
            "ST_SetSRID(ST_MakePoint(:hotelLng, :hotelLat), 4326)" +
            ") AS distance", nativeQuery = true)
    Double hotelDistanceBetweenUser2(@Param("userLng") Double userLongitude,
                                     @Param("userLat") Double userLatitude,
                                     @Param("hotelLng") Double hotelLongitude,
                                     @Param("hotelLat") Double hotelLatitude);

    @Query(value = "SELECT ST_Distance(" +
            "ST_SetSRID(ST_MakePoint(:userLng, :userLat), 4326), " +
            "ST_SetSRID(ST_MakePoint(:hotelLng, :hotelLat), 4326)" +
            ") AS distance", nativeQuery = true)
    Double hotelDistanceBetweenUser2(@Param("userLng") Double userLongitude,
                                     @Param("userLat") Double userLatitude,
                                     @Param("hotelLng") Double hotelLongitude,
                                     @Param("hotelLat") Double hotelLatitude);

46 Replies

JavaBot•2mo ago

⌛ This post has been reserved for your question.

Hey @Rag JN Eclipse IDE! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

The problem is I used Vincenty's algorithm Which gave accurate result

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

Distance from repo is the PostGIS usage, and distanceFromAlgorithm by using the algo

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

The accurate one is Vincenty's algorithm

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

https://www.baeldung.com/java-find-distance-between-points#calculate-the-distance-using-vincentys-formula Vincenty's algorithm

Baeldung

Calculate Distance Between Two Coordinates in Java | Baeldung

Learn three methods to calculate the distance between two geographical coordinates in Java.

tjoener•2mo ago

That's not inaccurate, one of those is calculating something completely different what postgis function did you use?

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

^ ST_Distance I even used ST_DistanceSphere I even used Points

            Point userLocation = factory.createPoint(new Coordinate(latitude, longitude));
            Point hotelLocation = factory.createPoint(new Coordinate(hotelLat, hotelLong));
            Double distanceFromRepo = hotelRepository.hotelDistanceBetweenUser(userLocation,hotelLocation);

            Point userLocation = factory.createPoint(new Coordinate(latitude, longitude));
            Point hotelLocation = factory.createPoint(new Coordinate(hotelLat, hotelLong));
            Double distanceFromRepo = hotelRepository.hotelDistanceBetweenUser(userLocation,hotelLocation);

Nothing gives me accurate results

    @Query(value = "SELECT ST_DistanceSphere(:point1,:point2) ", nativeQuery = true)
    Double hotelDistanceBetweenUser(@Param("point1") Point point1, @Param("point2") Point point2);

    @Query(value = "SELECT ST_DistanceSphere(:point1,:point2) ", nativeQuery = true)
    Double hotelDistanceBetweenUser(@Param("point1") Point point1, @Param("point2") Point point2);

    private GeometryFactory factory = new GeometryFactory(new PrecisionModel(), 4326);

    private GeometryFactory factory = new GeometryFactory(new PrecisionModel(), 4326);

This also has been used

tjoener•2mo ago

you should pass it geometries or it wouldn't work ok makepoint does that your points are stored in 4326?

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

yeah it should be so I am not passing Geometrics?

tjoener•2mo ago

st_makepoint is ok I'd suggest read the docs very carefully, to see what it outputs, and what you expect it to output You want haversine, or spherical distance between two points, and in a specific unit of measurement So check those requirements for whichever function you call

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

https://postgis.net/docs/ST_Distance.html This one right?

tjoener•2mo ago

And also, don't go to the database for something like this, unless the data is already there, because a roundtrip to the database is not the way to do it any, there's more

minimum 2D Cartesian (planar) distance between two geometries, in projected units (spatial ref units)

Is that what you want? If not, look around for another function

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

I want how most online tools give almost havesine's result, and I want to get it using PostGIS

tjoener•2mo ago

Returns minimum distance in meters between two lon/lat geometries using a spherical earth model

That one seems more accurate

Returns minimum distance in meters between two lon/lat points. Uses a spherical earth and radius derived from the spheroid defined by the SRID

ST_DistanceSphere what does that give you?

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

please wait.

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

Query I used is

    @Query(value = "SELECT ST_DistanceSphere(:point1,:point2) ", nativeQuery = true)
    Double hotelDistanceBetweenUser(@Param("point1") Point point1, @Param("point2") Point point2);

    @Query(value = "SELECT ST_DistanceSphere(:point1,:point2) ", nativeQuery = true)
    Double hotelDistanceBetweenUser(@Param("point1") Point point1, @Param("point2") Point point2);

tjoener•2mo ago

that's better paste those two locations plz

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

distanceFromRepo is the result for query. 25.7217, -80.1218 25.7617, -80.1918

tjoener•2mo ago

yeah, check if you have lat/lon switched but that result makes way more sense

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

You can see the lat and longs aren't switched UserLocation, and hotelLocation are the Points.

            private GeometryFactory factory = new GeometryFactory(new PrecisionModel(), 4326);
            double hotelLat = hotel.getGeoLocation().getY();
            double hotelLong = hotel.getGeoLocation().getX();

            Point userLocation = factory.createPoint(new Coordinate(latitude, longitude));
            Point hotelLocation = factory.createPoint(new Coordinate(hotelLat, hotelLong));
            Double distanceFromRepo = hotelRepository.hotelDistanceBetweenUser(userLocation,hotelLocation);

            private GeometryFactory factory = new GeometryFactory(new PrecisionModel(), 4326);
            double hotelLat = hotel.getGeoLocation().getY();
            double hotelLong = hotel.getGeoLocation().getX();

            Point userLocation = factory.createPoint(new Coordinate(latitude, longitude));
            Point hotelLocation = factory.createPoint(new Coordinate(hotelLat, hotelLong));
            Double distanceFromRepo = hotelRepository.hotelDistanceBetweenUser(userLocation,hotelLocation);

7820.7057?

tjoener•2mo ago

yeah

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

It should be this

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

bit brightness increased

tjoener•2mo ago

and the spheroid function what does that do?

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

Synopsis
float ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid=WGS84);

Description
Returns minimum distance in meters between two lon/lat geometries given a particular spheroid. See the explanation of spheroids given for ST_LengthSpheroid.

Synopsis
float ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid=WGS84);

Description
Returns minimum distance in meters between two lon/lat geometries given a particular spheroid. See the explanation of spheroids given for ST_LengthSpheroid.

tjoener•2mo ago

yes, but result I know what it does

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

ok let me try

tjoener•2mo ago

oof, JTS, you're full on in the "use all the things" phase it seems

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

I hope it all the same syntax, the example syntax is harder to even read

tjoener•2mo ago

Also, use Point, not Coordinate I mean, the function arguments are the same...

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

yep

    @Query(value = "SELECT ST_DistanceSpheroid(:point1,:point2) ", nativeQuery = true)
    Double hotelDistanceBetweenUser(@Param("point1") Point point1, @Param("point2") Point point2);

    @Query(value = "SELECT ST_DistanceSpheroid(:point1,:point2) ", nativeQuery = true)
    Double hotelDistanceBetweenUser(@Param("point1") Point point1, @Param("point2") Point point2);

how none of them doesn't come close to 8?

tjoener•2mo ago

switch both lat and lon around just curious

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

bet

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

wow

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

🙊

tjoener•2mo ago

yeah I had a feeling Which is why I fucking hate that stupid library you're using It's so confusing With their factories and their coordinates with X and Y instead of lat and lon

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

it's still my fault right? I mean I just followed the standards Thank you so much.

JavaBot•2mo ago

If you are finished with your post, please close it. If you are not, please ignore this message. Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.

tjoener•2mo ago

nah, that library is just confusing as hell, I made the same mistakes all the time when using it a long time ago Now I just quickly add my own types and call it a day Anyway, good luck!

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

ah ok. what that means really?

tjoener•2mo ago

I write my own Coordinate class, and some converters so jpa can be happy

Rag...JN 🌌 🦡 👽 💰 🐊OP•2mo ago

ah ok

JavaBot•2mo ago

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

Gaming

Programming

PostGIS distance calculation gives in-accurate results