Solved

Querying user location using Google Play Services, Google Maps, and SQL

Posted on 2014-04-06
6
469 Views
Last Modified: 2014-04-16
I am writing a client server application that tries to coordinate carpooling between users, based on their location.

I am using Google Play Services library, which can be downloaded in the Android SDK Manager in Eclipse.

You need to have PostGreSQL installed on your machine. You can download it here: http://www.postgresql.org/download/

To create the database and insert the tables into the database, you must run the commands in the init.sql file in psql.

The init.sql file outlines the schema for the database. See attached file.

I am trying to modify the following SQL query in my proximityToEndPoint() method, which is shown below the following statement:

query = "SELECT username, password" + "FROM endpoint, rs_user\n" + "WHERE lat AND long IN " + calculateDistance(lat1, long1, lat2, long2);

public void proximityToEndPoint(String username, String password,
double latitude, double longitude) {
double radius = 6371;

connect2DB();

if (this.conn != null) {
try {

String query = "";
PreparedStatement statement;
@SuppressWarnings("unused")
ResultSet resultSet;

double lat1 = 33.420243;
double long1 = -111.936125;
double lat2 = 33.3070;
double long2 = 111.6783;

query = "SELECT username, password"
+ "FROM endpoint, rs_user\n" + "WHERE lat AND long IN "
+ calculateDistance(lat1, long1, lat2, long2);
statement = this.conn.prepareStatement(query);
statement.setString(1, username);
statement.setString(2, password);
resultSet = statement.executeQuery();

this.conn.commit();
statement.close();

} catch (SQLException e) {
e.printStackTrace();
}

}
closeDBConnection();

}

Open in new window


The calculateDistance() method calculates the distance between two sets of geographical coordinates (endpoints). We are trying to determine which set of coordinates a user is closest to:

public static double calculateDistance(double lat1, double lng1,
double lat2, double lng2) {
int r = 6371; // average radius of the earth in km
double dLat = Math.toRadians(lat2 - lat1);
double dLon = Math.toRadians(lng2 - lng1);
double a = Math.sin(dLat / 2) * Math.sin(dLat / 2)
+ Math.cos(Math.toRadians(lat1))
* Math.cos(Math.toRadians(lat2)) * Math.sin(dLon / 2)
* Math.sin(dLon / 2);
double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
double d = r * c;
double e = 0.621371 * d;
return e;
}

Open in new window

How do you determine whether a user in a certain radius of a location (represented by a set of coordinates in latitude and longitude)?

Is there any way to query if a user is geographically located between a range of coordinates (ie between 15.0 and 20.0 degrees latitude and 5.0 and 20.0 degrees longitude)?

Thank you for your help.
init.sql
0
Comment
Question by:AttilaB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 39997194
Have you considered using postgis which uses SQL query && operator to reduce data slice before you do the compute intensive ST_Distance function.

SELECT the_geom
FROM geom_table
WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
  AND
ST_Distance(the_geom, ST_GeomFromText('POINT(100000 200000)', -1)) < 100;

I don't understand your schema very well the only table that has latitude is table location but no other table references it.

select * from location as loc where loc.lat between 15.0 and 20.0 and
loc."long" between 5.0 and 20.0;


select calculateDistance( loc.lat, loc."long", 18.4,7.6 ) as distance, loc.* from location as loc where calculateDistance( loc.lat, loc."long", 18.4,7.6 )
< 5.0;
0
 
LVL 22

Expert Comment

by:earth man2
ID: 39997196
combining the two...

select calculateDistance( loc.lat, loc."long", 18.4,7.6 ) as distance, loc.* from  
(select * from location  where location.lat between 15.0 and 20.0 and
location."long" between 5.0 and 20.0 )  as loc
where calculateDistance( loc.lat, loc."long", 18.4,7.6 ) < 5.0;
0
 

Author Comment

by:AttilaB
ID: 40001488
Thank you for the tip. Unfortunately, there were errors in your SQL command. loc is not a table. loc should be an instance of the location table. Also, I cannot retrieve the latitude and longitude using the dot operator, as you presented in your script.

 Do I need to make getters and setters for the lat and long in my code or is there a way to retrieve those values through SQL?

Also, the function parameters seem to be incorrect. It should be passing four numeric values (decimals), rather than two real and two numeric values. It also says 'missing FROM-clause entry for table', even though it appears that you have it.

Could you look at these, and get back to me?

Thanks.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 40002672
create function calculateDistance(lat1 real, lng1 real, lat2 real, lng2 real ) returns real as
$$
declare
  dLat real := (lat2 - lat1)*pi()/180.0;
  dLon real := (lng2 - lng1)*pi()/180.0;
  a real;
begin
  a := sin( dLat / 2 ) ^ 2 + cos( lat1 * pi()/180.0) * cos( lat2 * pi()/180.0) * sin( dLon / 2 ) ^ 2;
  return 6371.0 * 0.621371 * 2.0 * atan2(sqrt(a), sqrt(1.0 - a));
end;
$$ language plpgsql;

select calculateDistance( loc.lat, loc."long", 18.4,7.6 ) as distance, loc.* from  
(select * from location  where location.lat between 15.0 and 20.0 and
location."long" between 5.0 and 20.0 )  as loc
where calculateDistance( loc.lat, loc."long", 18.4,7.6 ) < 125.0;

 distance | location_id | lat  | long | endpoint
----------+-------------+------+------+----------
  103.581 |           1 | 19.1 |    9 |        1
(1 row)
0
 

Author Closing Comment

by:AttilaB
ID: 40004371
Thank you for your help. It worked in PostGreSQL. I noticed that your function had hard-coded values (for testing, of course). Is there a way to write this query with general parameters in Java? (ie loc1.lat, loc2.long)? I have been trying to write this query in my Java source code but have experienced compiler errors.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 40005945
Are you using java float or double.  You might want to increase precision like ..

create function calculateDistance( lat1 double precision, lng1 double precision, lat2 double precision, lng2 double precision ) returns double precision as
$$
declare
  deg2rad double precision := pi()/180.0;
  a double precision := sin( ( lat2 - lat1 )*deg2rad / 2 ) ^ 2 +
        cos( lat1 * deg2rad ) * cos( lat2 * deg2rad ) * sin( ( lng2 - lng1 )*deg2rad / 2 ) ^ 2;
begin
  return 6371.0 * 0.621371 * 2.0 * atan2(sqrt(a), sqrt(1.0 - a));
end;
$$ language plpgsql;

Are you escaping the double quotes with the backslash -- difficult to know what the issue is without further detail.  The java should look something like ..

  ResultSet rs = null;
  Connection conn = DriverManager.getConnection( url, username, password );

    try {
        PreparedStatement matches = conn.prepareStatement(

"select calculateDistance( loc.lat, loc.\"long\", ?,? ) as distance, loc.* from  
(select * from location  where location.lat between ? and ? and
location.\"long\" between ? and ? )  as loc
where calculateDistance( loc.lat, loc.\"long\", ?,? ) < ?;"

);
         matches.setDouble( 1, lat1 );
         matches.setDouble( 2, long1 );

         matches.setDouble( 3, latmin );
         matches.setDouble( 4, latmax );

         matches.setDouble( 5, longmin );
         matches.setDouble( 6, longmax );

         matches.setDouble( 7, lat1 );
         matches.setDouble( 8, long1 );

         matches.setDouble( 9, limiting_distance );

         rs = matches.executeQuery();

etc
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
The case of the missing phone talks about the way a small electronic gadget (the mobile phone) has penetrated into our lives and has made us addicted to it.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question