Add custom params to sql query is not working

[navot-tomorrow]

I have an issue with my config.toml configuration. This is my config.toml file:(I removed the webserver and providers section)

[[providers.layers]]
name = "locations"
geometry_fieldname = "geometry"
id_fieldname = "vector_tile_id"
geometry_type = "Point"
fields = []
sql = "SELECT ST_AsBinary(geometry) AS geometry,vector_tile_id FROM events.locations WHERE geometry && !BBOX! !PARAM!"

[[maps]]
name = "monitors-events"

[[maps.layers]]
name = "locations"
provider_layer = "monitors-events.locations"

[[maps.params]]
name = "accountId"
token = "!PARAM!"
type = "string"
sql = "AND acc_id = ?"
default_sql = "123"

this is my tegola start command: serve --config /opt/config/config.toml

this is my image: image: gospatial/tegola

this is my error I see in the container: [ERROR] map.go:342: err fetching tile (z: 0, x: 0, y: 0) features: error running layer (locations) SQL (SELECT ST_AsBinary(geometry) AS geometry,vector_tile_id FROM events.locations WHERE geometry && ST_MakeEnvelope(-185.62499997416083,-85.5133983078662,185.62499997416083,85.5133983078662,4326) !PARAM!): ERROR: syntax error at end of input (SQLSTATE 42601)

I have 2 questions:

1. What do I need to change to fix the error and return locations based on the custom param?(acctId in my example)
2. I want to return in the same response Point and Polygon, is it possible? If I remove the geometry_type in the provider layer the tegola server is crashed.

Thanks!

[iwpnd]

Does the same occur when you use the mvt_postgis provider as provided in the example?

[ARolek]

@navot-tomorrow did you build tegola from source? This feature is not yet released, but we are looking for people to test it like you're doing here. Let's also try to get @bemyak into the convo and they authored this feature.

[navot-tomorrow]

@iwpnd when I use mvt_postgis type Im getting this error: [ERROR] handle_map_layer_zxy.go:175: error marshalling tile: ERROR: syntax error at or near ")" (SQLSTATE 42601) @ARolek I used the latest image

[bemyak]

Hey, @navot-tomorrow ! Try removing exclamation marks around the token string:

token = "PARAM"

[navot-tomorrow]

I want to return Polygon and Point in the same response is it something I can do? @bemyak I tried and I get this error: [ERROR] handle_map_layer_zxy.go:175: error marshalling tile: ERROR: syntax error at or near "PARAM" (SQLSTATE 42601)

[navot-tomorrow]

sorry, my bad. to remove the exclamation marks around the token only? or in the SQL query as well?

[bemyak]

No, the bad is mine :sweat_smile: Your config seems to be correct, let me check it more thoroughly.

[navot-tomorrow]

Thanks! I tried also what you said and I got the same error.

[bemyak]

One minor thing caught my eye: you'd probably want to use default_value = "123" instead of default_sql = "123". Sorry, I'll be unavailable until tomorrow.

[bemyak]

I used the latest image

@navot-tomorrow do you mean latest from docker hub? It seems to me that this feature isn't included there yet, right, @ARolek?

[navot-tomorrow]

yes, the latest from the docker hub. when it should be released? When I run my query against the source(main branch) I see that it is working

[iwpnd]

You want to use the edge tag. this will always mirror master.

[navot-tomorrow]

@iwpnd can you explain where do use it? maybe to add an example? thanks in advance

[iwpnd]

Instead of using the image

gospatial/tegola:latest

you can use

gospatial/tegola:edge

The latter does have every feature that makes it to the master branch of tegola.

[bemyak]

edge was updated the same time v0.16 was released, so to me it seems that it might be a bit outdated :thinking:

[navot-tomorrow]

from my test when I run gospatial/tegola:edge, it's working. is it possible?

[bemyak]

Yes, it could have been built from master right after the release, so it could include the feature.

Did I get you right that the issue is resolved with the edge container?

[ARolek]

I just looked at the CI definition and edge is the right build to grab:

https://github.com/go-spatial/tegola/blob/b358d4df0e812e8b960a80b630550c2a63bf9190/.github/workflows/on_release_publish.yml#L182-L199

You can also grab build artifacts from the any of the CI actions against master: https://github.com/go-spatial/tegola/actions?query=branch%3Amaster+event%3Apush

[navot-tomorrow]

@bemyak yes it is resolved. thank you for your help

[iwpnd]

edge was built 7 days after the last latest. Dockerhub is not very verbose on dates at first glance. You can hover the "last pushed .." tho.

[gdey]

@bemyak yes it is resolved. thank you for your help

Sounds like this issue is resolved. I'll close it then, if it's not please reopen it.