Polemic

Hamish Campbell – Aotearoa New Zealand

GitHub Twitter LinkedIn Keybase

Popular New Zealand Road Names

19 Jan 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:

  1. Download the data in the Shapefile format
  2. 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)
1Accessway2414220,876
2Service Lane49254,500
3Roadway452335,172
4State Highway 11252,168,214
5George Street7438,105
6Queen Street6950,181
7Beach Road69103,280
8High Street6868,671
9King Street6642,302
10Station Road6680,138
11Rata Street5725,589
12School Road5575,847
13Grey Street5436,090
14State Highway 252991,692
15Church Street5240,540
16Elizabeth Street5221,323
17Victoria Street5045,263
18Rimu Street4817,075
19River Road48273,968
20Bridge Street4630,604
21State Highway 6461,183,521
22Cemetery Road4656,800
23Main Road4585,202
24Albert Street4425,832
25Domain Road4447,879
26Mill Road43111,434
27Short Street436,575
28Boundary Road42152,026
29Miro Street4217,852
30William Street4117,808
31Matai Street4015,183
32Totara Street4018,546
33Wilson Road39104,471
34Seddon Street3825,237
35John Street3714,036
36Princes Street3626,509
37Charles Street3516,041
38Oxford Street3523,789
39Nelson Street3517,365
40James Street3515,162
41Valley Road3492,760
42York Street3412,786
43Tui Street3412,064
44Kowhai Street3412,220
45Campbell Street3217,273
46Kent Street3218,598
47Ward Street3217,431
48Russell Street3214,733
49Duke Street3217,436
50Main Street3233,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.