Add SQL function to create geom/datetime indexes when creating a new Table in the public schema

[vincentsarago]

eoAPI is built on top a Postgres database that has a pgSTAC schema but also a public one we can use to store other geo data. We don't have a service to ingest data but I think it will be cool to create a simple SQL script to automatically create INDEX on any geometry and datetime column so eoapi.vector is fast.

CREATE OR REPLACE FUNCTION on_create_table_func()
RETURNS event_trigger AS $$
BEGIN
    -- find geom/datetime column
    -- add indexes
END
$$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER
on_create_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE on_create_table_func();

The function and trigger should be used in

eoAPI/infrastructure/aws/handlers/db_handler.py

Line 268 in 019e4b5

cc @bitner

[zacharyDez]

@wildintellect; is this something Kiribati could work on? It seems pretty critical to vector functionality.

[wildintellect]

@zacharyDez I feel like I saw another discussion on this recently in relation to previous work @ingalls did on a vector loading service, and how Felt does this with just about anything. Yes potentially Kiribati could pick this up but @kylebarron and @bitner are also highly qualified.

[bitner]

I would not do this as an event trigger, as there are many cases where you would either not want the index (indexes are NOT always beneficial) or where you could end up in a situation where you accidentally created multiple identical indexes, for example when an ingest mechanism (ie shp2pgsql or ogr) already created an index for you.

When creating an ingest process, we should look for these columns and offer the option of adding the indexes as part of the ingest.

[zacharyDez]

@bitner @vincentsarago; while we wait for an ingestion process where we could add the option for creating indexes, should we demonstrate vector data and detail the process of creating these indexes?