custom provider query -> wrong/too much results

[baststar]

Hi,
i have this layer configured:

[[providers.layers]]
    name = "admin_lines_level_8"
    geometry_fieldname = "geometry"
    id_fieldname = "osm_id"
    sql = "SELECT ST_AsBinary(geometry) AS geometry, osm_id, admin_level, name, type FROM osm_admin_areas WHERE admin_level = '8' AND geometry && !BBOX!"

with this map:

[[maps.layers]]
    name = "admin_lines_level_8"
    provider_layer = "osm.admin_lines_level_8"
    min_zoom = 14
    max_zoom = 18

and my provider-configuration:

    [[providers]]
    name = "osm"                         
    type = "postgis" 
    host = "postgres.default.svc" 
    port = 5432
    database = "osm" 
    user = "tegola"
    password = "xxx"
    srid = 3857

but instead of getting data only where admin_level = 8 i get also all other admin_levels (2, 4, 6, 9)

Am i doing this right or am i misunderstanding something?

Im using no cache at the moment

[ARolek]

Are the in levels integers or text in the database? Your query has '8' which indicates text. Double check the data type on the admin levels column.

Also, for the provider, take a look at mvt_postgis as you will see some great performance gains.

[iwpnd]

While you seem to be correct that admin level is type int, the expected outcome of using '8' would be 0 rows, and not all rows. 🤔

[baststar]

8 or '8' didn't do any difference. tried both. looks like its ignored completely. but syntax-errors in the query are not ignored (changes are recognized)

@ARolek i will look into mvt_postgis, thanks :)

[ARolek]

@baststar hmm, seems strange. One way to debug this is to turn on TEGOLA_SQL_DEBUG=EXECUTE_SQL per the docs: https://tegola.io/documentation/debugging/. This will show you what tegola is sending to PostGIS. You can then copy the query and execute it yourself directly against the database. I find this is a quicker way to debug.

[baststar]

where is the output of the logs?
I'm using kubernetes, i think debugging is enabled? but the service doesnt output debug-infos and /var/log is empty in the tegola-pod

tegola-deployment.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: tegola
spec:
  selector:
    matchLabels:
      app: tegola
      tier: frontend
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: tegola
        tier: frontend
    spec:
      containers:
        - name: tegola
          image: gospatial/tegola:v0.15.2
          # image: registry.gitlab.com/bamdelicious/tegolamirror:latest
          imagePullPolicy: Always
          ports:
            - containerPort: 8080
          resources:
            limits:
              cpu: 2000m
              memory: 8Gi
            requests:
              cpu: 2000m
              memory: 8Gi
          env:
            - name: TEGOLA_SQL_DEBUG
              value: LAYER_SQL:EXECUTE_SQL
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-root-password
                  key: root-password
          volumeMounts:
          - name: tegola-volume
            mountPath: /opt/tegola_config/config.toml
            subPath: config.toml
          command: ["/opt/tegola"]
          args: ["serve", "--config", "/opt/tegola_config/config.toml", "--log-level", "DEBUG"]
      volumes:
      - name: tegola-volume
        configMap:
          name: tegola-config
          items:
          - key: config.toml
            path: config.toml

EDIT 01.07.2022:
Updated yaml so the logs work
kubectl logs -f service/tegola

[iwpnd]

Start tegola with --log-level DEBUG from v0.15.0 upwards.

[baststar]

@iwpnd Thank you! That works.
Now i see whats happening:

With only this single Layer

const adminLinesLayer = new VectorTileLayer({
            declutter: false,
            source: new VectorTileSource({
                minZoom: 14,
                maxZoom: 18,
                format: new MVT(),
                url: 'http://xxx.xxx.xxx.xxx/maps/osm/admin_lines_level_8/{z}/{x}/{y}.pbf?debug=true',

            }),
            style: myStyleFunction
        });

[[providers.layers]]
        name = "admin_lines_level_8"
        geometry_fieldname = "geometry"
        id_fieldname = "osm_id"
        sql = "SELECT ST_AsBinary(geometry) AS geometry, osm_id, admin_level, name, type FROM osm_admin_areas WHERE admin_level = 8 AND geometry && !BBOX!"

[[maps.layers]]
        name = "admin_lines_level_8"
        provider_layer = "osm.admin_lines_level_8"
        min_zoom = 14
        max_zoom = 18

i got these logs:

2022-07-01 18:03:48 [DEBUG] postgis.go:817: TEGOLA_SQL_DEBUG:EXECUTE_SQL for layer (admin_lines_level_8): SELECT ST_AsBinary(geometry) AS geometry, osm_id, admin_level, name, type FROM osm_admin_areas WHERE admin_level = 8 AND geometry && ST_MakeEnvelope(1.049289305638693e+06,6.975910729933128e+06,1.0518117275717559e+06,6.978433151866188e+06,3857)
2022-07-01 18:03:48 [DEBUG] postgis.go:817: TEGOLA_SQL_DEBUG:EXECUTE_SQL for layer (admin_boundaries_13-20): SELECT ST_AsBinary(geometry) AS geometry, osm_id, admin_level, name, type FROM osm_admin_areas WHERE admin_level IN (1,2,3,4,5,6,7,8,9,10) AND geometry && ST_MakeEnvelope(1.049289305638693e+06,6.975910729933128e+06,1.0518117275717559e+06,6.978433151866188e+06,3857)

so there is a second query happenig...

it is this little (bad named) thing here:

[[maps.layers]]
        name = "admin_lines"
        provider_layer = "osm.admin_boundaries_13-20"
        min_zoom = 13
        max_zoom = 20

http://xxx.xxx.xxx.xxx/maps/osm/admin_lines_level_8/{z}/{x}/{y}.pbf?debug=true

admin_lines_level_8 becomes "admin_lines" and "admin_lines_level_8" on the server

i renamed it to "adminlines8" and now i'm getting only admin-levels of 8!!

Thanks all!!

:)

[iwpnd]

Awesome!

[ARolek]

@baststar did you figure out if that was OpenLayers or tegola mutating the name?

[gdey]

@baststar did you @ARolek question? Also, can we close this issue?