Work on functions for eoapi

[bitner]

PLAYGROUND TO SEE WHAT WORKS

pg_temp.pgstac_collections_view should work just like a normal collection

pg_temp.pgstac_hash takes a queryhash as the argument and then will use the "automagic" bounds geometry calculated from the z/x/y

http://localhost:8083/collections/pg_temp.pgstac_hash/tiles/0/0/0?queryhash=c53f4ebe9b2a50bc2b52fd88a5d503e1 is an example with registering search('{}') as the query in pgstac.

If only someone hadn't gotten rid of the vector tile viewer, we'd be able to see the MVT results ;-P

[bitner]

@vincentsarago

[vincentsarago]

pg_temp.pgstac_hash works great 🥳 but I'm getting projection/geometries issue with the collection function

[bitner]

@vincentsarago do you have any stac definitions and urls that I can use to replicate the issues you are seeing with the collections view? At least with the one stac record for noaa-emergency-response it seems to work correctly

[bitner]

@vincentsarago http://localhost:8083/collections/pg_temp.pgstac_hash_count/viewer?queryhash=c53f4ebe9b2a50bc2b52fd88a5d503e1&depth=4

[vincentsarago]

INFO:     127.0.0.1:49827 - "GET /collections/pg_temp.pgstac_hash_count/tiles/WebMercatorQuad/1/1/1?queryhash=c53f4ebe9b2a50bc2b52fd88a5d503e1&depth=4 HTTP/1.1" 500 Internal Server Error
syntax error at or near "AND"

eoapi.db  | 2023-03-15 20:15:56.189 UTC [91] STATEMENT:  
eoapi.db  |                 WITH
eoapi.db  |                 t AS (SELECT cnt, ST_AsMVTGeom(ST_Transform(geom, $1::int), ST_Segmentize(ST_MakeEnvelope($2, $3, $4, $5), $6), $7, $8, $9) AS geom
eoapi.db  |     FROM pg_temp.pgstac_hash_count($10::text::text, $11::text::geometry, $12::text::integer)
eoapi.db  |     WHERE $13 AND ST_Intersects(ST_Transform(ST_Segmentize(ST_MakeEnvelope($14, $15, $16, $17, $18), $19), $20::int), geom)
eoapi.db  |     LIMIT $21)
eoapi.db  |                 SELECT ST_AsMVT(t.*, $22) FROM t

@bitner doesn't work for me 🤷

edit: because the search didn't exist for me!

[vincentsarago]

Thanks @bitner 🙏 everything seems to work.

I'll merge to the other PR and continue the work