Pass a PostgreSQL query to Martin

[gagecarto]

Is it possible to pass a simple PostgreSQL query to Martin connected to PostGIS? Something like below is how it looks as a raw query

select * 
from pointstable
where ST_Within(
    pointstable.geom,
    ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[-111.8444750539709,45.64143209001972],[-111.83434323763505,45.64193806087536],[-111.83448797786839,45.649830614583635],[-111.84708037817165,45.650235330952654],[-111.8444750539709,45.64143209001972]]]}')
)

[nyurik]

no, we on purpose do not support complex queries from martin - instead, you can use a more powerful approach with functions: https://maplibre.org/martin/sources-pg-functions.html

[zmatez]

Is there a walkaround? I use middleware to construct queries for specific user on-the-go, wrapping it to multiple functions sounds impossible

[nyurik]

@zmatez could you give a bit more details - what kind of query construction do you do, how does the user control it, etc?

Note that the function approach allows you to pass in all the query params into sql function - so you can dynamically construct the actual query with. For example, your request could be

GET /mytiles/z/x/y?foo=1&bar=2

and the function would process those z,x,y,foo=1,bar=2 params and give a result.

[zmatez]

Yeah I know about it and probably found workaround - using just query params for basic needs and querying data for the map separately on the client and adding it to the map layer (prove me wrong but I think that's possible - I'm doing research) - like foreach every terrain and append e.g. SALES_COUNT from separately queried table - so on the map I can see both terrain name (from martin) and SALES_COUNT from postgres database, or whatever variable user sets to show

[gagecarto]

This looks promising.. I don't quite understand where the function is configured or how I access it.

Are there any more docs or a more detailed example of how to implement something like this? I simply want to run some ST_Within and ST_Extent calls by passing geojson polygons.

[nyurik]

The function is a regular PostgreSQL function - it lives in the database. When Martin starts up, it queries all tables and all functions available, and exposes them all to the user (unless you have a config file where you explicitly list tables/functions you want to publish)

P.S. its best to click "write a reply" under the message - keeps conversation relevant, but no biggie.