예제 #1
0
postgis_datasource::~postgis_datasource()
{
    if (is_bound_ && ! persist_connection_)
    {
        ConnectionManager* mgr = ConnectionManager::instance();
        shared_ptr< Pool<Connection,ConnectionCreator> > pool = mgr->getPool(creator_.id());
        if (pool)
        {
            shared_ptr<Connection> conn = pool->borrowObject();
            if (conn)
            {
                conn->close();
            }
        }
    }
}
예제 #2
0
boost::optional<mapnik::datasource::geometry_t> postgis_datasource::get_geometry_type() const
{
    if (! is_bound_)
    {
        bind();
    }

    boost::optional<mapnik::datasource::geometry_t> result;

    ConnectionManager* mgr = ConnectionManager::instance();
    shared_ptr< Pool<Connection,ConnectionCreator> > pool = mgr->getPool(creator_.id());
    if (pool)
    {
        shared_ptr<Connection> conn = pool->borrowObject();
        if (conn && conn->isOK())
        {
            PoolGuard<shared_ptr<Connection>, shared_ptr< Pool<Connection,ConnectionCreator> > > guard(conn, pool);

            std::ostringstream s;
            std::string g_type;

            s << "SELECT lower(type) as type FROM "
              << GEOMETRY_COLUMNS <<" WHERE f_table_name='"
              << mapnik::sql_utils::unquote_double(geometry_table_)
              << "'";

            if (! schema_.empty())
            {
                s << " AND f_table_schema='"
                  << mapnik::sql_utils::unquote_double(schema_)
                  << "'";
            }

            if (! geometry_field_.empty())
            {
                s << " AND f_geometry_column='"
                  << mapnik::sql_utils::unquote_double(geometry_field_)
                  << "'";
            }

            shared_ptr<ResultSet> rs = conn->executeQuery(s.str());
            if (rs->next())
            {
                g_type = rs->getValue("type");
                if (boost::algorithm::contains(g_type, "line"))
                {
                    result.reset(mapnik::datasource::LineString);
                    return result;
                }
                else if (boost::algorithm::contains(g_type, "point"))
                {
                    result.reset(mapnik::datasource::Point);
                    return result;
                }
                else if (boost::algorithm::contains(g_type, "polygon"))
                {
                    result.reset(mapnik::datasource::Polygon);
                    return result;
                }
                else // geometry
                {
                    result.reset(mapnik::datasource::Collection);
                    return result;
                }
            }

            // fallback to querying first several features
            if (g_type.empty() && ! geometryColumn_.empty())
            {
                s.str("");

                std::string prev_type("");

                s << "SELECT ST_GeometryType(\""
                  << geometryColumn_ << "\") AS geom"
                  << " FROM " << populate_tokens(table_);

                if (row_limit_ > 0 && row_limit_ < 5)
                {
                    s << " LIMIT " << row_limit_;
                }
                else
                {
                    s << " LIMIT 5";
                }

                shared_ptr<ResultSet> rs = conn->executeQuery(s.str());
                while (rs->next() && ! rs->isNull(0))
                {
                    const char* data = rs->getValue(0);
                    if (boost::algorithm::icontains(data, "line"))
                    {
                        g_type = "linestring";
                        result.reset(mapnik::datasource::LineString);
                    }
                    else if (boost::algorithm::icontains(data, "point"))
                    {
                        g_type = "point";
                        result.reset(mapnik::datasource::Point);
                    }
                    else if (boost::algorithm::icontains(data, "polygon"))
                    {
                        g_type = "polygon";
                        result.reset(mapnik::datasource::Polygon);
                    }
                    else // geometry
                    {
                        result.reset(mapnik::datasource::Collection);
                        return result;
                    }
                    if (! prev_type.empty() && g_type != prev_type)
                    {
                        result.reset(mapnik::datasource::Collection);
                        return result;
                    }
                    prev_type = g_type;
                }
            }
        }
    }

    return result;
}
예제 #3
0
box2d<double> postgis_datasource::envelope() const
{
    if (extent_initialized_)
    {
        return extent_;
    }

    if (! is_bound_)
    {
        bind();
    }

    ConnectionManager* mgr = ConnectionManager::instance();
    shared_ptr< Pool<Connection,ConnectionCreator> > pool = mgr->getPool(creator_.id());
    if (pool)
    {
        shared_ptr<Connection> conn = pool->borrowObject();
        if (conn && conn->isOK())
        {
            PoolGuard<shared_ptr<Connection>, shared_ptr< Pool<Connection,ConnectionCreator> > > guard(conn, pool);

            std::ostringstream s;

            boost::optional<mapnik::boolean> estimate_extent =
                params_.get<mapnik::boolean>("estimate_extent", false);

            if (geometryColumn_.empty())
            {
                std::ostringstream s_error;
                s_error << "PostGIS: unable to query the layer extent of table '";

                if (! schema_.empty())
                {
                    s_error << schema_ << ".";
                }
                s_error << geometry_table_ << "' because we cannot determine the geometry field name."
                        << "\nPlease provide either an 'extent' parameter to skip this query, "
                        << "a 'geometry_field' and/or 'geometry_table' parameter, or add a "
                        << "record to the 'geometry_columns' for your table.";

                throw mapnik::datasource_exception("Postgis Plugin: " + s_error.str());
            }

            if (estimate_extent && *estimate_extent)
            {
                s << "SELECT ST_XMin(ext),ST_YMin(ext),ST_XMax(ext),ST_YMax(ext)"
                  << " FROM (SELECT ST_Estimated_Extent('";

                if (! schema_.empty())
                {
                    s << mapnik::sql_utils::unquote_double(schema_) << "','";
                }

                s << mapnik::sql_utils::unquote_double(geometry_table_) << "','"
                  << mapnik::sql_utils::unquote_double(geometryColumn_) << "') as ext) as tmp";
            }
            else
            {
                s << "SELECT ST_XMin(ext),ST_YMin(ext),ST_XMax(ext),ST_YMax(ext)"
                  << " FROM (SELECT ST_Extent(" <<geometryColumn_<< ") as ext from ";

                if (extent_from_subquery_)
                {
                    // if a subselect limits records then calculating the extent upon the
                    // subquery will be faster and the bounds will be more accurate
                    s << populate_tokens(table_) << ") as tmp";
                }
                else
                {
                    if (! schema_.empty())
                    {
                        s << schema_ << ".";
                    }

                    // but if the subquery does not limit records then querying the
                    // actual table will be faster as indexes can be used
                    s << geometry_table_ << ") as tmp";
                }
            }

            /*
              if (show_queries_)
              {
              std::clog << boost::format("PostGIS: sending query: %s\n") % s.str();
              }
            */

            shared_ptr<ResultSet> rs = conn->executeQuery(s.str());
            if (rs->next() && ! rs->isNull(0))
            {
                double lox, loy, hix, hiy;
                if (mapnik::util::string2double(rs->getValue(0), lox) &&
                    mapnik::util::string2double(rs->getValue(1), loy) &&
                    mapnik::util::string2double(rs->getValue(2), hix) &&
                    mapnik::util::string2double(rs->getValue(3), hiy))
                {
                    extent_.init(lox, loy, hix, hiy);
                    extent_initialized_ = true;
                }
                else
                {
                    std::clog << boost::format("Postgis Plugin: warning: could not determine extent from query: %s\n") % s.str() << std::endl;
                }
            }
            rs->close();
        }
    }

    return extent_;
}
예제 #4
0
featureset_ptr postgis_datasource::features_at_point(coord2d const& pt) const
{
    if (! is_bound_)
    {
        bind();
    }

    ConnectionManager* mgr = ConnectionManager::instance();
    shared_ptr< Pool<Connection,ConnectionCreator> > pool = mgr->getPool(creator_.id());
    if (pool)
    {
        shared_ptr<Connection> conn = pool->borrowObject();
        if (conn && conn->isOK())
        {
            PoolGuard<shared_ptr<Connection>, shared_ptr< Pool<Connection,ConnectionCreator> > > guard(conn, pool);

            if (geometryColumn_.empty())
            {
                std::ostringstream s_error;
                s_error << "PostGIS: geometry name lookup failed for table '";

                if (! schema_.empty())
                {
                    s_error << schema_ << ".";
                }
                s_error << geometry_table_
                        << "'. Please manually provide the 'geometry_field' parameter or add an entry "
                        << "in the geometry_columns for '";

                if (! schema_.empty())
                {
                    s_error << schema_ << ".";
                }
                s_error << geometry_table_ << "'.";

                throw mapnik::datasource_exception(s_error.str());
            }

            std::ostringstream s;
            s << "SELECT ST_AsBinary(\"" << geometryColumn_ << "\") AS geom";

            mapnik::context_ptr ctx = boost::make_shared<mapnik::context_type>();
            std::vector<attribute_descriptor>::const_iterator itr = desc_.get_descriptors().begin();
            std::vector<attribute_descriptor>::const_iterator end = desc_.get_descriptors().end();

            if (! key_field_.empty())
            {
                mapnik::sql_utils::quote_attr(s, key_field_);
                ctx->push(key_field_);
                for (; itr != end; ++itr)
                {
                    if (itr->get_name() != key_field_)
                    {
                        mapnik::sql_utils::quote_attr(s, itr->get_name());
                        ctx->push(itr->get_name());
                    }
                }
            }
            else
            {
                for (; itr != end; ++itr)
                {
                    mapnik::sql_utils::quote_attr(s, itr->get_name());
                    ctx->push(itr->get_name());
                }
            }

            box2d<double> box(pt.x, pt.y, pt.x, pt.y);
            std::string table_with_bbox = populate_tokens(table_, FMAX, box);

            s << " FROM " << table_with_bbox;

            if (row_limit_ > 0)
            {
                s << " LIMIT " << row_limit_;
            }

            boost::shared_ptr<IResultSet> rs = get_resultset(conn, s.str());
            return boost::make_shared<postgis_featureset>(rs, ctx, desc_.get_encoding(), !key_field_.empty());
        }
    }

    return featureset_ptr();
}
예제 #5
0
featureset_ptr postgis_datasource::features(const query& q) const
{
    if (! is_bound_)
    {
        bind();
    }

    box2d<double> const& box = q.get_bbox();
    double scale_denom = q.scale_denominator();

    ConnectionManager* mgr = ConnectionManager::instance();
    shared_ptr< Pool<Connection,ConnectionCreator> > pool = mgr->getPool(creator_.id());
    if (pool)
    {
        shared_ptr<Connection> conn = pool->borrowObject();
        if (conn && conn->isOK())
        {
            PoolGuard<shared_ptr<Connection>, shared_ptr< Pool<Connection,ConnectionCreator> > > guard(conn ,pool);

            if (geometryColumn_.empty())
            {
                std::ostringstream s_error;
                s_error << "PostGIS: geometry name lookup failed for table '";

                if (! schema_.empty())
                {
                    s_error << schema_ << ".";
                }
                s_error << geometry_table_
                        << "'. Please manually provide the 'geometry_field' parameter or add an entry "
                        << "in the geometry_columns for '";

                if (! schema_.empty())
                {
                    s_error << schema_ << ".";
                }
                s_error << geometry_table_ << "'.";

                throw mapnik::datasource_exception(s_error.str());
            }

            std::ostringstream s;
            s << "SELECT ST_AsBinary(\"" << geometryColumn_ << "\") AS geom";

            mapnik::context_ptr ctx = boost::make_shared<mapnik::context_type>();
            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();

            if (! key_field_.empty())
            {
                mapnik::sql_utils::quote_attr(s, key_field_);
                ctx->push(key_field_);

                for (; pos != end; ++pos)
                {
                    if (*pos != key_field_)
                    {
                        mapnik::sql_utils::quote_attr(s, *pos);
                        ctx->push(*pos);
                    }
                }
            }
            else
            {
                for (; pos != end; ++pos)
                {
                    mapnik::sql_utils::quote_attr(s, *pos);
                    ctx->push(*pos);
                }
            }

            std::string table_with_bbox = populate_tokens(table_, scale_denom, box);

            s << " FROM " << table_with_bbox;

            if (row_limit_ > 0)
            {
                s << " LIMIT " << row_limit_;
            }

            boost::shared_ptr<IResultSet> rs = get_resultset(conn, s.str());
            return boost::make_shared<postgis_featureset>(rs, ctx, desc_.get_encoding(), !key_field_.empty());
        }
        else
        {
            throw mapnik::datasource_exception("Postgis Plugin: bad connection");
        }
    }

    return featureset_ptr();
}
예제 #6
0
void postgis_datasource::bind() const
{
    if (is_bound_)
    {
        return;
    }

    boost::optional<int> initial_size = params_.get<int>("initial_size", 1);
    boost::optional<int> max_size = params_.get<int>("max_size", 10);
    boost::optional<mapnik::boolean> autodetect_key_field = params_.get<mapnik::boolean>("autodetect_key_field", false);

    ConnectionManager* mgr = ConnectionManager::instance();
    mgr->registerPool(creator_, *initial_size, *max_size);

    shared_ptr< Pool<Connection,ConnectionCreator> > pool = mgr->getPool(creator_.id());
    if (pool)
    {
        shared_ptr<Connection> conn = pool->borrowObject();
        if (conn && conn->isOK())
        {
            PoolGuard<shared_ptr<Connection>,
                shared_ptr< Pool<Connection,ConnectionCreator> > > guard(conn, pool);

            desc_.set_encoding(conn->client_encoding());

            if (geometry_table_.empty())
            {
                geometry_table_ = mapnik::sql_utils::table_from_sql(table_);
            }

            std::string::size_type idx = geometry_table_.find_last_of('.');
            if (idx != std::string::npos)
            {
                schema_ = geometry_table_.substr(0, idx);
                geometry_table_ = geometry_table_.substr(idx + 1);
            }
            else
            {
                geometry_table_ = geometry_table_.substr(0);
            }

            // If we do not know both the geometry_field and the srid
            // then first attempt to fetch the geometry name from a geometry_columns entry.
            // This will return no records if we are querying a bogus table returned
            // from the simplistic table parsing in table_from_sql() or if
            // the table parameter references a table, view, or subselect not
            // registered in the geometry columns.
            geometryColumn_ = geometry_field_;
            if (geometryColumn_.empty() || srid_ == 0)
            {
                std::ostringstream s;
                s << "SELECT f_geometry_column, srid FROM "
                  << GEOMETRY_COLUMNS <<" WHERE f_table_name='"
                  << mapnik::sql_utils::unquote_double(geometry_table_)
                  << "'";

                if (! schema_.empty())
                {
                    s << " AND f_table_schema='"
                      << mapnik::sql_utils::unquote_double(schema_)
                      << "'";
                }

                if (! geometry_field_.empty())
                {
                    s << " AND f_geometry_column='"
                      << mapnik::sql_utils::unquote_double(geometry_field_)
                      << "'";
                }

                /*
                  if (show_queries_)
                  {
                  std::clog << boost::format("PostGIS: sending query: %s\n") % s.str();
                  }
                */

                shared_ptr<ResultSet> rs = conn->executeQuery(s.str());
                if (rs->next())
                {
                    geometryColumn_ = rs->getValue("f_geometry_column");

                    if (srid_ == 0)
                    {
                        const char* srid_c = rs->getValue("srid");
                        if (srid_c != NULL)
                        {
                            int result = 0;
                            if (mapnik::util::string2int(srid_c, result))
                            {
                                srid_ = result;
                            }
                        }
                    }
                }
                rs->close();

                // If we still do not know the srid then we can try to fetch
                // it from the 'table_' parameter, which should work even if it is
                // a subselect as long as we know the geometry_field to query
                if (! geometryColumn_.empty() && srid_ <= 0)
                {
                    s.str("");

                    s << "SELECT ST_SRID(\"" << geometryColumn_ << "\") AS srid FROM "
                      << populate_tokens(table_) << " WHERE \"" << geometryColumn_ << "\" IS NOT NULL LIMIT 1;";

                    /*
                      if (show_queries_)
                      {
                      std::clog << boost::format("PostGIS: sending query: %s\n") % s.str();
                      }
                    */

                    shared_ptr<ResultSet> rs = conn->executeQuery(s.str());
                    if (rs->next())
                    {
                        const char* srid_c = rs->getValue("srid");
                        if (srid_c != NULL)
                        {
                            int result = 0;
                            if (mapnik::util::string2int(srid_c, result))
                            {
                                srid_ = result;
                            }
                        }
                    }
                    rs->close();
                }
            }

            // detect primary key
            if (*autodetect_key_field && key_field_.empty())
            {
                std::ostringstream s;
                s << "SELECT a.attname, a.attnum, t.typname, t.typname in ('int2','int4','int8') "
                  "AS is_int FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n, pg_index i "
                  "WHERE a.attnum > 0 AND a.attrelid = c.oid "
                  "AND a.atttypid = t.oid AND c.relnamespace = n.oid "
                  "AND c.oid = i.indrelid AND i.indisprimary = 't' "
                  "AND t.typname !~ '^geom' AND c.relname ="
                  << " '" << mapnik::sql_utils::unquote_double(geometry_table_) << "' "
                  //"AND a.attnum = ANY (i.indkey) " // postgres >= 8.1
                  << "AND (i.indkey[0]=a.attnum OR i.indkey[1]=a.attnum OR i.indkey[2]=a.attnum "
                  "OR i.indkey[3]=a.attnum OR i.indkey[4]=a.attnum OR i.indkey[5]=a.attnum "
                  "OR i.indkey[6]=a.attnum OR i.indkey[7]=a.attnum OR i.indkey[8]=a.attnum "
                  "OR i.indkey[9]=a.attnum) ";
                  if (! schema_.empty())
                  {
                      s << "AND n.nspname='"
                        << mapnik::sql_utils::unquote_double(schema_)
                        << "' ";
                  }
                s << "ORDER BY a.attnum";

                shared_ptr<ResultSet> rs_key = conn->executeQuery(s.str());
                if (rs_key->next())
                {
                    unsigned int result_rows = rs_key->size();
                    if (result_rows == 1)
                    {
                        bool is_int = (std::string(rs_key->getValue(3)) == "t");
                        if (is_int)
                        {
                            const char* key_field_string = rs_key->getValue(0);
                            if (key_field_string)
                            {
                                key_field_ = std::string(key_field_string);
#ifdef MAPNIK_DEBUG
                                std::clog << "PostGIS Plugin: auto-detected key field of '"
                                          << key_field_ << "' on table '"
                                          << geometry_table_ << "'\n";
#endif
                            }
                        }
                        else
                        {
                            // throw for cases like a numeric primary key, which is invalid
                            // as it should be floating point (int numerics are useless)
                            std::ostringstream err;
                            err << "PostGIS Plugin: Error: '"
                                << rs_key->getValue(0)
                                << "' on table '"
                                << geometry_table_
                                << "' is not a valid integer primary key field\n";
                            throw mapnik::datasource_exception(err.str());
                        }
                    }
                    else if (result_rows > 1)
                    {
                        std::ostringstream err;
                        err << "PostGIS Plugin: Error: '"
                            << "multi column primary key detected but is not supported";
                        throw mapnik::datasource_exception(err.str());
                    }
                }
                rs_key->close();
            }

            // if a globally unique key field/primary key is required
            // but still not known at this point, then throw
            if (*autodetect_key_field && key_field_.empty())
            {
                throw mapnik::datasource_exception(std::string("PostGIS Plugin: Error: primary key required")
                      + " but could not be detected for table '" +
                      geometry_table_ + "', please supply 'key_field' option to specify field to use for primary key");
            }

            if (srid_ == 0)
            {
                srid_ = -1;

#ifdef MAPNIK_DEBUG
                std::clog << "Postgis Plugin: SRID warning, using srid=-1 for '" << table_ << "'" << std::endl;
#endif
            }

            // At this point the geometry_field may still not be known
            // but we'll catch that where more useful...
#ifdef MAPNIK_DEBUG
            std::clog << "Postgis Plugin: using SRID=" << srid_ << std::endl;
            std::clog << "Postgis Plugin: using geometry_column=" << geometryColumn_ << std::endl;
#endif

            // collect attribute desc
            std::ostringstream s;
            s << "SELECT * FROM " << populate_tokens(table_) << " LIMIT 0";


            /*
              if (show_queries_)
              {
              std::clog << boost::format("PostGIS: sending query: %s\n") % s.str();
              }
            */


            shared_ptr<ResultSet> rs = conn->executeQuery(s.str());
            int count = rs->getNumFields();
            bool found_key_field = false;
            for (int i = 0; i < count; ++i)
            {
                std::string fld_name = rs->getFieldName(i);
                int type_oid = rs->getTypeOID(i);

                // validate type of key_field
                if (! found_key_field && ! key_field_.empty() && fld_name == key_field_)
                {
                    if (type_oid == 20 || type_oid == 21 || type_oid == 23)
                    {
                        found_key_field = true;
                        desc_.add_descriptor(attribute_descriptor(fld_name, mapnik::Integer));
                    }
                    else
                    {
                        std::ostringstream error_s;
                        error_s << "invalid type '";

                        std::ostringstream type_s;
                        type_s << "SELECT oid, typname FROM pg_type WHERE oid = " << type_oid;

                        shared_ptr<ResultSet> rs_oid = conn->executeQuery(type_s.str());
                        if (rs_oid->next())
                        {
                            error_s << rs_oid->getValue("typname")
                                    << "' (oid:" << rs_oid->getValue("oid") << ")";
                        }
                        else
                        {
                            error_s << "oid:" << type_oid << "'";
                        }

                        rs_oid->close();
                        error_s << " for key_field '" << fld_name << "' - "
                                << "must be an integer primary key";

                        rs->close();
                        throw mapnik::datasource_exception(error_s.str());
                    }
                }
                else
                {
                    switch (type_oid)
                    {
                    case 16:    // bool
                        desc_.add_descriptor(attribute_descriptor(fld_name, mapnik::Boolean));
                        break;
                    case 20:    // int8
                    case 21:    // int2
                    case 23:    // int4
                        desc_.add_descriptor(attribute_descriptor(fld_name, mapnik::Integer));
                        break;
                    case 700:   // float4
                    case 701:   // float8
                    case 1700:  // numeric
                        desc_.add_descriptor(attribute_descriptor(fld_name, mapnik::Double));
                    case 1042:  // bpchar
                    case 1043:  // varchar
                    case 25:    // text
                        desc_.add_descriptor(attribute_descriptor(fld_name, mapnik::String));
                        break;
                    default: // should not get here
#ifdef MAPNIK_DEBUG
                        s.str("");
                        s << "SELECT oid, typname FROM pg_type WHERE oid = " << type_oid;

                        /*
                          if (show_queries_)
                          {
                          std::clog << boost::format("PostGIS: sending query: %s\n") % s.str();
                          }
                        */

                        shared_ptr<ResultSet> rs_oid = conn->executeQuery(s.str());
                        if (rs_oid->next())
                        {
                            std::clog << "Postgis Plugin: unknown type = " << rs_oid->getValue("typname")
                                      << " (oid:" << rs_oid->getValue("oid") << ")" << std::endl;
                        }
                        else
                        {
                            std::clog << "Postgis Plugin: unknown oid type =" << type_oid << std::endl;
                        }
                        rs_oid->close();
#endif
                        break;
                    }
                }
            }

            rs->close();

            is_bound_ = true;
        }
    }
}