const tissuestack::imaging::TissueStackLabelLookup * tissuestack::database::LabelLookupDataProvider::queryLookupValuesByFileName(
const std::string file_name) {
if (file_name.empty())
return nullptr;
const std::string sql =
"SELECT * FROM dataset_values_lookup WHERE filename='"
+ file_name + "';";
tissuestack::imaging::TissueStackLabelLookup * ret = nullptr;
const pqxx::result results =
tissuestack::database::TissueStackPostgresConnector::instance()->executeNonTransactionalQuery(sql);
if (results.size() == 0) return ret;
if (results.size() > 1)
THROW_TS_EXCEPTION(tissuestack::common::TissueStackApplicationException,
"Unique key based search returned more than 1 record!");
for (pqxx::result::const_iterator look = results.begin(); look != results.end(); ++look)
{
tissuestack::database::AtlasInfo * atlasInfo = nullptr;
if (!look["atlas_association"].is_null())
{
// go query associated atlas info
const std::string innerSql =
"SELECT * FROM atlas_info WHERE id=" +
std::to_string(look["atlas_association"].as<unsigned long long int>()) + ";";
const pqxx::result innerResults =
tissuestack::database::TissueStackPostgresConnector::instance()->executeNonTransactionalQuery(innerSql);
if (innerResults.size() == 1)
{
for (pqxx::result::const_iterator atlas = innerResults.begin(); atlas != innerResults.end(); ++atlas)
{
atlasInfo = new tissuestack::database::AtlasInfo(
atlas["id"].as<unsigned long long int>(),
atlas["atlas_prefix"].as<std::string>(),
atlas["atlas_description"].as<std::string>(),
atlas["atlas_query_url"].is_null() ? "" :
atlas["atlas_query_url"].as<std::string>());
break;
}
}
}
// construct lookup label from db info
ret = const_cast<tissuestack::imaging::TissueStackLabelLookup * >(
tissuestack::imaging::TissueStackLabelLookup::fromDataBaseId(
look["id"].as<unsigned long long int>(),
look["filename"].as<std::string>(),
look["content"].as<std::string>(),
atlasInfo));
break;
}
return ret;
}
void getAddress(float lat, float lon,std::string &address){
bool placeIDExist(false);
mapValue.clear();
pqxx::nontransaction nType((*ptrConn));
for (int i=0; i < 10; ++i){
sql="select place_id,parent_place_id,rank_search from placex WHERE ST_DWithin(ST_SetSRID(ST_Point("+std::to_string(lon)+","+std::to_string(lat)+"),4326), "
"geometry, "+std::to_string(radius)+") and rank_search != 28 and rank_search >= 26 and (name is not null or housenumber is not null) and"
" class not in ('waterway','railway','tunnel','bridge') and indexed_status = 0 and "
"(ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') "
"OR ST_DWithin(ST_SetSRID(ST_Point("+std::to_string(lon)+","+std::to_string(lat)+"),4326), centroid, "+std::to_string(radius)+")) "
"ORDER BY ST_distance(ST_SetSRID(ST_Point("+std::to_string(lon)+","+std::to_string(lat)+"),4326), geometry) ASC limit 1";
//std::cout << sql << std::endl;
resultSet = nType.exec(sql);
if (!resultSet.empty()) {
for (pqxx::result::const_iterator c = resultSet.begin(); c != resultSet.end(); ++c){
//cout << '\t' << c[0].as(string()) << '\t' << c[1].as(string()) <<'\t' << c[2].as(string()) <<endl;
place_id = c[0].as(std::string());
}
placeIDExist=true;
break;
}
radius*=2;
}// end of for loop
if(placeIDExist){
resultSet = nType.exec("select name,class,type from get_addressdata(" + place_id + ") where isaddress order by rank_address asc");
if (!resultSet.empty()) {
for (pqxx::result::const_iterator c = resultSet.begin(); c != resultSet.end(); ++c){
parseLocation( c[0].as(std::string()), c[1].as(std::string()), c[2].as(std::string()));
}
}
}
printAddress(address);
} // end of func getAddress
HumanWithVolumeRecordMap HumanAccessorPostgresql::prepareResultGetRecords(
pqxx::result const & a_result,
IDHolder const & a_id_holder
) const
{
// Create a result map.
HumanWithVolumeRecordMap records;
// Prepare types for the values to be written to.
string key;
Volume volume;
for (pqxx::result::const_iterator it = a_result.begin(); it != a_result.end(); ++it)
{
// Get the values.
it["human_key"].to(key);
it["volume"].to(volume);
// Create a corresponding record.
HumanWithVolumeRecordShrPtr record = make_shared<HumanWithVolumeRecord>(a_id_holder, key, volume);
// Create a pair.
HumanWithVolumeRecordPair pair(key, record);
// Add record to the result.
records.insert(pair);
}
return records;
}
ILandRecordMap LandAccessorPostgresql::prepareResultGetRecords(
pqxx::result const & a_result
) const
{
string login;
string world_name;
string land_name;
int turns;
bool granted;
ILandRecordMap records;
for (pqxx::result::const_iterator it = a_result.begin(); it != a_result.end(); ++it)
{
it["login"].to(login);
it["world_name"].to(world_name);
it["land_name"].to(land_name);
it["turns"].to(turns);
it["granted"].to(granted);
ILandRecordShrPtr record = ILandRecordShrPtr(new LandRecord(login, world_name, land_name, turns, granted));
ILandRecordPair pair(land_name, record);
records.insert(pair);
}
return records;
}
/// Perform the transaction itself
void operator ()(pqxx::work & t)
{
// Setup wci:
t.exec("SELECT wci.begin( 'wcitest', 999, 999, 999 )");
// Request BLOB oid:
const pqxx::result result = t.exec(myQuery);
// Fetch each BLOB, and store it in the return object
for (pqxx::result::const_iterator it = result.begin(); it != result.end(); ++it )
{
const pqxx::result::field & f = (*it)["value"];
// Fetch the grid, remember to check for NULL
float value = std::numeric_limits<float>::quiet_NaN();
if ( not f.is_null() )
value = f.as<float>();
// Store data:
const std::string identifier = (*it)["validtimeto"].as<std::string>() + " - " + (*it)["valueparametername"].as<std::string>();
internalStorage_[identifier] = value;
}
}