Lat Long for Map Charts

One really cool thing I was able to do was use publicly available country and US zip lat long info (attached) to leverage the mapping charts in periscope:

Domestic/US Snippet

select
count(id),
[lat_long_zip_view].lat,
[lat_long_zip_view].lng
from orders
join [lat_long_zip_view]
on orders.postal_code = [lat_long_zip_view].zip
where
  order_date > current_date - 7
group by
  [lat_long_zip_view].lat,
  [lat_long_zip_view].lng

INTL Snippet:

select
  count(id),
  destination_country,
  [country_lat_long].latitude,
  [country_lat_long].longitude
from orders
left join [country_lat_long]
on orders.destination_country = [country_lat_long].name
where
  order_date > current_date - 7
group by
  [country_lat_long].latitude, orders.destination_country,
  [country_lat_long].longitude
2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Will definitely use this, thanks for sharing.

    Reply Like
  • Thanks for including the lookup tables with the code!

    Reply Like
Like2 Follow
  • 2 Likes
  • 2 yrs agoLast active
  • 2Replies
  • 475Views
  • 4 Following