select_properties (and sortby, datetime_, skip_geometry) parameter in Postgresql provider is ignored

[skyNacho]

Description When listing items using a Postgresql provider, if the parameter property is used to select which properties should be returned, pygeoapy ignores it and returns all properties. The same happens with sortby, datetime_ and skip_geometry parameters.

Steps to Reproduce Steps to reproduce the behavior:

(unfortunately the on-line demo does not provide an example with a Postgresql provider)

1. Get access to a pygeoapi instance publishing features from a Postgresql provider.
2. Confirm that the openapi description allows for the use of properties parameter using the get method in the 'collections/{collection_id}/items' link (description is "The properties that should be included for each feature. The parameter value is a comma-separated list of property names.")
3. Test it and confirm that all properties are returned, no matter the value of the properties parameter

The steps are similar to the other aforementioned parameters.

Expected behavior When specifying a list of comma-separated property names in the properties parameter, features should include only those specified properties.

Screenshots/Tracebacks

Reviewing the code it is easy to find that something is missing. Here is the code for the query function in postgresql.py. Just search for the keyword select_properties (that provides a list for the queried properties) and you will see that nothing is done with that value. Similarly with parameters sortby, datetime_ and skip_geometry.

    def query(self, startindex=0, limit=10, resulttype='results',
              bbox=[], datetime_=None, properties=[], sortby=[],
              select_properties=[], skip_geometry=False, q=None, **kwargs):
        """
        Query Postgis for all the content.
        e,g: http://localhost:5000/collections/hotosm_bdi_waterways/items?
        limit=1&resulttype=results
        :param startindex: starting record to return (default 0)
        :param limit: number of records to return (default 10)
        :param resulttype: return results or hit limit (default results)
        :param bbox: bounding box [minx,miny,maxx,maxy]
        :param datetime_: temporal (datestamp or extent)
        :param properties: list of tuples (name, value)
        :param sortby: list of dicts (property, order)
        :param select_properties: list of property names
        :param skip_geometry: bool of whether to skip geometry (default False)
        :param q: full-text search term(s)
        :returns: GeoJSON FeaturesCollection
        """
        LOGGER.debug('Querying PostGIS')

        if resulttype == 'hits':

            with DatabaseConnection(self.conn_dic,
                                    self.table, context="hits") as db:
                cursor = db.conn.cursor(cursor_factory=RealDictCursor)

                where_clause = self.__get_where_clauses(
                    properties=properties, bbox=bbox)
                sql_query = SQL("SELECT COUNT(*) as hits from {} {}").\
                    format(Identifier(self.table), where_clause)
                try:
                    cursor.execute(sql_query)
                except Exception as err:
                    LOGGER.error('Error executing sql_query: {}: {}'.format(
                        sql_query.as_string(cursor), err))
                    raise ProviderQueryError()

                hits = cursor.fetchone()["hits"]

            return self.__response_feature_hits(hits)

        end_index = startindex + limit

        with DatabaseConnection(self.conn_dic, self.table) as db:
            cursor = db.conn.cursor(cursor_factory=RealDictCursor)

            where_clause = self.__get_where_clauses(
                properties=properties, bbox=bbox)

            sql_query = SQL("DECLARE \"geo_cursor\" CURSOR FOR \
             SELECT DISTINCT {},ST_AsGeoJSON({}) FROM {}{}").\
                format(db.columns,
                       Identifier(self.geom),
                       Identifier(self.table),
                       where_clause)

            LOGGER.debug('SQL Query: {}'.format(sql_query.as_string(cursor)))
            LOGGER.debug('Start Index: {}'.format(startindex))
            LOGGER.debug('End Index: {}'.format(end_index))
            try:
                cursor.execute(sql_query)
                for index in [startindex, limit]:
                    cursor.execute("fetch forward {} from geo_cursor"
                                   .format(index))
            except Exception as err:
                LOGGER.error('Error executing sql_query: {}'.format(
                    sql_query.as_string(cursor)))
                LOGGER.error(err)
                raise ProviderQueryError()

            row_data = cursor.fetchall()

            feature_collection = {
                'type': 'FeatureCollection',
                'features': []
            }

            for rd in row_data:
                feature_collection['features'].append(
                    self.__response_feature(rd))

            return feature_collection

Environment

• OS:
• Python version:
• pygeoapi version: latest stable

Additional context I am unsure how the code should behave in the case of datetime_ parameter, as there does not seem to be predefined columns in Postgresql provider defining the time values of the feature (interval or instant). The implementation for the other parameters should be relatively straightforward.

If no one has the time or the interest I can give it a try if I get some directions.