Reuse SQL between providers layers

[leeran88]

Hi!
I have a provider with 3 layers, with the same SQL query (with parameters).

Is it possible to share the SQL query between the layers? I want to avoid duplicating them in the config.

Worth to mention - I separated them because the have different geometry types (point, polygon, linestring)

[ARolek]

@leeran88 Have you tried pointing different map layers to the same provider layer?

[leeran88]

@ARolek I defined 3 layers because they have different geometry types, but the SQL look identical because of the use of parameters. Here's the main idea of my config:

[[providers]]
name = "locations"
type = "mvt_postgis"
host = "..."
port = 5432
database = "..."
user = "..."
password = "..."
srid = 4326

[[providers.layers]]
name = "points"
geometry_fieldname = "geometry"
geometry_type = "point"
id_fieldname = "mvt_id"
fields = [ "id", "mvt_id", "name" ]
sql = "..."

[[providers.layers]]
name = "polygons"
geometry_fieldname = "geometry"
geometry_type = "polygon"
id_fieldname = "mvt_id"
fields = [ "id", "mvt_id", "name" ]
sql = "..."

[[providers.layers]]
name = "linestrings"
geometry_fieldname = "geometry"
geometry_type = "linestring"
id_fieldname = "mvt_id"
fields = [ "id", "mvt_id", "name" ]
sql = "..."

[ARolek]

@leeran88

I defined 3 layers because they have different geometry types

Ah yes, sorry you clearly said that in the opening question. Sorry I missed that. The geometry_type is a pretty key detail when encoding a vector tile, so I don't really have a suggestion on a clean way to reuse code blocks when that property is changing. You could maybe use Postgres function to achieve this, but I'm not sure it would be worth it. Are you dealing with a lot of layers where this is becoming a maintenance burden?

[leeran88]

I have only 3 layers, but the query is pretty complex, so I want to maintain it only once if possible 🙂