Downloads

Maps and fiscal calendars

Addresses with latitude and longitude, reference geography, and a fiscal calendar in every lab database: map it, chart it by fiscal period.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Store and use latitude and longitude for addresses.
  • Measure the distance between two points with the haversine formula.
  • Chart results by fiscal period, using the fiscal calendar in each lab database.
  • Map results in Excel and Power BI.

Seven of the sample databases share the same two building blocks: addresses with coordinates for mapping, and a fiscal calendar for charting by fiscal period. They behave the same way in cpafirm, nonprofit, pqlab (its tables are prefixed core_), smarthome, bigbear, retailwh and mfgorders, so a map or a fiscal-year chart you build for one works for the others.

Addresses and coordinates

Every table that holds an address has the same columns:

Column Meaning
address_line1, city, state, postal_code The address as entered
county, country Added from the city (county names, US)
latitude, longitude WGS84 decimal degrees, six decimals
geocode_quality How the point was found: rooftop, street, postal_centroid or city_centroid (bigbear adds gps for mobile-app reports, address_match, neighborhood_centroid and failed)
full_address The address in one line, for geocoding tools and map labels
Watch out

Where the address is missing the coordinates are null. Where only the city is known you get the city centroid.

Database Tables with coordinates
cpafirm clients, offices, client_visits (origin and destination of each trip)
nonprofit constituents (about 18,500 donors, volunteers and prospects), funders, vendors, program_sites, mobile_market_stops, event venues
pqlab core_customers, core_stores, core_suppliers (HQ), core_shipments (destination and distance from the warehouse), plus the lab_geo_* practice tables
smarthome homes, points_of_interest, geofences, trips, trip_points, and an indoor floor plan in feet
bigbear parcels, facilities, assets, streets (segment endpoints), service_requests, cad_calls, permits, inspections, code_cases, work_orders, capital_projects, special_events, businesses, vendors, property_owners
retailwh dim_store, dim_customer (latitude and longitude), dim_region (a rough boundary polygon as GeoJSON)
mfgorders dim_customer, dim_plant

Postal codes in these databases are fictitious (a real city prefix with random digits). The point for a postal code is a synthetic centroid placed near the city, so use postal_centroid rows for rough maps only.

Reference geography

Each database has the same three reference tables (in pqlab they are core_geo_states, core_geo_places and core_geo_postal_codes):

Table Holds
geo_states The 50 states and DC: name, FIPS code, Census region and division, centroid, capital
geo_places Each city in use with county, centroid, population, time zone and metro area
geo_postal_codes A centroid for every postal code in the data

Join to them on state_code, on city and state, or on postal_code. Use geo_states for filled (shaded-area) maps and geo_places for bubble maps.

Distance on a map

The same great-circle (haversine) formula works on SQL Server and MariaDB. This finds the five clients farthest from the office that serves them in cpafirm:

SELECT c.client_name, c.city, o.office_name,
       ROUND(3958.8 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(c.latitude - o.latitude) / 2), 2)
             + COS(RADIANS(o.latitude)) * COS(RADIANS(c.latitude)) * POWER(SIN(RADIANS(c.longitude - o.longitude) / 2), 2))), 1) AS miles_from_office
FROM clients c
JOIN offices o ON o.office_id = c.office_id
WHERE c.latitude IS NOT NULL
ORDER BY miles_from_office DESC
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Out-of-state clients (Arizona, for example) are served remotely, which is why they top the list. To build a catchment for the nearest store, use lab_geo_distance_pairs in pqlab, which already holds the miles and a flag for each customer's nearest store.

Mapping in Excel and Power BI

  1. Set the data category. In Power BI select latitude and longitude and set their data category to Latitude and Longitude. For a state map use state with the category State or Province.
  2. Bubble map. Put longitude and latitude on the map visual, and size the bubbles by a measure such as donors, billed amount or miles.
  3. Filled map. Join to geo_states and shade by state; use census_region for a region rollup.
  4. Routes. client_visits, trips and lab_geo_route_points have an origin and a destination, or ordered points, which a line layer or a Shape map can draw.
  5. Big maps. For thousands of points, round the coordinates (for example to one decimal) and group, so the map draws bins rather than every row.
  6. Excel. Use 3D Maps (Insert, 3D Map) or a Filled Map chart; for a Geography data type, build it from full_address or city and state.

Fiscal calendars

Every database has a fiscal-calendar table (fiscal_calendar, or core_fiscal_calendar in pqlab) with one row per day, and its main fact tables carry three ready-made columns so you do not need a join to filter by fiscal period:

Column Meaning
fiscal_year The fiscal year (the naming rule is below)
fiscal_quarter 1 to 4
fiscal_period 1 to 12, with 1 the first month of the fiscal year

The calendar tables add fiscal_year_label (such as FY2026), fiscal_period_name (such as FY2026-P03), the period and year start and end dates, fiscal_week, fiscal_day_of_year, and flags for the last day of a period and of a year. Each also has a few extras for its domain.

Database Fiscal year Named by Extras in the calendar
cpafirm July to June year it ends tax_season (Busy, Extension, Off) and tax_deadline
nonprofit July to June year it ends giving_season, holidays, Giving Tuesday
pqlab February to January year it starts a true 4-5-4 retail calendar (retail_year, retail_period, retail_week_of_period) and shopping seasons
smarthome October to September (water year) year it ends utility_season and the rate plan in force
bigbear July to June year it ends budget_phase, property tax installment dates, tourism_season, snow season and holidays
retailwh April to March year it ends dim_date with fiscal year, quarter, period and period name
mfgorders November to October year it ends dim_date with fiscal year, quarter, period and period name

"Named by the year it ends" means FY2026 is the year ending in 2026 (July 2025 to June 2026 for a July start). pqlab names its year by the year it starts, to match its older core_calendar table; its retail_* columns are the 4-5-4 weeks, which differ from the calendar-month fiscal periods by a few days at the edges.

Fiscal quarters of billing, with no join:

SELECT fiscal_year, fiscal_quarter, COUNT(*) AS invoices, SUM(total_amount) AS billed
FROM invoices
WHERE fiscal_year BETWEEN 2024 AND 2026
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarter;

Using the extras with a join (revenue by tax season in cpafirm):

SELECT f.tax_season, COUNT(*) AS invoices, SUM(i.total_amount) AS billed
FROM invoices i
JOIN fiscal_calendar f ON f.calendar_date = i.issue_date
WHERE i.fiscal_year = 2025
GROUP BY f.tax_season
ORDER BY billed DESC;

Giving by season in nonprofit:

SELECT f.giving_season, COUNT(*) AS gifts, SUM(g.amount) AS raised
FROM gifts g
JOIN fiscal_calendar f ON f.calendar_date = g.gift_date
WHERE g.fiscal_year = 2025
GROUP BY f.giving_season
ORDER BY raised DESC;

The retail calendar in pqlab:

SELECT f.retail_period, COUNT(*) AS orders, SUM(o.total) AS sales
FROM core_orders o
JOIN core_fiscal_calendar f ON f.calendar_date = o.order_date
WHERE f.retail_year = 2025
GROUP BY f.retail_period
ORDER BY f.retail_period;

Fiscal time in Power BI and Excel

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.