search

go-spatial / tegola

Tegola is a Mapbox Vector Tile server written in Go
http://tegola.io/
MIT License
1.25k stars 192 forks source link

Params for SQL query not working #910

Closed jmcaracoche closed 1 year ago

jmcaracoche commented 1 year ago

I've been dealing with this issue for some days. I didn't find anything in the web and I read the code and should work but it's no working for me.

I'm running the compiles version 0.16.0

I have this section in my config.toml file

[[providers.layers]]
  name = "harvest_map"
  geometry_fieldname="geometry"
  id_fieldname="gid"
  #geometry_type="Point"
  sql = """SELECT ST_AsMVTGeom(geometry,!BBOX!) 
        AS geometry, gid,  yield_, collection_id, crop 
        FROM harvest_map WHERE geometry && !BBOX!  !PARAM!"""

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

I get this error when launching the server

{"level":"debug","timestamp":"2023-03-11T16:18:06.272-0300","message":"SQL for Layer(harvest_map):\nSELECT STAsMVTGeom(geometry,!BBOX!) AS geometry, gid, yield, collection_id, crop FROM harvest_map WHERE geometry && !BBOX! !PARAM! \n"} Error: could not register providers: error fetching geometry type for layer (harvest_map): ERROR: column "param" does not exist (SQLSTATE 42703)

If I change the token to something else line !PARAM2!, the error is

Error: could not register providers: error fetching geometry type for layer (harvest_map): ERROR: column "param2" does not exist (SQLSTATE 42703)

Whatever I use as token it's appear as a column X does not exists.

Is this a bug or I'm doing something wrong?

Thank you in advance.

ARolek commented 1 year ago

@jmcaracoche What happens when you uncomment:

#geometry_type="Point"

What you're encountering is a part of tegola from the early days where the software tries to figure out your geometry type based on your SQL. Unfortunately this process is kind of fragile, and the more complex the SQL the less likely it is to succeed. By setting the geometry_type for the layer this startup step will be skipped.

jmcaracoche commented 1 year ago

Thank you! I could move forward and I can get the servers up and runninng but when I do a query the same thing happened. Converts !PARAM! as 'param' in the final query. Am I doing something wrong?

{"level":"error","timestamp":"2023-03-12T18:57:11.011-0300","message":"TEGOLA_SQL_DEBUG:EXECUTE_SQL: returned error ERROR: column \"param\" does not exist (SQLSTATE 42703)","stacktrace":"github.com/go-spatial/tegola/internal/log.Errorf\n\t/Users/runner/work/tegola/tegola/internal/log/log.go:132\ngithub.com/go-spatial/tegola/provider/postgis.Provider.MVTForLayers\n\t/Users/runner/work/tegola/tegola/provider/postgis/postgis.go:999\ngithub.com/go-spatial/tegola/atlas.Map.encodeMVTProviderTile\n\t/Users/runner/work/tegola/tegola/atlas/map.go:193\ngithub.com/go-spatial/tegola/atlas.Map.Encode\n\t/Users/runner/work/tegola/tegola/atlas/map.go:382\ngithub.com/go-spatial/tegola/server.HandleMapLayerZXY.ServeHTTP\n\t/Users/runner/work/tegola/tegola/server/handle_map_layer_zxy.go:162\ngithub.com/go-spatial/tegola/server.TileCacheHandler.func1\n\t/Users/runner/work/tegola/tegola/server/middleware_tile_cache.go:27\nnet/http.HandlerFunc.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2109\ngithub.com/go-spatial/tegola/server.GZipHandler.func1\n\t/Users/runner/work/tegola/tegola/server/middleware_gzip.go:45\nnet/http.HandlerFunc.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2109\ngithub.com/go-spatial/tegola/server.HeadersHandler.func1\n\t/Users/runner/work/tegola/tegola/server/middleware_headers.go:11\nnet/http.HandlerFunc.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2109\ngithub.com/dimfeld/httptreemux.(ContextGroup).Handler.func1\n\t/Users/runner/work/tegola/tegola/vendor/github.com/dimfeld/httptreemux/context.go:66\ngithub.com/dimfeld/httptreemux.(TreeMux).ServeLookupResult\n\t/Users/runner/work/tegola/tegola/vendor/github.com/dimfeld/httptreemux/router.go:247\ngithub.com/dimfeld/httptreemux.(TreeMux).ServeHTTP\n\t/Users/runner/work/tegola/tegola/vendor/github.com/dimfeld/httptreemux/router.go:268\nnet/http.serverHandler.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2947\nnet/http.(conn).serve\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:1991"}

{"level":"error","timestamp":"2023-03-12T18:57:11.024-0300","message":"error marshalling tile: ERROR: column \"param\" does not exist (SQLSTATE 42703)","stacktrace":"github.com/go-spatial/tegola/internal/log.Error\n\t/Users/runner/work/tegola/tegola/internal/log/log.go:175\ngithub.com/go-spatial/tegola/server.HandleMapLayerZXY.ServeHTTP\n\t/Users/runner/work/tegola/tegola/server/handle_map_layer_zxy.go:175\ngithub.com/go-spatial/tegola/server.TileCacheHandler.func1\n\t/Users/runner/work/tegola/tegola/server/middleware_tile_cache.go:27\nnet/http.HandlerFunc.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2109\ngithub.com/go-spatial/tegola/server.GZipHandler.func1\n\t/Users/runner/work/tegola/tegola/server/middleware_gzip.go:45\nnet/http.HandlerFunc.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2109\ngithub.com/go-spatial/tegola/server.HeadersHandler.func1\n\t/Users/runner/work/tegola/tegola/server/middleware_headers.go:11\nnet/http.HandlerFunc.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2109\ngithub.com/dimfeld/httptreemux.(ContextGroup).Handler.func1\n\t/Users/runner/work/tegola/tegola/vendor/github.com/dimfeld/httptreemux/context.go:66\ngithub.com/dimfeld/httptreemux.(TreeMux).ServeLookupResult\n\t/Users/runner/work/tegola/tegola/vendor/github.com/dimfeld/httptreemux/router.go:247\ngithub.com/dimfeld/httptreemux.(TreeMux).ServeHTTP\n\t/Users/runner/work/tegola/tegola/vendor/github.com/dimfeld/httptreemux/router.go:268\nnet/http.serverHandler.ServeHTTP\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:2947\nnet/http.(conn).serve\n\t/Users/runner/hostedtoolcache/go/1.19.3/x64/src/net/http/server.go:1991"}

ARolek commented 1 year ago

@jmcaracoche did you build tegola from source, or grab a binary from the CI? I realized that we don't support param filtering in 0.16.0, but we do in the upcoming release (v0.17.0). I want to first make sure you're on the correct version.

I would also like to summon @bemyak to be part of this convo as they authored this fantastic feature.

jmcaracoche commented 1 year ago

I’m using the compiled version. Seeing the stack trace and following it with the code I realized that may be a different code the binary one! Now confirm it! I will build it! When are you planning to release the 0.17?

thank you for this!

best

juan

gdey commented 1 year ago

Closing, please reopen if it still fails with the version built from the source.