Postgres Ports

s3://trase-storage/postgres_views/postgres_ports.parquet

Dbt path: trase_production.main_postgres_tables.postgres_ports

Explore on Metabase: Full table; summary statistics

Containing yaml file link: trase/data_pipeline/models/postgres_views/_schema_postgres_tables.yml

Model file link: trase/data_pipeline/models/postgres_views/postgres_ports.sql

Dbt test runs & lineage: Test results · Lineage

Full dbt_docs page: Open in dbt docs (includes lineage graph -at the bottom right-, tests, and downstream dependencies)

Tags: postgres, ports

postgres_ports

Description

Queries the Postgres 'views.regions' view for region_type = 'PORT'

Details

ColumnsDepends OnModel code

Column	Type	Description
`name`	`VARCHAR`
`trase_id`	`VARCHAR`
`synonyms`	`VARCHAR[]`
`node_id`	`BIGINT`
`country`	`VARCHAR`
`_geometry`	`VARCHAR`
`biome`	`VARCHAR`
`brazil_port_urf_codes`	`VARCHAR[]`
`last_refresh_time`	`TIMESTAMP WITH TIME ZONE`
`parent_trase_id`	`VARCHAR`
`parent_name`	`VARCHAR`
`parent_node_type_slug`	`VARCHAR`
`parent_node_type_name`	`VARCHAR`
`longitude`	`DOUBLE`
`latitude`	`DOUBLE`

No dependencies recorded.

No called script or script source not found.

{{ config(materialized='external') }}

WITH
source_data AS (
    SELECT 
        * 
        EXCLUDE(
            region_type, 
            level, 
            node_type_slug, 
            year_start, 
            year_end,
            matopiba,
            node_type_name)
    FROM {{ this.identifier }}_postgres_db.views.regions
    WHERE region_type = 'PORT'
)

SELECT
    *,
    JSON_EXTRACT_STRING(_geometry, '$.coordinates[0]')::DOUBLE AS longitude,
    JSON_EXTRACT_STRING(_geometry, '$.coordinates[1]')::DOUBLE AS latitude
FROM source_data