dbt spatial test macros

test_is_in_null_island.sql

{% test is_in_null_island(model, column_name) %}

with validation as (

    select
        st_x({{ column_name }}) as lng,
        st_y({{ column_name }}) as lat
    from 
        {{ model }}

),

validation_errors as (

    select
        lat,
        lng
    from 
        validation
    where 
        lat = 0
    and
        lng = 0
)

select *
from validation_errors

{% endtest %}

test_is_lat_valid.sql

{% test is_lat_valid(model, column_name) %}

with validation as (

    select
        {{ column_name }} as lat
    from 
        {{ model }}

),

validation_errors as (

    select
        lat
    from 
        validation
    where 
        lat < -90
        or lat > 90
)

select *
from validation_errors

{% endtest %}

test_is_lng_valid.sql

{% test is_lng_valid(model, column_name) %}

with validation as (

    select
        {{ column_name }} as lng
    from 
        {{ model }}

),

validation_errors as (

    select
        lng
    from 
        validation
    where 
        lng < -180
        or lng > 180
)

select *
from validation_errors

{% endtest %}