Calculating distance between geo-location-points

[bernhard s]

Hallo,

I want to do a 'user near you' query. I found this post which describes how to do this in sql. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
The query looks like this:

       SELECT *
            FROM (
           SELECT user.latitude, user.longitude,
                  p.radius,
                  p.distance_unit
                           * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                           * COS(RADIANS(user.latitude))
                           * COS(RADIANS(p.longpoint - user.longitude))
                           + SIN(RADIANS(p.latpoint))
                           * SIN(RADIANS(user.latitude)))) AS distance
            FROM USER AS user
            JOIN (   -- /* these are the query parameters */
                  SELECT  $latitude AS latpoint, $longitude AS longpoint,
                          $radius AS radius,      111.045 AS distance_unit -- this means kilometer
              ) AS p ON 1=1
            WHERE user.latitude
               BETWEEN p.latpoint  - (p.radius / p.distance_unit)
                   AND p.latpoint  + (p.radius / p.distance_unit)
              AND user.longitude
               BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                   AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
           ) AS d
           WHERE distance <= radius
           ORDER BY distance
           LIMIT 100

I can not get this working with slick. It complains, that it does not know the type 'latpoint'

The only way I could accecute this, was to use plain JDBC. Is there a way to get this query working with lifted embedding?

thx

[Sam Halliday]

eek! That's only going to ever work for coordinates very close together. Have a look here for more details of the Vincenty algorithm:

  https://github.com/fommil/openlapi

I doubt you can do this in the DB without arbitrary code execution.

[Sam Halliday]

btw, if you're doing a "users close to you" style query, you may as well use the DB just to prune the data with a simple box cutoff. See https://github.com/fommil/openlapi/blob/master/openlapi/src/com/openlapi/LocationProvider.java#L302

[bernhard s]

If I understand the Vincenty algorithm correctly, it calculates distances on a spheroid not on a sphere. That is more accurate. But for my use-case I think it is not necessary. All points should be in a relatively small area, of about 100.000 km2.

So I'd stick with that sql query. Any hints on how to map that to slick?

[Sam Halliday]

If it's within 100km then you'd be best profiling your db to see if that query isn't going to kill it. The openlapi code gives a similar query that would allow you to slice simply by longitude /latitude, which can be trivially backed by a SQL index.

On 1 Dec 2014 18:31, "bernhard s" <nebe...@gmail.com> wrote:

If I understand the Vincenty algorithm correctly, it calculates distances on a spheroid not on a sphere. That is more accurate. But for my use-case I think it is not necessary. All points should be in a relatively small area, of about 100.000 km2.

So I'd stick with that sql query. Any hints on how to map that to slick?

--

---
You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/xLZLF4wiwS0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/141f1847-9e93-4f91-933e-706fb3237bb0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[bernhard s]

Good point. I'll investigate that. Thank you.