Popular New Zealand Road Names

19 January 2015

I pulled down Land Information New Zealand's Road Centre Line dataset to have quick look at name statistics. To analyse the data, import it to Postgres with shp2pgsql:

Download the data in the Shapefile format
Extract and load into an existing Postgresql/PostGIS database:

shp2pgsql nz-road-centre-line-electoral.shp | psql [database_name]

Now you can run queries against the data. To get the 50 most popular road names with their total length:

SELECT 
    "name",
    COUNT(*) as total_count,
    ROUND(SUM(ST_LENGTH(geom))) as total_length
FROM "nz-road-centre-line-electoral" GROUP BY "name"
ORDER BY total_count DESC
LIMIT 50

And the results:

Rank	Name	Frequency	Length (m)
1	Accessway	2414	220,876
2	Service Lane	492	54,500
3	Roadway	452	335,172
4	State Highway 1	125	2,168,214
5	George Street	74	38,105
6	Queen Street	69	50,181
7	Beach Road	69	103,280
8	High Street	68	68,671
9	King Street	66	42,302
10	Station Road	66	80,138
11	Rata Street	57	25,589
12	School Road	55	75,847
13	Grey Street	54	36,090
14	State Highway 2	52	991,692
15	Church Street	52	40,540
16	Elizabeth Street	52	21,323
17	Victoria Street	50	45,263
18	Rimu Street	48	17,075
19	River Road	48	273,968
20	Bridge Street	46	30,604
21	State Highway 6	46	1,183,521
22	Cemetery Road	46	56,800
23	Main Road	45	85,202
24	Albert Street	44	25,832
25	Domain Road	44	47,879
26	Mill Road	43	111,434
27	Short Street	43	6,575
28	Boundary Road	42	152,026
29	Miro Street	42	17,852
30	William Street	41	17,808
31	Matai Street	40	15,183
32	Totara Street	40	18,546
33	Wilson Road	39	104,471
34	Seddon Street	38	25,237
35	John Street	37	14,036
36	Princes Street	36	26,509
37	Charles Street	35	16,041
38	Oxford Street	35	23,789
39	Nelson Street	35	17,365
40	James Street	35	15,162
41	Valley Road	34	92,760
42	York Street	34	12,786
43	Tui Street	34	12,064
44	Kowhai Street	34	12,220
45	Campbell Street	32	17,273
46	Kent Street	32	18,598
47	Ward Street	32	17,431
48	Russell Street	32	14,733
49	Duke Street	32	17,436
50	Main Street	32	33,201

Short Street - clearly well named at an average length of 153m.

And of course we can get the total length of all road centrelines in the dataset with:

SELECT 
    SUM(ST_LENGTH(geom)) as total_length
FROM "nz-road-centre-line-electoral"

Which gives 123,086,930 metres of road.*

*Note that the dataset exists for electoral addressing purposes, so this comment on the layer information is important: Because some roads have multiple names for part of their lengths, some road centreline geometries will duplicate parts of other roads even though there is in reality only one road formation. In other words, we've got some duplication above.