Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add option to write custom SQL filters for experienced users. #67

Open
RaczeQ opened this issue Mar 21, 2024 · 0 comments
Open

Add option to write custom SQL filters for experienced users. #67

RaczeQ opened this issue Mar 21, 2024 · 0 comments

Comments

@RaczeQ
Copy link
Collaborator

RaczeQ commented Mar 21, 2024

Prepare a grammar language for writing custom SQL filters (simplest option for users - utilize replacement, simplest option to implement - allow users to use DuckDB functions and expect them to use it)

Examples:

-- Queries provided by user

-- Option 1: probably simplest for the end user, will require additional step to unnest all key value pairs
(osm_tag_key = 'highway' AND (osm_tag_value LIKE '%ary' OR osm_tag_value = 'motorway'))

-- Expanded in the QuackOSM query
WITH raw_data AS (
    SELECT kind, id, tags
    FROM ST_ReadOSM('tests/test_files/monaco.osm.pbf')
    WHERE tags IS NOT NULL
), 
unnested_data AS (
    SELECT kind, id, UNNEST(map_entries(tags)) kv_pair
    FROM raw_data
), 
expanded_data AS (
    SELECT kind, id, kv_pair['key'] as osm_tag_key, kv_pair['value'] as osm_tag_value
    FROM unnested_data
),
filtered_elements AS (
    SELECT DISTINCT kind, id
    FROM expanded_data
    -- paste filter here 1 to 1
    WHERE (osm_tag_key = 'highway' AND (osm_tag_value LIKE '%ary' OR osm_tag_value = 'motorway'))
)
SELECT *
FROM raw_data
SEMI JOIN filtered_elements USING (kind, id)
@RaczeQ RaczeQ linked a pull request Apr 16, 2024 that will close this issue
@RaczeQ RaczeQ removed a link to a pull request Apr 16, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant