CODEWITHSUNDEEP
sqldatabasepostgresqlpositionlocation
smartmouse
smartmouse

Reputation: 14404

How to get the nearest points near user position querying SQL database?

in the past for a Java project i wrote the following code to get the nearest points to a provided location (within 50 km):

// user position
double myLatG = myLat * Math.PI / 180;
double myLonG = myLon * Math.PI / 180;              

// loop on sqlite database that contains all points
while (cur.moveToNext()) {
    double destLatG = cur.getDouble(0) * Math.PI / 180;
    double destLonG = cur.getDouble(1) * Math.PI / 180;
    double phi = Math.abs(myLonG - destLonG);
    double distance = (Math.acos(Math.cos(phi) * Math.cos(myLatG) * Math.cos(destLatG) + Math.sin(myLatG) * Math.sin(destLatG))) * 6387;

    if (distance <= 50) {
        // my code
    }
}

Now i want to do the same on a SQL database, I mean finding the nearest points to provided latitude and longitude within 50 km. So would apply the above algorithm to a database (Postgres). Is it possible to get the same result with any SQL query?

Upvotes: 1

Views: 754

Answers (3)

smartmouse
smartmouse

Reputation: 14404

I solved, thanks to this link. It shows the nearest points to provided coordinates (within 50 km). It works perfectly:

SELECT m.name,
    m.lat, m.lon,
    p.distance_unit
             * DEGREES(ACOS(COS(RADIANS(p.latpoint))
             * COS(RADIANS(m.lat))
             * COS(RADIANS(p.longpoint) - RADIANS(m.lon))
             + SIN(RADIANS(p.latpoint))
             * SIN(RADIANS(m.lat)))) AS distance_in_km
FROM <table_name> AS m
JOIN (
      SELECT <userLat> AS latpoint, <userLon> AS longpoint,
             50.0 AS radius, 111.045 AS distance_unit
     ) AS p ON 1=1
WHERE m.lat
BETWEEN p.latpoint  - (p.radius / p.distance_unit)
    AND p.latpoint  + (p.radius / p.distance_unit)
    AND m.lon BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
    AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY distance_in_km

Just change <table_name>. <userLat> and <userLon>

Upvotes: 1

Patrick
Patrick

Reputation: 32326

In PostgreSQL you can use the PostGIS extension. PostGIS provides two data types, of which the geography data type is just what you need. In columns of this type you can store (longitude, latitude) coordinates on the WGS84 sphere (just what you need, looking at your Earth radius of 6,387 km).

It appears that you have a certain location (myLon, myLat) and you want to find all points in a table that are within 50 km. In PostGIS this is easy:

SELECT *
FROM table_with_points t
WHERE ST_DWithin(
         ST_GeogFromText('SRID=4326;POINT(' || myLon || ' ' || myLat || ')'),
         ST_GeogFromText('SRID=4326;POINT(' || t.lon || ' ' || t.lat || ')'),
         50000);

This calculates the distance in meters on the WGS84 spheroid in a great-arc, i.e. as the crow flies.

If you go for this option, you probably want to use a geography column in your table so you can just enter the column name and forget about the second ST_GeogFromText() call.

Upvotes: 0

irene
irene

Reputation: 445

You can try with 

SELECT lat, lon 
FROM 
(
   SELECT lat, 
          lon, 
          frml(lat, lon, :mylat, :mylon) AS distance, 
   FROM table_all_points
) distance_table
WHERE distance <= 50

where table_all_points is your database table with all Points and :mylat and :mylon are your Input Parameters.

Replace frml(lat,lon,:mylat,:mylon) by a one-line-formula where you calculate the distance from given lat, lon, mylat and mylon value. I think frml(lat, lon, :mylat, :mylon) has to be

acos(cos(@(:mylon * pi()/180- lon * pi()/180)) * cos(:mylat * pi()/180) * cos(lat * pi()/180) + sin(:mylat * pi()/180) * sin(lat * pi()/180))) * 6387;

The mathemical functions you can use with postgres are listed here: http://www.postgresql.org/docs/7.4/static/functions-math.html

If you do not want to use a one-line-formula you can add another in-between-table, but i'm not sure whether it's more efficient.

Upvotes: 1

Related Questions