featureset_ptr sqlite_datasource::features(query const& q) const
{
if (!is_bound_) bind();
if (dataset_)
{
mapnik::box2d<double> const& e = q.get_bbox();
std::ostringstream s;
s << "SELECT " << geometry_field_ << "," << key_field_;
std::set<std::string> const& props = q.property_names();
std::set<std::string>::const_iterator pos = props.begin();
std::set<std::string>::const_iterator end = props.end();
while (pos != end)
{
s << ",\"" << *pos << "\"";
++pos;
}
s << " FROM ";
std::string query (table_);
if (use_spatial_index_)
{
std::ostringstream spatial_sql;
spatial_sql << std::setprecision(16);
spatial_sql << " WHERE " << key_field_ << " IN (SELECT pkid FROM idx_" << geometry_table_ << "_" << geometry_field_;
spatial_sql << " WHERE xmax>=" << e.minx() << " AND xmin<=" << e.maxx() ;
spatial_sql << " AND ymax>=" << e.miny() << " AND ymin<=" << e.maxy() << ")";
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", spatial_sql.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, geometry_table_))
{
boost::algorithm::ireplace_first(query, table_, table_ + " " + spatial_sql.str());
}
}
s << query ;
if (row_limit_ > 0) {
s << " LIMIT " << row_limit_;
}
if (row_offset_ > 0) {
s << " OFFSET " << row_offset_;
}
#ifdef MAPNIK_DEBUG
std::clog << "Sqlite Plugin: " << s.str() << std::endl;
#endif
boost::shared_ptr<sqlite_resultset> rs (dataset_->execute_query (s.str()));
return boost::make_shared<sqlite_featureset>(rs, desc_.get_encoding(), format_, multiple_geometries_);
}
return featureset_ptr();
}
featureset_ptr sqlite_datasource::features_at_point(coord2d const& pt) const
{
if (!is_bound_) bind();
if (dataset_)
{
// TODO - need tolerance
mapnik::box2d<double> const e(pt.x,pt.y,pt.x,pt.y);
std::ostringstream s;
s << "SELECT " << geometry_field_ << "," << key_field_;
std::vector<attribute_descriptor>::const_iterator itr = desc_.get_descriptors().begin();
std::vector<attribute_descriptor>::const_iterator end = desc_.get_descriptors().end();
while (itr != end)
{
std::string fld_name = itr->get_name();
if (fld_name != key_field_)
s << ",\"" << itr->get_name() << "\"";
++itr;
}
s << " FROM ";
std::string query (table_);
if (use_spatial_index_)
{
std::ostringstream spatial_sql;
spatial_sql << std::setprecision(16);
spatial_sql << " WHERE " << key_field_ << " IN (SELECT pkid FROM idx_" << geometry_table_ << "_" << geometry_field_;
spatial_sql << " WHERE xmax>=" << e.minx() << " AND xmin<=" << e.maxx() ;
spatial_sql << " AND ymax>=" << e.miny() << " AND ymin<=" << e.maxy() << ")";
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", spatial_sql.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, geometry_table_))
{
boost::algorithm::ireplace_first(query, table_, table_ + " " + spatial_sql.str());
}
}
s << query ;
if (row_limit_ > 0) {
s << " LIMIT " << row_limit_;
}
if (row_offset_ > 0) {
s << " OFFSET " << row_offset_;
}
#ifdef MAPNIK_DEBUG
std::clog << "Sqlite Plugin: " << s.str() << std::endl;
#endif
boost::shared_ptr<sqlite_resultset> rs (dataset_->execute_query (s.str()));
return boost::make_shared<sqlite_featureset>(rs, desc_.get_encoding(), format_, multiple_geometries_);
}
return featureset_ptr();
}
featureset_ptr occi_datasource::features(query const& q) const
{
if (! is_bound_) bind();
#ifdef MAPNIK_STATS
mapnik::progress_timer __stats__(std::clog, "occi_datasource::features");
#endif
box2d<double> const& box = q.get_bbox();
std::ostringstream s;
s << "SELECT ";
if (use_wkb_)
{
s << "SDO_UTIL.TO_WKBGEOMETRY(" << geometry_field_ << ")";
}
else
{
s << geometry_field_;
}
std::set<std::string> const& props = q.property_names();
std::set<std::string>::const_iterator pos = props.begin();
std::set<std::string>::const_iterator end = props.end();
mapnik::context_ptr ctx = boost::make_shared<mapnik::context_type>();
for (; pos != end; ++pos)
{
s << ", " << *pos;
ctx->push(*pos);
}
s << " FROM ";
std::string query(table_);
if (use_spatial_index_)
{
std::ostringstream spatial_sql;
spatial_sql << std::setprecision(16);
spatial_sql << " WHERE SDO_FILTER(" << geometry_field_ << ",";
spatial_sql << " MDSYS.SDO_GEOMETRY(" << SDO_GTYPE_2DPOLYGON << "," << srid_ << ",NULL,";
spatial_sql << " MDSYS.SDO_ELEM_INFO_ARRAY(1," << SDO_ETYPE_POLYGON << "," << SDO_INTERPRETATION_RECTANGLE << "),";
spatial_sql << " MDSYS.SDO_ORDINATE_ARRAY(";
spatial_sql << box.minx() << "," << box.miny() << ", ";
spatial_sql << box.maxx() << "," << box.maxy() << ")), 'querytype=WINDOW') = 'TRUE'";
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", spatial_sql.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, table_name_))
{
boost::algorithm::ireplace_first(query, table_name_, table_name_ + " " + spatial_sql.str());
}
else
{
MAPNIK_LOG_WARN(occi) << "occi_datasource: cannot determine where to add the spatial filter declaration";
}
}
if (row_limit_ > 0)
{
std::ostringstream row_limit_string;
row_limit_string << "rownum < " << row_limit_;
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", row_limit_string.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, table_name_))
{
boost::algorithm::ireplace_first(query, table_name_, table_name_ + " " + row_limit_string.str());
}
else
{
MAPNIK_LOG_WARN(occi) << "occi_datasource: Cannot determine where to add the row limit declaration";
}
}
s << query;
MAPNIK_LOG_DEBUG(occi) << "occi_datasource: " << s.str();
return boost::make_shared<occi_featureset>(pool_,
conn_,
ctx,
s.str(),
desc_.get_encoding(),
use_connection_pool_,
use_wkb_,
row_prefetch_);
}
featureset_ptr occi_datasource::features_at_point(coord2d const& pt) const
{
if (! is_bound_) bind();
#ifdef MAPNIK_STATS
mapnik::progress_timer __stats__(std::clog, "occi_datasource::features_at_point");
#endif
std::ostringstream s;
s << "SELECT ";
if (use_wkb_)
{
s << "SDO_UTIL.TO_WKBGEOMETRY(" << geometry_field_ << ")";
}
else
{
s << geometry_field_;
}
std::vector<attribute_descriptor>::const_iterator itr = desc_.get_descriptors().begin();
std::vector<attribute_descriptor>::const_iterator end = desc_.get_descriptors().end();
mapnik::context_ptr ctx = boost::make_shared<mapnik::context_type>();
while (itr != end)
{
s << ", " << itr->get_name();
ctx->push(itr->get_name());
++itr;
}
s << " FROM ";
std::string query(table_);
if (use_spatial_index_)
{
std::ostringstream spatial_sql;
spatial_sql << std::setprecision(16);
spatial_sql << " WHERE SDO_FILTER(" << geometry_field_ << ",";
spatial_sql << " MDSYS.SDO_GEOMETRY(" << SDO_GTYPE_2DPOINT << "," << srid_ << ",NULL,";
spatial_sql << " MDSYS.SDO_ELEM_INFO_ARRAY(1," << SDO_ETYPE_POINT << "," << SDO_INTERPRETATION_POINT << "),";
spatial_sql << " MDSYS.SDO_ORDINATE_ARRAY(";
spatial_sql << pt.x << "," << pt.y << ")), 'querytype=WINDOW') = 'TRUE'";
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", spatial_sql.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, table_name_))
{
boost::algorithm::ireplace_first(query, table_name_, table_name_ + " " + spatial_sql.str());
}
else
{
MAPNIK_LOG_WARN(occi) << "occi_datasource: Cannot determine where to add the spatial filter declaration";
}
}
if (row_limit_ > 0)
{
std::ostringstream row_limit_string;
row_limit_string << "rownum < " << row_limit_;
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", row_limit_string.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, table_name_))
{
boost::algorithm::ireplace_first(query, table_name_, table_name_ + " " + row_limit_string.str());
}
else
{
MAPNIK_LOG_WARN(occi) << "occi_datasource: Cannot determine where to add the row limit declaration";
}
}
s << query;
MAPNIK_LOG_DEBUG(occi) << "occi_datasource: " << s.str();
return boost::make_shared<occi_featureset>(pool_,
conn_,
ctx,
s.str(),
desc_.get_encoding(),
use_connection_pool_,
use_wkb_,
row_prefetch_);
}
featureset_ptr sqlite_datasource::features_at_point(coord2d const& pt, double tol) const
{
#ifdef MAPNIK_STATS
mapnik::progress_timer __stats__(std::clog, "sqlite_datasource::features_at_point");
#endif
if (dataset_)
{
mapnik::box2d<double> e(pt.x, pt.y, pt.x, pt.y);
e.pad(tol);
std::ostringstream s;
mapnik::context_ptr ctx = std::make_shared<mapnik::context_type>();
s << "SELECT " << geometry_field_;
if (!key_field_.empty())
{
s << "," << key_field_;
ctx->push(key_field_);
}
std::vector<attribute_descriptor>::const_iterator itr = desc_.get_descriptors().begin();
std::vector<attribute_descriptor>::const_iterator end = desc_.get_descriptors().end();
for ( ; itr != end; ++itr)
{
std::string fld_name = itr->get_name();
if (fld_name != key_field_)
{
s << ",[" << itr->get_name() << "]";
ctx->push(itr->get_name());
}
}
s << " FROM ";
std::string query(table_);
if (! key_field_.empty() && has_spatial_index_)
{
// TODO - debug warn if fails
sqlite_utils::apply_spatial_filter(query,
e,
table_,
key_field_,
index_table_,
geometry_table_,
intersects_token_);
}
else
{
query = populate_tokens(table_);
}
s << query ;
if (row_limit_ > 0)
{
s << " LIMIT " << row_limit_;
}
if (row_offset_ > 0)
{
s << " OFFSET " << row_offset_;
}
MAPNIK_LOG_DEBUG(sqlite) << "sqlite_datasource: " << s.str();
std::shared_ptr<sqlite_resultset> rs(dataset_->execute_query(s.str()));
return std::make_shared<sqlite_featureset>(rs,
ctx,
desc_.get_encoding(),
e,
format_,
has_spatial_index_,
using_subquery_);
}
return featureset_ptr();
}
featureset_ptr sqlite_datasource::features(query const& q) const
{
#ifdef MAPNIK_STATS
mapnik::progress_timer __stats__(std::clog, "sqlite_datasource::features");
#endif
if (dataset_)
{
mapnik::box2d<double> const& e = q.get_bbox();
std::ostringstream s;
mapnik::context_ptr ctx = std::make_shared<mapnik::context_type>();
s << "SELECT " << geometry_field_;
if (!key_field_.empty())
{
s << "," << key_field_;
ctx->push(key_field_);
}
std::set<std::string> const& props = q.property_names();
std::set<std::string>::const_iterator pos = props.begin();
std::set<std::string>::const_iterator end = props.end();
for ( ;pos != end;++pos)
{
// TODO - should we restrict duplicate key query?
//if (*pos != key_field_)
s << ",[" << *pos << "]";
ctx->push(*pos);
}
s << " FROM ";
std::string query(table_);
if (! key_field_.empty() && has_spatial_index_)
{
// TODO - debug warn if fails
sqlite_utils::apply_spatial_filter(query,
e,
table_,
key_field_,
index_table_,
geometry_table_,
intersects_token_);
}
else
{
query = populate_tokens(table_);
}
s << query ;
if (row_limit_ > 0)
{
s << " LIMIT " << row_limit_;
}
if (row_offset_ > 0)
{
s << " OFFSET " << row_offset_;
}
MAPNIK_LOG_DEBUG(sqlite) << "sqlite_datasource: " << s.str();
std::shared_ptr<sqlite_resultset> rs(dataset_->execute_query(s.str()));
return std::make_shared<sqlite_featureset>(rs,
ctx,
desc_.get_encoding(),
e,
format_,
has_spatial_index_,
using_subquery_);
}
return featureset_ptr();
}
featureset_ptr occi_datasource::features_at_point(coord2d const& pt) const
{
if (!is_bound_) bind();
std::ostringstream s;
s << "SELECT " << geometry_field_;
std::vector<attribute_descriptor>::const_iterator itr = desc_.get_descriptors().begin();
std::vector<attribute_descriptor>::const_iterator end = desc_.get_descriptors().end();
unsigned size=0;
while (itr != end)
{
s << ", " << itr->get_name();
++itr;
++size;
}
s << " FROM ";
std::string query (table_);
std::string table_name = mapnik::table_from_sql(query);
if (use_spatial_index_)
{
std::ostringstream spatial_sql;
spatial_sql << std::setprecision(16);
spatial_sql << " WHERE SDO_FILTER(" << geometry_field_ << ",";
spatial_sql << " MDSYS.SDO_GEOMETRY(" << SDO_GTYPE_2DPOINT << "," << srid_ << ",NULL,";
spatial_sql << " MDSYS.SDO_ELEM_INFO_ARRAY(1," << SDO_ETYPE_POINT << "," << SDO_INTERPRETATION_POINT << "),";
spatial_sql << " MDSYS.SDO_ORDINATE_ARRAY(";
spatial_sql << pt.x << "," << pt.y << ")), 'querytype=WINDOW') = 'TRUE'";
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", spatial_sql.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query,table_name))
{
boost::algorithm::ireplace_first(query, table_name, table_name + " " + spatial_sql.str());
}
else
{
#ifdef MAPNIK_DEBUG
std::clog << "OCCI Plugin: cannot determine where to add the spatial filter declaration" << std::endl;
#endif
}
}
if (row_limit_ > 0)
{
std::string row_limit_string = "rownum < " + row_limit_;
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", row_limit_string + " AND ");
}
else if (boost::algorithm::ifind_first(query, table_name))
{
boost::algorithm::ireplace_first(query, table_name, table_name + " " + row_limit_string);
}
else
{
#ifdef MAPNIK_DEBUG
std::clog << "OCCI Plugin: cannot determine where to add the row limit declaration" << std::endl;
#endif
}
}
s << query;
#ifdef MAPNIK_DEBUG
std::clog << "OCCI Plugin: " << s.str() << std::endl;
#endif
return featureset_ptr (new occi_featureset (pool_,
conn_,
s.str(),
desc_.get_encoding(),
multiple_geometries_,
use_connection_pool_,
row_prefetch_,
size));
}
featureset_ptr occi_datasource::features(query const& q) const
{
if (!is_bound_) bind();
box2d<double> const& box=q.get_bbox();
std::ostringstream s;
s << "SELECT " << geometry_field_;
std::set<std::string> const& props = q.property_names();
std::set<std::string>::const_iterator pos = props.begin();
std::set<std::string>::const_iterator end = props.end();
while (pos != end)
{
s << ", " << *pos;
++pos;
}
s << " FROM ";
std::string query (table_);
std::string table_name = mapnik::table_from_sql(query);
if (use_spatial_index_)
{
std::ostringstream spatial_sql;
spatial_sql << std::setprecision(16);
spatial_sql << " WHERE SDO_FILTER(" << geometry_field_ << ",";
spatial_sql << " MDSYS.SDO_GEOMETRY(" << SDO_GTYPE_2DPOLYGON << "," << srid_ << ",NULL,";
spatial_sql << " MDSYS.SDO_ELEM_INFO_ARRAY(1," << SDO_ETYPE_POLYGON << "," << SDO_INTERPRETATION_RECTANGLE << "),";
spatial_sql << " MDSYS.SDO_ORDINATE_ARRAY(";
spatial_sql << box.minx() << "," << box.miny() << ", ";
spatial_sql << box.maxx() << "," << box.maxy() << ")), 'querytype=WINDOW') = 'TRUE'";
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", spatial_sql.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, table_name))
{
boost::algorithm::ireplace_first(query, table_name, table_name + " " + spatial_sql.str());
}
else
{
#ifdef MAPNIK_DEBUG
std::clog << "OCCI Plugin: cannot determine where to add the spatial filter declaration" << std::endl;
#endif
}
}
if (row_limit_ > 0)
{
std::string row_limit_string = "rownum < " + row_limit_;
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", row_limit_string + " AND ");
}
else if (boost::algorithm::ifind_first(query, table_name))
{
boost::algorithm::ireplace_first(query, table_name, table_name + " " + row_limit_string);
}
else
{
#ifdef MAPNIK_DEBUG
std::clog << "OCCI Plugin: cannot determine where to add the row limit declaration" << std::endl;
#endif
}
}
s << query;
#ifdef MAPNIK_DEBUG
std::clog << "OCCI Plugin: " << s.str() << std::endl;
#endif
return featureset_ptr (new occi_featureset (pool_,
conn_,
s.str(),
desc_.get_encoding(),
multiple_geometries_,
use_connection_pool_,
row_prefetch_,
props.size()));
}
featureset_ptr spatialite_datasource::features(query const& q) const
{
if (!is_bound_) bind();
if (dataset_)
{
mapnik::box2d<double> const& e = q.get_bbox();
std::ostringstream s;
s << "SELECT " << geometry_field_ << "," << key_field_;
std::set<std::string> const& props = q.property_names();
std::set<std::string>::const_iterator pos = props.begin();
std::set<std::string>::const_iterator end = props.end();
while (pos != end)
{
s << ",\"" << *pos << "\"";
++pos;
}
s << " FROM ";
std::string query (table_);
/* todo
throw if select * and key_field == rowid?
or add schema support so sqlite throws
*/
if (has_spatial_index_)
{
/*
Use rtree to limit record id's to a given bbox
then a btree to pull the records for those ids.
*/
std::ostringstream spatial_sql;
spatial_sql << std::setprecision(16);
spatial_sql << " WHERE " << key_field_ << " IN (SELECT pkid FROM " << index_table_;
spatial_sql << " WHERE xmax>=" << e.minx() << " AND xmin<=" << e.maxx() ;
spatial_sql << " AND ymax>=" << e.miny() << " AND ymin<=" << e.maxy() << ")";
if (boost::algorithm::ifind_first(query, "WHERE"))
{
boost::algorithm::ireplace_first(query, "WHERE", spatial_sql.str() + " AND ");
}
else if (boost::algorithm::ifind_first(query, geometry_table_))
{
boost::algorithm::ireplace_first(query, table_, table_ + " " + spatial_sql.str());
}
/* support UNION queries */
// #if 0
if(boost::algorithm::ifind_first(query, "union"))
{
/* use spatial index for UNION query, too */
boost::algorithm::ireplace_last(query, "WHERE", spatial_sql.str() + " AND ");
}
// #endif
}
s << query ;
if (row_limit_ > 0) {
s << " LIMIT " << row_limit_;
}
if (row_offset_ > 0) {
s << " OFFSET " << row_offset_;
}
#ifdef MAPNIK_DEBUG
std::clog << "Spatialite Plugin: table_: " << table_ << "\n\n";
std::clog << "Spatialite Plugin: query:" << s.str() << "\n\n";
#endif
boost::shared_ptr<sqlite_resultset> rs(dataset_->execute_query(s.str()));
return boost::make_shared<spatialite_featureset>(rs, desc_.get_encoding(), format_, multiple_geometries_, using_subquery_);
}
return featureset_ptr();
}
