v_location_map (view)

46 rows

✎ View and edit SQL

This data as json, CSV (advanced)

CREATE VIEW v_location_map AS
SELECT l.canonical_name AS location, l.location_type, l.state, l.country,
       l.lat AS latitude, l.lon AS longitude, l.geocode_source,
       (SELECT ROUND(SUM(qty_dest),0)   FROM v_trip_canon WHERE dst_canon=l.canonical_name) AS tonnage_in,
       (SELECT ROUND(SUM(qty_source),0) FROM v_trip_canon WHERE src_canon=l.canonical_name) AS tonnage_out
FROM locations l WHERE l.lat IS NOT NULL;