Chart guide Tableau and SQL

How do I plot latitude and longitude from a CSV on a map?

Put AVG([longitude]) on Columns and AVG([latitude]) on Rows, then add the location to Detail so you get one mark per place, not one average dot. To see density, round both coordinates into grid cells. In this bike-share, the busiest dock (Union Station) is not in the busiest part of town.

Updated October 30, 2026

The data

Rose City Bikes pickups, September 2025

Synthetic datapickups.csv

A docked bike-share in central Portland, Oregon: 27 stations, one row per pickup, with the bike's GPS position at the start of the ride. A few dockless bikes report no GPS fix.

`trip_id`	`started_at`	`station_id`	`station`	`latitude`	`longitude`
T00001	2025-09-01 00:08	S16	SE Grand & Belmont	45.5164	-122.6611
T00002	2025-09-01 00:08	S24	NE 7th & Holladay	45.5287	-122.6596
T00003	2025-09-01 01:39	S12	Salmon St Springs	45.5138	-122.6733
T00004	2025-09-01 03:55	S16	SE Grand & Belmont	45.5164	-122.6611

Step 1

Drop the coordinates on the shelves: one dot

Every pickup averaged into one point, and it isn't even in Portland: 23 rows with no GPS fix sit at (0, 0) and drag the average 168 km away, into the middle of the state. The second dot is the same average without them.

Tableau

Columns: AVG([longitude])    Rows: AVG([latitude])

SQL

SELECT
  'Average of every row' AS point,
  AVG(latitude) AS latitude,
  AVG(longitude) AS longitude,
  COUNT(*) AS trips
FROM input
UNION ALL 
SELECT
  'Where the bikes are',
  AVG(latitude),
  AVG(longitude),
  COUNT(*)
FROM input
WHERE NOT (latitude = 0 AND longitude = 0)

Rows

Point	Latitude	Longitude	Trips
Average of every row	44.82	−121	1,492
Where the bikes are	45.52	−123	1,469

Step 2

One mark per station, without the (0, 0) rows

Now each station is a circle sized by pickups. One circle dominates: Union Station, with 216 pickups, nearly three times the next station.

Tableau

// Detail: [station]    Size: COUNT([trip_id])
// Filter: NOT ([latitude] = 0 AND [longitude] = 0)

SQL

SELECT
  station,
  AVG(latitude) AS latitude,
  AVG(longitude) AS longitude,
  COUNT(*) AS trips
FROM input
WHERE NOT (latitude = 0 AND longitude = 0)
GROUP BY station
ORDER BY trips DESC

Rows

Station	Latitude	Longitude	Trips
Union Station	45.53	−123	216
SW 6th & Alder	45.52	−123	75
SW 5th & Oak	45.52	−123	74
Pioneer Square	45.52	−123	73
SW 4th & Stark	45.52	−123	71
SW Broadway & Morrison	45.52	−123	62
SW 3rd & Taylor	45.52	−123	61
NW 13th & Lovejoy	45.53	−123	59
Jamison Square	45.53	−123	58
Salmon St Springs	45.51	−123	55
Hawthorne Bridge West	45.51	−123	53
SW Naito & Clay	45.51	−123	52
OHSU Tram Lower	45.5	−123	50
SW Moody & Gibbs	45.5	−123	50
Riverplace Marina	45.51	−123	49
Eastbank Esplanade	45.52	−123	49
SE Grand & Belmont	45.52	−123	46
Moda Center	45.53	−123	46
Lloyd Center	45.53	−123	43
NW 11th & Johnson	45.53	−123	38
NE 7th & Holladay	45.53	−123	35
NW 6th & Hoyt	45.53	−123	32
NE Killingsworth & 14th	45.56	−123	28
SE MLK & Ash	45.52	−123	27
South Waterfront Park	45.5	−123	26
SE 11th & Morrison	45.52	−123	25
NE Alberta & 15th	45.56	−123	16

Step 3

Round the coordinates into a grid

Each square counts every pickup inside it, whichever station it came from. The hottest square is downtown, where 6 ordinary docks add up to 416 pickups, well ahead of the square around Union Station. Try 3 decimals and the grid shrinks to single docks; try 1 and nearly the whole city falls in one cell.

Tableau

// [Lat cell]: ROUND([latitude], {knob.decimals})
// [Lon cell]: ROUND([longitude], {knob.decimals})
// Columns: [Lon cell]    Rows: [Lat cell]   (both as continuous dimensions)
// Marks: Square    Color and Size: COUNT([trip_id])

SQL

SELECT
  ROUND(latitude, 2) AS lat_cell,
  ROUND(longitude, 2) AS lon_cell,
  COUNT(*) AS trips,
  COUNT(DISTINCT station) AS stations
FROM input
WHERE NOT (latitude = 0 AND longitude = 0)
GROUP BY 1, 2
ORDER BY trips DESC

Rows

Lat cell	Lon cell	Trips	Stations
45.52	−123	416	6
45.53	−123	248	2
45.51	−123	209	4
45.53	−123	155	3
45.52	−123	147	4
45.5	−123	126	3
45.53	−123	124	3
45.56	−123	44	2

Why isn't the busiest station the busiest place?

Because a dock is a point and demand is an area. Union Station has 216 pickups, almost three times any other dock, so on a station map it looks like the center of the system. Round the pickups into 1 km cells and downtown wins: 6 docks a few blocks apart add up to 416. If you're deciding where to put more bikes, the grid is the map to read.

Grid cell size

In Tableau

The shelves for the finished chart

Marks Circle

SizeCOUNT([trip_id])
Detail[station]

ColumnsAVG([longitude])

RowsAVG([latitude])

Green pills are continuous, blue are discrete, as in Tableau.

How many decimals should I round to?

Pick the cell size that matches the decision. 3 decimals (about 100 m) is a street corner: good for placing a dock. 2 decimals (about 1 km) is a neighbourhood: good for staffing and rebalancing. 1 decimal (about 11 km) is a whole city and only helps when you compare cities.

Variants

Other ways to draw it

Distance from the depot

Color stations by how far a van drives to rebalance them. The depot sits at Union Station (45.5289, -122.6765).

Point at a circle to read it.

km from depot: 02.254.5

Common mistake

Summing coordinates

SUM([latitude]) adds a station's latitude once per pickup, so SW 6th & Alder, with 75 rides, lands at latitude 3,414 and falls off the map. Coordinates are positions, not amounts: average them, or use MIN or MAX.

WrongStarts with Dockless bike, Eastbank Esplanade, Hawthorne Bridge West…

Point at the line to read it.

Tableau

Rows: SUM([latitude])

SQL

SELECT
  station,
  SUM(latitude) AS latitude,
  SUM(longitude) AS longitude
FROM input
GROUP BY station
ORDER BY station

RightStarts with Dockless bike, Eastbank Esplanade, Hawthorne Bridge West…

Point at the line to read it.

Tableau

Rows: AVG([latitude])

SQL

SELECT
  station,
  AVG(latitude) AS latitude,
  AVG(longitude) AS longitude
FROM input
GROUP BY station
ORDER BY station

Common mistake

Leaving the (0, 0) rows in

A missing GPS fix often arrives as 0, 0, a spot in the Atlantic off West Africa that people call Null Island. Here 23 rows, 1.5% of the file, move the average point 168 km. Filter them out before you average, and check for empty coordinates too.

Wrong

Point at a bar to read it.

SQL

SELECT AVG(latitude), AVG(longitude)
FROM input

Right

Point at a bar to read it.

SQL

SELECT AVG(latitude), AVG(longitude)
FROM input
WHERE NOT (latitude = 0 AND longitude = 0) AND latitude IS NOT NULL AND longitude IS NOT NULL

Common mistake

Judging demand by the biggest circle

One big dock catches the eye, but riders don't care which dock is nearest, only that a bike is. Count pickups per grid cell before you decide where to add bikes.

WrongStarts with Union Station…

Point at a bar to read it.

SQL

SELECT station, COUNT(*) AS trips
FROM input
GROUP BY station
ORDER BY trips DESC
LIMIT 1

Right

Point at a bar to read it.

SQL

SELECT
  ROUND(latitude, 2) AS lat_cell,
  ROUND(longitude, 2) AS lon_cell,
  COUNT(*) AS trips
FROM input
WHERE NOT (latitude = 0 AND longitude = 0)
GROUP BY 1, 2
ORDER BY trips DESC
LIMIT 1

Run it on your own data

Any CSV with latitude and longitude columns works: deliveries, store visits, sensor readings. Drop it here, pick the two coordinate columns and a cell size, and the grid counts itself in your browser. Nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, pickups.csv, shows the shape.

Questions

Why is my map just one dot?

Latitude and longitude are measures, so Tableau averages them over every row and draws the average. Put the location (station, store, customer) on Detail to get one mark per location.

Why is my map rotated or upside down?

Latitude and longitude are on the wrong shelves. Longitude runs east to west, so it goes on Columns; latitude runs north to south, so it goes on Rows.

How big is a grid cell when I round to 2 decimals?

0.01 degrees of latitude is about 1.1 km everywhere. 0.01 degrees of longitude shrinks toward the poles: about 1.1 km at the equator, about 780 m in Portland, about 560 m in Oslo. Cells are rectangles, not squares, away from the equator.

How do I measure the distance between two points?

In Tableau, DISTANCE(MAKEPOINT([lat1], [lon1]), MAKEPOINT([lat2], [lon2]), 'km'). In SQL, use the haversine formula shown in the distance variant.

Can I plot these points in xmetric?

Point maps in the xmetric app are coming. You can already run the binning and distance steps on your CSV here, in your browser.

Sources

Tableau: Create maps that show points checked October 30, 2026
Tableau: Spatial functions (MAKEPOINT, DISTANCE) checked October 30, 2026