Be seen. Boost your question’s priority for more expert views and faster solutions

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);

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:

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

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();
}
```

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;
}
```

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

SELECT the_geom

FROM geom_table

WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d

AND

ST_Distance(the_geom, ST_GeomFromText('POINT(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;

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;

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.

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.getConnectio

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

$$

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)