58 |
protected String locateDroids(Connection conn, long imei, double latitude, double longitude) throws SQLException { |
protected String locateDroids(Connection conn, long imei, double latitude, double longitude) throws SQLException { |
59 |
//inner select is workaround from not being able to use a calculated column directly in where clause |
//inner select is workaround from not being able to use a calculated column directly in where clause |
60 |
final String SQL = "SELECT * FROM ( "+ |
final String SQL = "SELECT * FROM ( "+ |
61 |
" select imei,handle,groupname,latitude,longitude,updated, earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?)) AS calcdist " + |
" SELECT imei,handle,groupname,latitude,longitude, " + |
62 |
" from droidradar " + |
" extract(epoch from now())::int - extract(epoch from updated)::int AS lastupdate, " + |
63 |
" WHERE imei <> ? AND updated > now() - interval '15 minutes' ) AS droidradar2 " + |
" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(56,9))::int AS calcdist " + |
64 |
|
" FROM droidradar " + |
65 |
|
" WHERE imei <> ? AND updated > now() - interval '15 minutes' " + |
66 |
|
" ) AS droidradar2 " + |
67 |
"WHERE calcdist < 64000 " + |
"WHERE calcdist < 64000 " + |
68 |
"ORDER BY calcdist ASC "; |
"ORDER BY calcdist ASC "; |
69 |
|
|
92 |
buff.append("<group>").append(res.getString(3) ) .append("</group>\n"); |
buff.append("<group>").append(res.getString(3) ) .append("</group>\n"); |
93 |
buff.append("<latitude>").append(res.getString(4) ) .append("</latitude>\n"); |
buff.append("<latitude>").append(res.getString(4) ) .append("</latitude>\n"); |
94 |
buff.append("<longitude>").append(res.getString(5) ) .append("</longitude>\n"); |
buff.append("<longitude>").append(res.getString(5) ) .append("</longitude>\n"); |
95 |
buff.append("<updated>").append(res.getTimestamp(6) ) .append("</updated>\n"); |
buff.append("<lastupdate>").append(res.getInt(6) ) .append("</lastupdate>\n"); |
96 |
buff.append("<distance>").append(res.getDouble(7) ) .append("</distance>\n"); |
buff.append("<distance>").append(res.getDouble(7) ) .append("</distance>\n"); |
97 |
|
|
98 |
buff.append("</droid>\n"); |
buff.append("</droid>\n"); |