The subterranean secrets of London’s super-rich are revealed in a study of 4,650 basements granted planning permission in some of the capital’s most affluent neighbourhoods, with hundreds of swimming pools and cinemas in the most luxurious developments.

Almost 1,000 gyms, 376 pools, 456 cinemas, 381 wine stores and cellars and 115 staff rooms, including bedrooms for nannies and au pairs, were found in the plans for the basements approved by seven London boroughs between 2008 and 2017.

The data, collected by Newcastle University’s global urban research unit from planning applications, identified two basements with more than one pool, including one with an artificial beach.

The study, Mapping Subterranean London: the Hidden Geography of Residential Basement Developments, maps their proliferation in Kensington and Chelsea, Westminster, Hammersmith and Fulham, Haringey, Camden, Islington and Wandsworth. The researchers calculated that the combined depth of all of the schemes would be 15,289 metres, almost 50 times the height of the Shard.

The study only covers the basements granted planning consent under existing properties. Other basements have been constructed under new homes. One new mansion in Notting Hill, on sale for £25m, includes a double-level basement with a 21-metre swimming pool.

The researchers do not know how many of the basements they found have been built, because current planning law allows owners to commence work up to three years after permission is granted, with no deadline to complete. Several leading basement architects and developers said, however, that the vast majority of approved projects are completed.

Other amenities uncovered in the research include 547 media rooms, 340 games and recreation rooms, 242 saunas or steam rooms, 63 underground garages and parking facilities, and one car museum.

The study, undertaken for the Guardian, classified the basements into three size categories. Standard developments, of a single storey under the footprint of the house, made up four in five of the schemes. Their amenities were generally utilitarian, often containing extra living or utility rooms.

The researchers also identified 785 large basements, of at least two storeys under the footprint of the house, or one storey extending well under the garden. Most were large enough to house a swimming pool.

Another 112 basements were classified as mega, of at least three storeys in depth under the footprint of the house or two storeys in depth and extended under the garden. Some of the largest were up to 18 metres deep and bigger than the rest of the property above ground, warranting the nickname of iceberg homes.

The most extravagant schemes include a three-storey basement in Holland Park, Kensington and Chelsea, with amenities including a swimming pool, plunge pool, beach, sauna, steam room, jacuzzi, media room, gym and staff quarters.

A three-storey scheme in Mayfair, Westminster, which was granted planning permission under an existing basement, features a swimming pool, cinema, car park, sauna, steam room and dance floor.

A single-storey basement in Primrose Hill, Camden, includes a Turkish bath, Roman bath, cigar room, swimming pool, plunge pool, sauna, massage room, cinema, games room and bar, wine store, pilates room, gym and banquet hall.

Roger Burrows, professor of cities at Newcastle University, said: “In many areas of ‘super prime’ London planning restrictions often makes it impossible to extend properties laterally or add floors on top. Consequently residential basement developments in the wealthiest parts of London have increased markedly.”

Becky Fatemi, the managing director of Rokstone estate agents, said 34% of the 140 properties the company has sold in the last five years had basements, with the most expensive sold for more than £20m in Kensington. Demand has grown recently for basements with accommodation for a nanny and other staff.

The study shows how the size and splendour of basements varies across the seven boroughs. Kensington and Chelsea and Westminster, the wealthiest boroughs surveyed, have the highest number of swimming pools.

In contrast, the researchers found only three mega basements in Hammersmith and Fulham, and none in Haringey, Islington or Wandsworth.

The Kensington and Chelsea MP, Emma Dent Coad, said the local council had once approved a three-level basement with a diving board. “I have sympathy for people who need more space for an expanding family, but building a beach under your mansion when you’re hardly going to be there is something else entirely,” she said.

“While we have people sleeping on the streets we really have to look at what we’re doing in our borough because that is bad planning.”

The streets with the highest numbers of basements granted planning permission include Tregunter Road in Chelsea with 22 and Hamilton Terrace in St John’s Wood with 16. Some of the latter basements extend to properties on the adjacent Hamilton Close.

Sir Hugh Cortazzi, a former British ambassador to Japan, and his wife Elizabeth, who live on Hamilton Close, said the number of excavations under houses on the mews in the past decade had blighted their lives.

“It’s depressing,” he said. “We’ve had huge dumper trucks blocking the mews. There’s still work going on. I fear the demand will go on and on.”

Karen Buck, the MP for Westminster North, who has campaigned for tighter restrictions on basement developments, said: “The sheer opulence of many of the larger basement excavations caused jaws to drop even in fairly affluent neighbourhoods like St John’s Wood and Bayswater, where neighbours have sometimes found themselves under siege from these developments. And it wasn’t uncommon to be told that the building was scarcely occupied at the end.”

The study found that council policies introduced to restrict the size of basement developments had led to a downturn in applications in recent years, but the researchers added that the numbers being granted approval in the past two years were similar to those in 2008-12, only now there were fewer mega-basements.