[Opentrep-svn] SF.net SVN: opentrep:[227] trunk/opentrep/opentrep
Status: Beta
Brought to you by:
denis_arnaud
Menu
▾
▴
[Opentrep-svn] SF.net SVN: opentrep:[227] trunk/opentrep/opentrep
|
From: <den...@us...> - 2010-09-05 18:56:18
|
Revision: 227
http://opentrep.svn.sourceforge.net/opentrep/?rev=227&view=rev
Author: denis_arnaud
Date: 2010-09-05 18:56:12 +0000 (Sun, 05 Sep 2010)
Log Message:
-----------
[Dev] Added a SQL request for getting closest places to some given coordinates (there is still some work to do).
Modified Paths:
--------------
trunk/opentrep/opentrep/Location.hpp
trunk/opentrep/opentrep/bom/Place.hpp
trunk/opentrep/opentrep/command/DBManager.cpp
trunk/opentrep/opentrep/command/DBManager.hpp
trunk/opentrep/opentrep/command/IndexBuilder.cpp
Modified: trunk/opentrep/opentrep/Location.hpp
===================================================================
--- trunk/opentrep/opentrep/Location.hpp 2010-09-05 16:25:14 UTC (rev 226)
+++ trunk/opentrep/opentrep/Location.hpp 2010-09-05 18:56:12 UTC (rev 227)
@@ -5,8 +5,7 @@
// Import section
// //////////////////////////////////////////////////////////////////////
// STL
-#include <istream>
-#include <ostream>
+#include <iosfwd>
#include <string>
#include <list>
// OpenTrep
Modified: trunk/opentrep/opentrep/bom/Place.hpp
===================================================================
--- trunk/opentrep/opentrep/bom/Place.hpp 2010-09-05 16:25:14 UTC (rev 226)
+++ trunk/opentrep/opentrep/bom/Place.hpp 2010-09-05 18:56:12 UTC (rev 227)
@@ -5,8 +5,7 @@
// Import section
// //////////////////////////////////////////////////////////////////////
// STL
-#include <istream>
-#include <ostream>
+#include <iosfwd>
#include <string>
#include <map>
// OpenTrep Bom
Modified: trunk/opentrep/opentrep/command/DBManager.cpp
===================================================================
--- trunk/opentrep/opentrep/command/DBManager.cpp 2010-09-05 16:25:14 UTC (rev 226)
+++ trunk/opentrep/opentrep/command/DBManager.cpp 2010-09-05 18:56:12 UTC (rev 227)
@@ -1,15 +1,14 @@
// //////////////////////////////////////////////////////////////////////
// Import section
// //////////////////////////////////////////////////////////////////////
-// C
-#include <assert.h>
+// STL
+#include <cassert>
// SOCI
#include <soci/core/soci.h>
#include <soci/backends/mysql/soci-mysql.h>
// OpenTrep
#include <opentrep/bom/World.hpp>
#include <opentrep/bom/Place.hpp>
-#include <opentrep/factory/FacWorld.hpp>
#include <opentrep/factory/FacPlace.hpp>
#include <opentrep/dbadaptor/DbaPlace.hpp>
#include <opentrep/command/DBManager.hpp>
@@ -18,9 +17,10 @@
namespace OPENTREP {
// //////////////////////////////////////////////////////////////////////
- void DBManager::prepareSelectStatement (soci::session& ioSociSession,
- soci::statement& ioSelectStatement,
- Place& ioPlace) {
+ void DBManager::
+ prepareSelectFromCodeStatement (soci::session& ioSociSession,
+ soci::statement& ioSelectStatement,
+ Place& ioPlace) {
try {
@@ -60,6 +60,78 @@
// //////////////////////////////////////////////////////////////////////
void DBManager::
+ prepareSelectFromCoordStatement (soci::session& ioSociSession,
+ soci::statement& ioSelectStatement,
+ const double& iLatitude,
+ const double& iLongitude) {
+
+ try {
+
+ // Instanciate a SQL statement (no request is performed at that stage)
+ /**
+ select rpd.code AS code, city_code, xapian_docid, is_airport, is_city,
+ is_main, is_commercial, state_code, country_code, region_code,
+ continent_code, time_zone_grp, longitude, latitude, language_code,
+ classical_name, extended_name, alternate_name1, alternate_name2,
+ alternate_name3, alternate_name4, alternate_name5, alternate_name6,
+ alternate_name7, alternate_name8, alternate_name9, alternate_name10
+ from ref_place_details rpd, ref_place_names rpn
+ where rpd.code = iPlaceCode
+ and rpn.code = rpd.code;
+
+ select (airpop.tpax)/1000 as 'popularity',
+ places.code as 'airport_code', places.code as 'city_code',
+ places.longitude as 'longitude', places.latitude as 'latitude'
+ from airport_popularity AS airpop, ref_place_details AS places
+ WHERE places.longitude >= ${PL_LON_LOWER}
+ AND places.longitude <= ${PL_LON_UPPER}
+ AND places.latitude >= ${PL_LAT_LOWER}
+ AND places.latitude <= ${PL_LAT_UPPER}
+ AND airpop.airport_code = places.code
+ AND places.is_city = 'y'
+ AND names.code = places.code
+ ORDER BY airpop.tpax DESC
+ */
+ Place& lPlace = FacPlace::instance().create();
+ const double K_ERROR = 2.0;
+ const double lLowerBoundLatitude = iLatitude - K_ERROR;
+ const double lUpperBoundLatitude = iLatitude + K_ERROR;
+ const double lLowerBoundLongitude = iLongitude - K_ERROR;
+ const double lUpperBoundLongitude = iLongitude + K_ERROR;
+
+ ioSelectStatement =
+ (ioSociSession.prepare
+ << "select rpd.code AS code, city_code, xapian_docid, is_airport, "
+ << "is_city, is_main, is_commercial, state_code, country_code, "
+ << "region_code, continent_code, time_zone_grp, longitude, latitude, "
+ << "language_code, classical_name, extended_name, "
+ << "alternate_name1, alternate_name2, alternate_name3, "
+ << "alternate_name4, alternate_name5, alternate_name6, "
+ << "alternate_name7, alternate_name8, alternate_name9, "
+ << "alternate_name10 "
+ << "from ref_place_details rpd, ref_place_names rpn, "
+ << " popularity pop "
+ << "where latitude >= :lower_latitude "
+ << " and latitude <= :upper_latitude "
+ << " and longitude >= :lower_longitude "
+ << " and longitude <= :upper_longitude "
+ << " and rpn.code = rpd.code"
+ << " and pop.airport_code = rpd.code",
+ soci::into (lPlace), soci::use (lLowerBoundLatitude),
+ soci::use (lUpperBoundLatitude), soci::use (lLowerBoundLongitude),
+ soci::use (lUpperBoundLongitude));
+
+ // Execute the SQL query
+ ioSelectStatement.execute();
+
+ } catch (std::exception const& lException) {
+ OPENTREP_LOG_ERROR ("Error: " << lException.what());
+ throw SQLDatabaseException();
+ }
+ }
+
+ // //////////////////////////////////////////////////////////////////////
+ void DBManager::
prepareSelectOnPlaceCodeStatement (soci::session& ioSociSession,
soci::statement& ioSelectStatement,
const std::string& iPlaceCode,
@@ -248,4 +320,22 @@
return oHasRetrievedPlace;
}
+ // //////////////////////////////////////////////////////////////////////
+ bool DBManager::retrieveClosestPlaces (soci::session& ioSociSession,
+ const double& iLatitude,
+ const double& iLongitude,
+ PlaceOrderedList_T& ioPlaceList) {
+ bool oHasRetrievedPlace = false;
+
+ try {
+
+
+ } catch (std::exception const& lException) {
+ OPENTREP_LOG_ERROR ("Error: " << lException.what());
+ throw SQLDatabaseException();
+ }
+
+ return oHasRetrievedPlace;
+ }
+
}
Modified: trunk/opentrep/opentrep/command/DBManager.hpp
===================================================================
--- trunk/opentrep/opentrep/command/DBManager.hpp 2010-09-05 16:25:14 UTC (rev 226)
+++ trunk/opentrep/opentrep/command/DBManager.hpp 2010-09-05 18:56:12 UTC (rev 227)
@@ -6,6 +6,7 @@
// //////////////////////////////////////////////////////////////////////
// OpenTrep
#include <opentrep/OPENTREP_Types.hpp>
+#include <opentrep/bom/PlaceList.hpp>
// Forward declarations
namespace soci {
@@ -15,45 +16,89 @@
namespace OPENTREP {
- // Forward declarations
- class Place;
-
/** Class building the Business Object Model (BOM) from data retrieved
from the database. */
class DBManager {
public:
/** Update the Xapian document ID field of the database row
- corresponding to the given Place object. */
+ corresponding to the given Place object.
+ @param soci::session& SOCI session handler.
+ @param const Place& The place to be updated. */
static void updatePlaceInDB (soci::session&, const Place&);
/** Retrieve, from the (MySQL) database, the row corresponding to
the given place code (e.g., 'sfo' for San Francisco Intl
airport), and fill the given Place object with that retrieved
- data. */
+ data.
+ @param soci::session& SOCI session handler.
+ @param const std::string& The code of the place to be retrieved.
+ @param Place& The object corresponding to the place to be retrieved.
+ It has to be given empty, and is filled by the method. */
static bool retrievePlace (soci::session&, const std::string& iPlaceCode,
Place&);
+ /** Retrieve, from the (MySQL) database, the row corresponding to
+ the given place code (e.g., 'sfo' for San Francisco Intl
+ airport), and fill the given Place object with that retrieved
+ data.
+ @param soci::session& SOCI session handler.
+ @param const std::double& The latitude of the place to be retrieved.
+ @param const std::double& The longitude of the place to be retrieved.
+ @param PlaceOrderedList_T& The list corresponding to the places to be
+ retrieved. It has to be given empty, and is filled by the
+ method. */
+ static bool retrieveClosestPlaces (soci::session&, const double& iLatitude,
+ const double& iLongitude,
+ PlaceOrderedList_T&);
+
public:
- /** Prepare (parse and put in cache) the SQL statement. */
- static void prepareSelectStatement (soci::session&, soci::statement&,
- Place&);
+ /** Prepare (parse and put in cache) the SQL statement.
+ @param soci::session& SOCI session handler.
+ @param soci::statement& SOCI SQL statement handler.
+ @param Place& The object corresponding to the place to be retrieved.
+ It has to be given empty, and is filled by the method. */
+ static void prepareSelectFromCodeStatement (soci::session&,
+ soci::statement&, Place&);
+ /** Prepare (parse and put in cache) the SQL statement.
+ @param soci::session& SOCI session handler.
+ @param soci::statement& SOCI SQL statement handler.
+ @param Place& The object corresponding to the place to be retrieved.
+ It has to be given empty, and is filled by the method. */
+ static void prepareSelectFromCoordStatement (soci::session&,
+ soci::statement&,
+ const double& iLatitude,
+ const double& iLongitude);
+
/** Iterate on the SQL statement.
<br>The SQL has to be already prepared.
- @parameter const bool Tells whether the Place object should be reset. */
+ @param soci::statement& SOCI SQL statement handler.
+ @param Place& The object corresponding to the place to be retrieved.
+ It has to be given empty, and is filled by the method.
+ @param const bool Tells whether the Place object should be reset. */
static bool iterateOnStatement (soci::statement&, Place&,
const bool iShouldDoReset);
private:
- /** Prepare (parse and put in cache) the SQL statement. */
+ /** Prepare (parse and put in cache) the SQL statement.
+ @param soci::session& SOCI session handler.
+ @param soci::statement& SOCI SQL statement handler.
+ @param const std::string& The code of the place to be retrieved.
+ @param Place& The object corresponding to the place to be retrieved.
+ It has to be given empty, and is filled by the method. */
static void prepareSelectOnPlaceCodeStatement(soci::session&,
soci::statement&,
const std::string& iPlaceCode,
Place&);
- /** Prepare (parse and put in cache) the SQL statement. */
+ /** Prepare (parse and put in cache) the SQL statement.
+ @param soci::session& SOCI session handler.
+ @param soci::statement& SOCI SQL statement handler.
+ @param const XapianDocID_T& The code of the place to be retrieved.
+ @param Place& The object corresponding to the place to be retrieved.
+ It has to be given empty, and is filled by the method. */
static void prepareSelectOnDocIDStatement (soci::session&, soci::statement&,
const XapianDocID_T&, Place&);
Modified: trunk/opentrep/opentrep/command/IndexBuilder.cpp
===================================================================
--- trunk/opentrep/opentrep/command/IndexBuilder.cpp 2010-09-05 16:25:14 UTC (rev 226)
+++ trunk/opentrep/opentrep/command/IndexBuilder.cpp 2010-09-05 18:56:12 UTC (rev 227)
@@ -154,8 +154,8 @@
// Prepare and execute the select statement
soci::statement lSelectStatement (ioSociSession);
- DBManager::prepareSelectStatement (ioSociSession, lSelectStatement,
- lPlace);
+ DBManager::prepareSelectFromCodeStatement (ioSociSession,
+ lSelectStatement, lPlace);
// Create the Xapian database (index)
Xapian::WritableDatabase lDatabase (iTravelDatabaseName,
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
