Skip to content

Instantly share code, notes, and snippets.

@ramiroaznar

ramiroaznar/test_is_in_null_island.sql

Created July 1, 2022 09:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ramiroaznar/8491d8733b3e13a057ac6541d1781ec4 to your computer and use it in GitHub Desktop.
Save ramiroaznar/8491d8733b3e13a057ac6541d1781ec4 to your computer and use it in GitHub Desktop.
Download ZIP
dbt spatial test macros
Raw
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 %}
Raw
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 %}
Raw
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 %}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment