pycsw icon indicating copy to clipboard operation
pycsw copied to clipboard

Published 20 hours ago • verified publisher icon geopython

New API - BBOX Query doesn't return expected records

Open epifanio opened this issue 4 years ago • 7 comments

Description

New API - BBOX Query doesn't return expected records.

using CSW url syntax below:

https://sios.csw.met.no/csw?service=CSW&version=2.0.2&request=GetRecords&ElementSetName=full&typenames=csw:Record&resulttype=results&bbox=0,0,180,90

I can extract records from the selected bbox region, the same query using the new API:

https://sios.csw.met.no/collections/metadata:main/items?bbox=0%200%20180%2090&limit=10&f=json

Doesn't return any record,

Environment

  • operating system: Ubutnu 21.04
  • Python version: 3.9
  • pycsw version: git master
  • source/distribution
    • [x] git clone
    • [ ] DebianGIS/UbuntuGIS
    • [ ] PyPI
    • [ ] zip/tar.gz
    • [ ] other (please specify):
  • web server
    • [x] Apache/mod_wsgi
    • [ ] CGI
    • [ ] other (please specify):

Steps to Reproduce

This returns expected records:

https://sios.csw.met.no/csw?service=CSW&version=2.0.2&request=GetRecords&ElementSetName=full&typenames=csw:Record&resulttype=results&bbox=0,0,180,90

The same using API doesn't return records:

https://sios.csw.met.no/collections/metadata:main/items?bbox=0%200%20180%2090&limit=10&f=json

Additional Information

epifanio avatar Sep 23 '21 03:09 epifanio

@epifanio the bbox parameter needs to be comma-separated:

https://sios.csw.met.no/collections/metadata:main/items?bbox=0,0,180,90&limit=10&f=json

However this results in a 500. Can you provide a traceback?

tomkralidis avatar Oct 11 '21 23:10 tomkralidis

@tomkralidis -- On the docker logs, the request is received:

[email protected]    | 10.0.1.9 - - [12/Oct/2021:00:55:28 +0000] "GET /collections/metadata:main/items?bbox=0,0,180,90&limit=10&f=json HTTP/1.1" 500 290 "https://github.com/geopython/pycsw/issues/712" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36"
[email protected]    | 10.0.1.9 - - [12/Oct/2021:00:55:28 +0000] "GET /favicon.ico HTTP/1.1" 404 232 "https://sios.csw.met.no/collections/metadata:main/items?bbox=0,0,180,90&limit=10&f=json" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36"

On the pycsw side, I can see the following error:

  File "/home/pycsw/pycsw/pycsw/ogc/api/records.py", line 141, in get_content_type
    if 'text/html' in headers['Accept']:
KeyError: 'Accept'

Full log attached below.

[SQL: SELECT count(*) AS count_1 
FROM (SELECT sios_records.identifier AS sios_records_identifier, sios_records.typename AS sios_records_typename, sios_records.schema AS sios_records_schema, sios_records.mdsource AS sios_records_mdsource, sios_records.insert_date AS sios_records_insert_date, sios_records.xml AS sios_records_xml, sios_records.anytext AS sios_records_anytext, sios_records.metadata AS sios_records_metadata, sios_records.metadata_type AS sios_records_metadata_type, sios_records.language AS sios_records_language, sios_records.type AS sios_records_type, sios_records.title AS sios_records_title, sios_records.title_alternate AS sios_records_title_alternate, sios_records.abstract AS sios_records_abstract, sios_records.keywords AS sios_records_keywords, sios_records.keywordstype AS sios_records_keywordstype, sios_records.parentidentifier AS sios_records_parentidentifier, sios_records.relation AS sios_records_relation, sios_records.time_begin AS sios_records_time_begin, sios_records.time_end AS sios_records_time_end, sios_records.topicategory AS sios_records_topicategory, sios_records.resourcelanguage AS sios_records_resourcelanguage, sios_records.creator AS sios_records_creator, sios_records.publisher AS sios_records_publisher, sios_records.contributor AS sios_records_contributor, sios_records.organization AS sios_records_organization, sios_records.distributor AS sios_records_distributor, sios_records.securityconstraints AS sios_records_securityconstraints, sios_records.accessconstraints AS sios_records_accessconstraints, sios_records.useconstraints AS sios_records_useconstraints, sios_records.otherconstraints AS sios_records_otherconstraints, sios_records.date AS sios_records_date, sios_records.date_revision AS sios_records_date_revision, sios_records.date_creation AS sios_records_date_creation, sios_records.date_publication AS sios_records_date_publication, sios_records.date_modified AS sios_records_date_modified, sios_records.format AS sios_records_format, sios_records.source AS sios_records_source, sios_records.crs AS sios_records_crs, sios_records.geodescode AS sios_records_geodescode, sios_records.denominator AS sios_records_denominator, sios_records.distancevalue AS sios_records_distancevalue, sios_records.distanceuom AS sios_records_distanceuom, sios_records.wkt_geometry AS sios_records_wkt_geometry, sios_records.servicetype AS sios_records_servicetype, sios_records.servicetypeversion AS sios_records_servicetypeversion, sios_records.operation AS sios_records_operation, sios_records.couplingtype AS sios_records_couplingtype, sios_records.operateson AS sios_records_operateson, sios_records.operatesonidentifier AS sios_records_operatesonidentifier, sios_records.operatesoname AS sios_records_operatesoname, sios_records.degree AS sios_records_degree, sios_records.classification AS sios_records_classification, sios_records.conditionapplyingtoaccessanduse AS sios_records_conditionapplyingtoaccessanduse, sios_records.lineage AS sios_records_lineage, sios_records.responsiblepartyrole AS sios_records_responsiblepartyrole, sios_records.specificationtitle AS sios_records_specificationtitle, sios_records.specificationdate AS sios_records_specificationdate, sios_records.specificationdatetype AS sios_records_specificationdatetype, sios_records.platform AS sios_records_platform, sios_records.instrument AS sios_records_instrument, sios_records.sensortype AS sios_records_sensortype, sios_records.cloudcover AS sios_records_cloudcover, sios_records.bands AS sios_records_bands, sios_records.links AS sios_records_links, sios_records.anytext_tsvector AS sios_records_anytext_tsvector 
FROM sios_records 
WHERE query_spatial(records_wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true') AS anon_1]
(Background on this error at: http://sqlalche.me/e/14/e3q8)
Mon, 11 Oct 2021 23:45:01] [ERROR] file=/usr/local/lib/python3.8/site-packages/flask/app.py line=1454 module=app function=log_exception Exception on /collections/metadata:main/items [HEAD]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/home/pycsw/pycsw/pycsw/wsgi_flask.py", line 149, in items
    return get_response(api_.items(dict(request.headers), request.args))
  File "/home/pycsw/pycsw/pycsw/ogc/api/records.py", line 401, in items
    headers_['Content-Type'] = self.get_content_type(headers_, args)
  File "/home/pycsw/pycsw/pycsw/ogc/api/records.py", line 141, in get_content_type
    if 'text/html' in headers['Accept']:
KeyError: 'Accept'
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=427 module=records function=items Transforming property filters into CQL
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=446 module=records function=items Evaluating CQL and other specified filtering parameters
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=453 module=records function=items Just other specified filtering parameters detected
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=456 module=records function=items CQL query: BBOX(geometry, 0,0,180,90)
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=460 module=records function=items Parsing CQL into AST
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=462 module=records function=items Abstract syntax tree: <pycql.ast.BBoxPredicateNode object at 0x7fbf35b3d7f0>
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=464 module=records function=items Transforming AST into filters
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=466 module=records function=items Filter: query_spatial(records_wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true'
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=473 module=records function=items limit specified
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=482 module=records function=items Query: SELECT sios_records.identifier AS sios_records_identifier, sios_records.typename AS sios_records_typename, sios_records.schema AS sios_records_schema, sios_records.mdsource AS sios_records_mdsource, sios_records.insert_date AS sios_records_insert_date, sios_records.xml AS sios_records_xml, sios_records.anytext AS sios_records_anytext, sios_records.metadata AS sios_records_metadata, sios_records.metadata_type AS sios_records_metadata_type, sios_records.language AS sios_records_language, sios_records.type AS sios_records_type, sios_records.title AS sios_records_title, sios_records.title_alternate AS sios_records_title_alternate, sios_records.abstract AS sios_records_abstract, sios_records.keywords AS sios_records_keywords, sios_records.keywordstype AS sios_records_keywordstype, sios_records.parentidentifier AS sios_records_parentidentifier, sios_records.relation AS sios_records_relation, sios_records.time_begin AS sios_records_time_begin, sios_records.time_end AS sios_records_time_end, sios_records.topicategory AS sios_records_topicategory, sios_records.resourcelanguage AS sios_records_resourcelanguage, sios_records.creator AS sios_records_creator, sios_records.publisher AS sios_records_publisher, sios_records.contributor AS sios_records_contributor, sios_records.organization AS sios_records_organization, sios_records.distributor AS sios_records_distributor, sios_records.securityconstraints AS sios_records_securityconstraints, sios_records.accessconstraints AS sios_records_accessconstraints, sios_records.useconstraints AS sios_records_useconstraints, sios_records.otherconstraints AS sios_records_otherconstraints, sios_records.date AS sios_records_date, sios_records.date_revision AS sios_records_date_revision, sios_records.date_creation AS sios_records_date_creation, sios_records.date_publication AS sios_records_date_publication, sios_records.date_modified AS sios_records_date_modified, sios_records.format AS sios_records_format, sios_records.source AS sios_records_source, sios_records.crs AS sios_records_crs, sios_records.geodescode AS sios_records_geodescode, sios_records.denominator AS sios_records_denominator, sios_records.distancevalue AS sios_records_distancevalue, sios_records.distanceuom AS sios_records_distanceuom, sios_records.wkt_geometry AS sios_records_wkt_geometry, sios_records.servicetype AS sios_records_servicetype, sios_records.servicetypeversion AS sios_records_servicetypeversion, sios_records.operation AS sios_records_operation, sios_records.couplingtype AS sios_records_couplingtype, sios_records.operateson AS sios_records_operateson, sios_records.operatesonidentifier AS sios_records_operatesonidentifier, sios_records.operatesoname AS sios_records_operatesoname, sios_records.degree AS sios_records_degree, sios_records.classification AS sios_records_classification, sios_records.conditionapplyingtoaccessanduse AS sios_records_conditionapplyingtoaccessanduse, sios_records.lineage AS sios_records_lineage, sios_records.responsiblepartyrole AS sios_records_responsiblepartyrole, sios_records.specificationtitle AS sios_records_specificationtitle, sios_records.specificationdate AS sios_records_specificationdate, sios_records.specificationdatetype AS sios_records_specificationdatetype, sios_records.platform AS sios_records_platform, sios_records.instrument AS sios_records_instrument, sios_records.sensortype AS sios_records_sensortype, sios_records.cloudcover AS sios_records_cloudcover, sios_records.bands AS sios_records_bands, sios_records.links AS sios_records_links, sios_records.anytext_tsvector AS sios_records_anytext_tsvector 
FROM sios_records 
WHERE query_spatial(records_wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true'
Tue, 12 Oct 2021 00:55:28] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=483 module=records function=items Querying repository
Tue, 12 Oct 2021 00:55:28] [ERROR] file=/usr/local/lib/python3.8/site-packages/flask/app.py line=1454 module=app function=log_exception Exception on /collections/metadata:main/items [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column "records_wkt_geometry" does not exist
LINE 4: WHERE query_spatial(records_wkt_geometry, 'POLYGON((0.00 0.0...
                            ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/home/pycsw/pycsw/pycsw/wsgi_flask.py", line 149, in items
    return get_response(api_.items(dict(request.headers), request.args))
  File "/home/pycsw/pycsw/pycsw/ogc/api/records.py", line 484, in items
    count = query.count()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3078, in count
    return self._from_self(col).enable_eagerloads(False).scalar()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2819, in scalar
    ret = self.one()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2796, in one
    return self._iter().one()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2834, in _iter
    result = self.session.execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 324, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "records_wkt_geometry" does not exist
LINE 4: WHERE query_spatial(records_wkt_geometry, 'POLYGON((0.00 0.0...
                            ^

[SQL: SELECT count(*) AS count_1 
FROM (SELECT sios_records.identifier AS sios_records_identifier, sios_records.typename AS sios_records_typename, sios_records.schema AS sios_records_schema, sios_records.mdsource AS sios_records_mdsource, sios_records.insert_date AS sios_records_insert_date, sios_records.xml AS sios_records_xml, sios_records.anytext AS sios_records_anytext, sios_records.metadata AS sios_records_metadata, sios_records.metadata_type AS sios_records_metadata_type, sios_records.language AS sios_records_language, sios_records.type AS sios_records_type, sios_records.title AS sios_records_title, sios_records.title_alternate AS sios_records_title_alternate, sios_records.abstract AS sios_records_abstract, sios_records.keywords AS sios_records_keywords, sios_records.keywordstype AS sios_records_keywordstype, sios_records.parentidentifier AS sios_records_parentidentifier, sios_records.relation AS sios_records_relation, sios_records.time_begin AS sios_records_time_begin, sios_records.time_end AS sios_records_time_end, sios_records.topicategory AS sios_records_topicategory, sios_records.resourcelanguage AS sios_records_resourcelanguage, sios_records.creator AS sios_records_creator, sios_records.publisher AS sios_records_publisher, sios_records.contributor AS sios_records_contributor, sios_records.organization AS sios_records_organization, sios_records.distributor AS sios_records_distributor, sios_records.securityconstraints AS sios_records_securityconstraints, sios_records.accessconstraints AS sios_records_accessconstraints, sios_records.useconstraints AS sios_records_useconstraints, sios_records.otherconstraints AS sios_records_otherconstraints, sios_records.date AS sios_records_date, sios_records.date_revision AS sios_records_date_revision, sios_records.date_creation AS sios_records_date_creation, sios_records.date_publication AS sios_records_date_publication, sios_records.date_modified AS sios_records_date_modified, sios_records.format AS sios_records_format, sios_records.source AS sios_records_source, sios_records.crs AS sios_records_crs, sios_records.geodescode AS sios_records_geodescode, sios_records.denominator AS sios_records_denominator, sios_records.distancevalue AS sios_records_distancevalue, sios_records.distanceuom AS sios_records_distanceuom, sios_records.wkt_geometry AS sios_records_wkt_geometry, sios_records.servicetype AS sios_records_servicetype, sios_records.servicetypeversion AS sios_records_servicetypeversion, sios_records.operation AS sios_records_operation, sios_records.couplingtype AS sios_records_couplingtype, sios_records.operateson AS sios_records_operateson, sios_records.operatesonidentifier AS sios_records_operatesonidentifier, sios_records.operatesoname AS sios_records_operatesoname, sios_records.degree AS sios_records_degree, sios_records.classification AS sios_records_classification, sios_records.conditionapplyingtoaccessanduse AS sios_records_conditionapplyingtoaccessanduse, sios_records.lineage AS sios_records_lineage, sios_records.responsiblepartyrole AS sios_records_responsiblepartyrole, sios_records.specificationtitle AS sios_records_specificationtitle, sios_records.specificationdate AS sios_records_specificationdate, sios_records.specificationdatetype AS sios_records_specificationdatetype, sios_records.platform AS sios_records_platform, sios_records.instrument AS sios_records_instrument, sios_records.sensortype AS sios_records_sensortype, sios_records.cloudcover AS sios_records_cloudcover, sios_records.bands AS sios_records_bands, sios_records.links AS sios_records_links, sios_records.anytext_tsvector AS sios_records_anytext_tsvector 
FROM sios_records 
WHERE query_spatial(records_wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true') AS anon_1]
(Background on this error at: http://sqlalche.me/e/14/f405)

epifanio avatar Oct 12 '21 01:10 epifanio

I have updated my server to the latest pycsw master - the error is now no longer related to the missing key - traceback below:

gist: https://gist.github.com/epifanio/d672eb7234d58a909687d07b06b96c87

Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=469 module=records function=items Transforming property filters into CQL
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=497 module=records function=items Evaluating CQL and other specified filtering parameters
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=504 module=records function=items Just other specified filtering parameters detected
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=507 module=records function=items CQL query: BBOX(geometry, 0,0,180,90)
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=510 module=records function=items Detected CQL text
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=518 module=records function=items Parsing CQL into AST
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=521 module=records function=items Abstract syntax tree: BBox(lhs=ATTRIBUTE geometry, minx=0, miny=0, maxx=180, maxy=90, crs=None)
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=527 module=records function=items Transforming AST into filters
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=530 module=records function=items Filter: query_spatial(wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true'
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=542 module=records function=items limit specified
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=554 module=records function=items Query: SELECT tstsios_records.identifier AS tstsios_records_identifier, tstsios_records.typename AS tstsios_records_typename, tstsios_records.schema AS tstsios_records_schema, tstsios_records.mdsource AS tstsios_records_mdsource, tstsios_records.insert_date AS tstsios_records_insert_date, tstsios_records.xml AS tstsios_records_xml, tstsios_records.anytext AS tstsios_records_anytext, tstsios_records.metadata AS tstsios_records_metadata, tstsios_records.metadata_type AS tstsios_records_metadata_type, tstsios_records.language AS tstsios_records_language, tstsios_records.type AS tstsios_records_type, tstsios_records.title AS tstsios_records_title, tstsios_records.title_alternate AS tstsios_records_title_alternate, tstsios_records.abstract AS tstsios_records_abstract, tstsios_records.keywords AS tstsios_records_keywords, tstsios_records.keywordstype AS tstsios_records_keywordstype, tstsios_records.parentidentifier AS tstsios_records_parentidentifier, tstsios_records.relation AS tstsios_records_relation, tstsios_records.time_begin AS tstsios_records_time_begin, tstsios_records.time_end AS tstsios_records_time_end, tstsios_records.topicategory AS tstsios_records_topicategory, tstsios_records.resourcelanguage AS tstsios_records_resourcelanguage, tstsios_records.creator AS tstsios_records_creator, tstsios_records.publisher AS tstsios_records_publisher, tstsios_records.contributor AS tstsios_records_contributor, tstsios_records.organization AS tstsios_records_organization, tstsios_records.securityconstraints AS tstsios_records_securityconstraints, tstsios_records.accessconstraints AS tstsios_records_accessconstraints, tstsios_records.otherconstraints AS tstsios_records_otherconstraints, tstsios_records.date AS tstsios_records_date, tstsios_records.date_revision AS tstsios_records_date_revision, tstsios_records.date_creation AS tstsios_records_date_creation, tstsios_records.date_publication AS tstsios_records_date_publication, tstsios_records.date_modified AS tstsios_records_date_modified, tstsios_records.format AS tstsios_records_format, tstsios_records.source AS tstsios_records_source, tstsios_records.crs AS tstsios_records_crs, tstsios_records.geodescode AS tstsios_records_geodescode, tstsios_records.denominator AS tstsios_records_denominator, tstsios_records.distancevalue AS tstsios_records_distancevalue, tstsios_records.distanceuom AS tstsios_records_distanceuom, tstsios_records.wkt_geometry AS tstsios_records_wkt_geometry, tstsios_records.servicetype AS tstsios_records_servicetype, tstsios_records.servicetypeversion AS tstsios_records_servicetypeversion, tstsios_records.operation AS tstsios_records_operation, tstsios_records.couplingtype AS tstsios_records_couplingtype, tstsios_records.operateson AS tstsios_records_operateson, tstsios_records.operatesonidentifier AS tstsios_records_operatesonidentifier, tstsios_records.operatesoname AS tstsios_records_operatesoname, tstsios_records.degree AS tstsios_records_degree, tstsios_records.classification AS tstsios_records_classification, tstsios_records.conditionapplyingtoaccessanduse AS tstsios_records_conditionapplyingtoaccessanduse, tstsios_records.lineage AS tstsios_records_lineage, tstsios_records.responsiblepartyrole AS tstsios_records_responsiblepartyrole, tstsios_records.specificationtitle AS tstsios_records_specificationtitle, tstsios_records.specificationdate AS tstsios_records_specificationdate, tstsios_records.specificationdatetype AS tstsios_records_specificationdatetype, tstsios_records.platform AS tstsios_records_platform, tstsios_records.instrument AS tstsios_records_instrument, tstsios_records.sensortype AS tstsios_records_sensortype, tstsios_records.cloudcover AS tstsios_records_cloudcover, tstsios_records.bands AS tstsios_records_bands, tstsios_records.links AS tstsios_records_links, tstsios_records.anytext_tsvector AS tstsios_records_anytext_tsvector 
FROM tstsios_records 
WHERE query_spatial(wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true'
Fri, 29 Oct 2021 14:36:46] [DEBUG] file=/home/pycsw/pycsw/pycsw/ogc/api/records.py line=555 module=records function=items Querying repository
Fri, 29 Oct 2021 14:36:46] [ERROR] file=/usr/local/lib/python3.8/site-packages/flask/app.py line=1457 module=app function=log_exception Exception on /collections/metadata:main/items [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedFunction: function query_spatial(text, unknown, unknown, unknown) does not exist
LINE 4: WHERE query_spatial(wkt_geometry, 'POLYGON((0.00 0.00, 0.00 ...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2073, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1518, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1516, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1502, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/home/pycsw/pycsw/pycsw/wsgi_flask.py", line 157, in items
    return get_response(api_.items(dict(request.headers), request.json, dict(request.args), stac_item))
  File "/home/pycsw/pycsw/pycsw/ogc/api/records.py", line 556, in items
    count = query.count()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3090, in count
    return self._from_self(col).enable_eagerloads(False).scalar()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2831, in scalar
    ret = self.one()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2808, in one
    return self._iter().one()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result = self.session.execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1611, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1478, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2023, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function query_spatial(text, unknown, unknown, unknown) does not exist
LINE 4: WHERE query_spatial(wkt_geometry, 'POLYGON((0.00 0.00, 0.00 ...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT count(*) AS count_1 
FROM (SELECT tstsios_records.identifier AS tstsios_records_identifier, tstsios_records.typename AS tstsios_records_typename, tstsios_records.schema AS tstsios_records_schema, tstsios_records.mdsource AS tstsios_records_mdsource, tstsios_records.insert_date AS tstsios_records_insert_date, tstsios_records.xml AS tstsios_records_xml, tstsios_records.anytext AS tstsios_records_anytext, tstsios_records.metadata AS tstsios_records_metadata, tstsios_records.metadata_type AS tstsios_records_metadata_type, tstsios_records.language AS tstsios_records_language, tstsios_records.type AS tstsios_records_type, tstsios_records.title AS tstsios_records_title, tstsios_records.title_alternate AS tstsios_records_title_alternate, tstsios_records.abstract AS tstsios_records_abstract, tstsios_records.keywords AS tstsios_records_keywords, tstsios_records.keywordstype AS tstsios_records_keywordstype, tstsios_records.parentidentifier AS tstsios_records_parentidentifier, tstsios_records.relation AS tstsios_records_relation, tstsios_records.time_begin AS tstsios_records_time_begin, tstsios_records.time_end AS tstsios_records_time_end, tstsios_records.topicategory AS tstsios_records_topicategory, tstsios_records.resourcelanguage AS tstsios_records_resourcelanguage, tstsios_records.creator AS tstsios_records_creator, tstsios_records.publisher AS tstsios_records_publisher, tstsios_records.contributor AS tstsios_records_contributor, tstsios_records.organization AS tstsios_records_organization, tstsios_records.securityconstraints AS tstsios_records_securityconstraints, tstsios_records.accessconstraints AS tstsios_records_accessconstraints, tstsios_records.otherconstraints AS tstsios_records_otherconstraints, tstsios_records.date AS tstsios_records_date, tstsios_records.date_revision AS tstsios_records_date_revision, tstsios_records.date_creation AS tstsios_records_date_creation, tstsios_records.date_publication AS tstsios_records_date_publication, tstsios_records.date_modified AS tstsios_records_date_modified, tstsios_records.format AS tstsios_records_format, tstsios_records.source AS tstsios_records_source, tstsios_records.crs AS tstsios_records_crs, tstsios_records.geodescode AS tstsios_records_geodescode, tstsios_records.denominator AS tstsios_records_denominator, tstsios_records.distancevalue AS tstsios_records_distancevalue, tstsios_records.distanceuom AS tstsios_records_distanceuom, tstsios_records.wkt_geometry AS tstsios_records_wkt_geometry, tstsios_records.servicetype AS tstsios_records_servicetype, tstsios_records.servicetypeversion AS tstsios_records_servicetypeversion, tstsios_records.operation AS tstsios_records_operation, tstsios_records.couplingtype AS tstsios_records_couplingtype, tstsios_records.operateson AS tstsios_records_operateson, tstsios_records.operatesonidentifier AS tstsios_records_operatesonidentifier, tstsios_records.operatesoname AS tstsios_records_operatesoname, tstsios_records.degree AS tstsios_records_degree, tstsios_records.classification AS tstsios_records_classification, tstsios_records.conditionapplyingtoaccessanduse AS tstsios_records_conditionapplyingtoaccessanduse, tstsios_records.lineage AS tstsios_records_lineage, tstsios_records.responsiblepartyrole AS tstsios_records_responsiblepartyrole, tstsios_records.specificationtitle AS tstsios_records_specificationtitle, tstsios_records.specificationdate AS tstsios_records_specificationdate, tstsios_records.specificationdatetype AS tstsios_records_specificationdatetype, tstsios_records.platform AS tstsios_records_platform, tstsios_records.instrument AS tstsios_records_instrument, tstsios_records.sensortype AS tstsios_records_sensortype, tstsios_records.cloudcover AS tstsios_records_cloudcover, tstsios_records.bands AS tstsios_records_bands, tstsios_records.links AS tstsios_records_links, tstsios_records.anytext_tsvector AS tstsios_records_anytext_tsvector 
FROM tstsios_records 
WHERE query_spatial(wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true') AS anon_1]
(Background on this error at: https://sqlalche.me/e/14/f405)

epifanio avatar Oct 29 '21 14:10 epifanio

same log from a psql session into the database container: https://gist.github.com/epifanio/034e1648c46a1158c69f298859c8ed23

pycsw=# SELECT tstsios_records.identifier AS tstsios_records_identifier, tstsios_records.typename AS tstsios_records_typename, tstsios_records.schema AS tstsios_records_schema, tstsios_records.mdsource AS tstsios_records_mdsource, tstsios_records.insert_date AS tstsios_records_insert_date, tstsios_records.xml AS tstsios_records_xml, tstsios_records.anytext AS tstsios_records_anytext, tstsios_records.metadata AS tstsios_records_metadata, tstsios_records.metadata_type AS tstsios_records_metadata_type, tstsios_records.language AS tstsios_records_language, tstsios_records.type AS tstsios_records_type, tstsios_records.title AS tstsios_records_title, tstsios_records.title_alternate AS tstsios_records_title_alternate, tstsios_records.abstract AS tstsios_records_abstract, tstsios_records.keywords AS tstsios_records_keywords, tstsios_records.keywordstype AS tstsios_records_keywordstype, tstsios_records.parentidentifier AS tstsios_records_parentidentifier, tstsios_records.relation AS tstsios_records_relation, tstsios_records.time_begin AS tstsios_records_time_begin, tstsios_records.time_end AS tstsios_records_time_end, tstsios_records.topicategory AS tstsios_records_topicategory, tstsios_records.resourcelanguage AS tstsios_records_resourcelanguage, tstsios_records.creator AS tstsios_records_creator, tstsios_records.publisher AS tstsios_records_publisher, tstsios_records.contributor AS tstsios_records_contributor, tstsios_records.organization AS tstsios_records_organization, tstsios_records.securityconstraints AS tstsios_records_securityconstraints, tstsios_records.accessconstraints AS tstsios_records_accessconstraints, tstsios_records.otherconstraints AS tstsios_records_otherconstraints, tstsios_records.date AS tstsios_records_date, tstsios_records.date_revision AS tstsios_records_date_revision, tstsios_records.date_creation AS tstsios_records_date_creation, tstsios_records.date_publication AS tstsios_records_date_publication, tstsios_records.date_modified AS tstsios_records_date_modified, tstsios_records.format AS tstsios_records_format, tstsios_records.source AS tstsios_records_source, tstsios_records.crs AS tstsios_records_crs, tstsios_records.geodescode AS tstsios_records_geodescode, tstsios_records.denominator AS tstsios_records_denominator, tstsios_records.distancevalue AS tstsios_records_distancevalue, tstsios_records.distanceuom AS tstsios_records_distanceuom, tstsios_records.wkt_geometry AS tstsios_records_wkt_geometry, tstsios_records.servicetype AS tstsios_records_servicetype, tstsios_records.servicetypeversion AS tstsios_records_servicetypeversion, tstsios_records.operation AS tstsios_records_operation, tstsios_records.couplingtype AS tstsios_records_couplingtype, tstsios_records.operateson AS tstsios_records_operateson, tstsios_records.operatesonidentifier AS tstsios_records_operatesonidentifier, tstsios_records.operatesoname AS tstsios_records_operatesoname, tstsios_records.degree AS tstsios_records_degree, tstsios_records.classification AS tstsios_records_classification, tstsios_records.conditionapplyingtoaccessanduse AS tstsios_records_conditionapplyingtoaccessanduse, tstsios_records.lineage AS tstsios_records_lineage, tstsios_records.responsiblepartyrole AS tstsios_records_responsiblepartyrole, tstsios_records.specificationtitle AS tstsios_records_specificationtitle, tstsios_records.specificationdate AS tstsios_records_specificationdate, tstsios_records.specificationdatetype AS tstsios_records_specificationdatetype, tstsios_records.platform AS tstsios_records_platform, tstsios_records.instrument AS tstsios_records_instrument, tstsios_records.sensortype AS tstsios_records_sensortype, tstsios_records.cloudcover AS tstsios_records_cloudcover, tstsios_records.bands AS tstsios_records_bands, tstsios_records.links AS tstsios_records_links, tstsios_records.anytext_tsvector AS tstsios_records_anytext_tsvector 
pycsw-# FROM tstsios_records 
pycsw-# WHERE query_spatial(wkt_geometry, 'POLYGON((0.00 0.00, 0.00 90.00, 180.00 90.00, 180.00 0.00, 0.00 0.00))', 'bbox', 'false') = 'true';
ERROR:  function query_spatial(text, unknown, unknown, unknown) does not exist
LINE 3: WHERE query_spatial(wkt_geometry, 'POLYGON((0.00 0.00, 0.00 ...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
pycsw=#

postgis is available on that same db:

pycsw=# SELECT PostGIS_version();
            postgis_version            
---------------------------------------
 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

pycsw=# SELECT PostGIS_full_version();
                                                                         postgis_full_version                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.0.3 0" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.0.1" LIBXML="2.9.10" LIBJSON="0.14" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)" TOPOLOGY
(1 row)

pycsw=#

epifanio avatar Oct 29 '21 17:10 epifanio

@epifanio are you able to provide the full traceback for the life of the request? It looks like PostgreSQL/PostGIS is not getting picked up as the DB backend.

tomkralidis avatar Oct 31 '21 18:10 tomkralidis

@epifanio testing further, I'm unable to reproduce. You need to ensure your DB is setup with the PostGIS extensions accordingly.

tomkralidis avatar Nov 01 '21 23:11 tomkralidis

@tomkralidis probably I need to re-create the DB, I was using an existing one - which is already in use with pycsw - it is a postgis DB running under docker.

I am using the same machine used in the compose environment from the pycsw docker settings -

  postgis:
    image: postgis/postgis:12-3.0-alpine

I see be the API call refer to a "missing function" wrapped into a custom SQL method in admin.py

```.. /pycsw/core/admin.py:239: function_query_spatial = '''````

Somehow this code has not been executed ?!

epifanio avatar Nov 02 '21 11:11 epifanio