30 |
protected String getStations(Connection conn, double latitude, double longitude) throws SQLException { |
protected String getStations(Connection conn, double latitude, double longitude) throws SQLException { |
31 |
//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 |
32 |
final String SQL = "SELECT * FROM ( "+ |
final String SQL = "SELECT * FROM ( "+ |
33 |
" SELECT name,latitude,longitude,stationcode, " + |
" SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, " + |
34 |
" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + |
" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + |
35 |
" FROM trainstations " + |
" FROM trainstations " + |
36 |
" WHERE latitude IS NOT NULL AND longitude IS NOT NULL " + |
" WHERE latitude IS NOT NULL AND longitude IS NOT NULL " + |
58 |
while (res.next()) { |
while (res.next()) { |
59 |
buff.append("<station>\n"); |
buff.append("<station>\n"); |
60 |
|
|
61 |
buff.append("<name>").append( res.getString(1) ) .append("</name>\n"); |
buff.append("<id>").append( res.getInt(1) ).append("</id>\n"); |
62 |
buff.append("<latitude>").append( res.getDouble(2) ) .append("</latitude>\n"); |
buff.append("<name>").append( res.getString(2) ) .append("</name>\n"); |
63 |
buff.append("<longitude>").append( res.getDouble(3) ) .append("</longitude>\n"); |
buff.append("<latitude>").append( res.getDouble(3) ) .append("</latitude>\n"); |
64 |
buff.append("<stationcode>").append( res.getString(4) ) .append("</stationcode>\n"); |
buff.append("<longitude>").append( res.getDouble(4) ) .append("</longitude>\n"); |
65 |
buff.append("<calcdist>").append( res.getInt(5) ) .append("</calcdist>\n"); |
res.getString(5); |
66 |
|
buff.append("<fjerntog>").append( !res.wasNull() ) .append("</fjerntog>\n"); |
67 |
|
res.getString(6); |
68 |
|
buff.append("<stog>").append( !res.wasNull() ) .append("</stog>\n"); |
69 |
|
buff.append("<calcdist>").append( res.getInt(7) ) .append("</calcdist>\n"); |
70 |
|
|
71 |
buff.append("</station>\n"); |
buff.append("</station>\n"); |
72 |
} |
} |