search

LukePrior / nbn-upgrade-map

Interactive map showing premises eligible for the NBN FTTP upgrade program.
https://nbn.lukeprior.com/
MIT License
138 stars 11 forks source link

Emit total progress estimate (addresses vs total) #126

Closed lyricnz closed 1 year ago

lyricnz commented 1 year ago

Similar to the tallies already emitted, it would be good to know what fraction of the total number of addresses were completed.

I think the suburbs we have already completed tend to skew larger just from looking at the total number of addresses in the results output. (ps might be good to have a tally of total number of premises/all in gnaf).

lyricnz commented 1 year ago

FWIW the query to count the number of GNAF addresses in the suburbs+states already processed is something like:

            SELECT COUNT(*) FROM address_principals WHERE
(state = 'NSW' AND locality_name IN ('ROSEBERY', 'CASINO', 'DUNDAS VALLEY', 'ETTALONG BEACH', 'LLANARTH', 'MASCOT', 'MITCHELL', 'STRATHFIELD', 'CALLALA BEACH', 'BALGOWNIE', 'ABERCROMBIE', 'KENSINGTON', 'WEST WALLSEND', 'WARILLA', 'MOREE', 'RHODES', 'CASTLE HILL', 'ELDERSLIE', 'FAIRY MEADOW', 'MOUNT KEMBLA', 'TERRANORA', 'LAKE ILLAWARRA', 'GREEN VALLEY', 'NOWRA', 'YOUNG', 'BLACKWALL', 'NIAGARA PARK', 'HAMLYN TERRACE', 'GLEN ALPINE', 'SPRING FARM', 'NORAH HEAD', 'KELLYVILLE', 'GREEN POINT', 'GEORGES HALL', 'SUSSEX INLET', 'NEWCASTLE', 'HAYWARDS BAY', 'NORTH NOWRA', 'KATOOMBA', 'HOLSWORTHY', 'BUFF POINT', 'TWEED HEADS SOUTH', 'GLENWOOD', 'DENILIQUIN', 'ISLINGTON', 'MILTON', 'KURRI KURRI', 'PRAIRIEWOOD', 'CALALA', 'CAMPSIE', 'FLETCHER', 'EGLINTON', 'PETERSHAM', 'SEVEN HILLS', 'ST PETERS', 'WHITE ROCK', 'BYRON BAY', 'CHINDERAH', 'CAMERON PARK', 'TUNCURRY', 'CORLETTE', 'NORAVILLE', 'GOONELLABAH', 'BOOKER BAY', 'PORT KEMBLA', 'BELMONT', 'QUAKERS HILL', 'NORTH MANLY', 'CHARMHAVEN', 'HILLVUE', 'MARMONG POINT', 'WENTWORTH FALLS', 'SHELL COVE', 'BATEMANS BAY', 'SAN REMO', 'YATTALUNGA', 'EDGEWORTH', 'WORRIGEE', 'CARNES HILL', 'LAVINGTON', 'BAR BEACH', 'LAURIETON', 'WELLINGTON', 'HAMILTON', 'LAKELANDS', 'STOCKTON', 'SHORTLAND', 'DOUBLE BAY', 'NEWCASTLE WEST', 'NARARA', 'EAST TAMWORTH', 'WATANOBBI', 'BASIN VIEW', 'COROWA', 'BEROWRA HEIGHTS', 'APPIN', 'SHELLHARBOUR', 'ST HELENS PARK', 'MEDOWIE', 'MOUNT HUTTON', 'EARLWOOD', 'COOKS HILL', 'CHERRYBROOK', 'ALBION PARK', 'FARMBOROUGH HEIGHTS', 'CORDEAUX HEIGHTS', 'ROZELLE', 'HARRINGTON', 'MONTEFIORES', 'LIVERPOOL', 'AIRDS', 'KEIRAVILLE', 'WINDRADYNE', 'MENAI', 'KELSO', 'TARRO', 'NORTH TAMWORTH', 'NORTH WOLLONGONG', 'MACKSVILLE', 'WETHERILL PARK', 'KIRRAWEE', 'GILGANDRA', 'CONDELL PARK', 'WALLSEND', 'WORONORA HEIGHTS', 'BEROWRA', 'MUSWELLBROOK', 'EPPING', 'TORONTO', 'BUDGEWOI', 'WEST KEMPSEY', 'SURFSIDE', 'BOSSLEY PARK', 'COWRA', 'HELENSBURGH', 'WAUCHOPE', 'HALEKULANI', 'DALMENY', 'CABARITA BEACH', 'BERKELEY', 'WATTLE GROVE', 'BANORA POINT', 'ASHMONT', 'GLENFIELD PARK', 'OLD BAR', 'PRIMBEE', 'LAKE HAVEN', 'THIRROUL', 'WINDALE', 'MARDI', 'PORT MACQUARIE', 'WOONGARRAH', 'FIGTREE', 'DENHAMS BEACH', 'BONNELLS BAY', 'COONABARABRAN', 'BARRACK POINT', 'MACQUARIE PARK', 'WICKHAM', 'NEWCASTLE EAST', 'GOROKAN', 'COOLANGATTA', 'CANLEY VALE', 'WEST ALBURY', 'RUSE', 'VILLAWOOD', 'MIDDLETON GRANGE', 'PARKES', 'SINGLETON HEIGHTS', 'ENFIELD', 'WANGI WANGI', 'FAIRFIELD', 'COLLAROY', 'THE HILL', 'MANNERING PARK', 'NORTH ALBURY', 'GUNNEDAH', 'SPEERS POINT', 'ERINA', 'FLINDERS', 'CAMPERDOWN', 'NORTH RICHMOND', 'ANGOURIE', 'BARNSLEY', 'WINDANG', 'SURF BEACH', 'VINCENTIA', 'ST GEORGES BASIN', 'HUNTERVIEW', 'WALLABI POINT', 'TULLIMBAR', 'NARRAWALLEE', 'BLUE HAVEN', 'FORBES', 'KILLINGWORTH', 'MOUNT WARRIGAL', 'BASS HILL', 'HOXTON PARK', 'WOODBINE', 'ZETLAND', 'CESSNOCK', 'PEMULWUY', 'LITHGOW', 'PARKLEA', 'TUGGERAH', 'LISAROW', 'ANNANDALE', 'OXLEY VALE', 'NORTH BATEMANS BAY', 'HASTINGS POINT', 'MOUNT OUSLEY', 'COOMA', 'MALONEYS BEACH', 'MARRICKVILLE', 'ALBURY', 'UMINA BEACH', 'MEREWETHER', 'MUDGEE', 'EDEN', 'GRANVILLE', 'THE JUNCTION', 'KANWAL', 'NORWEST', 'NARELLAN', 'WEST HOXTON', 'EAST ALBURY', 'WYONGAH', 'SUTHERLAND', 'CANTERBURY', 'EASTWOOD', 'CASUARINA', 'WYOMING', 'THORNTON', 'KOTARA', 'SOUTH TAMWORTH', 'WAGGA WAGGA', 'BELMONT NORTH', 'BOWRAL', 'AMBARVALE', 'BATHURST', 'KELLYVILLE RIDGE', 'LEURA', 'HUNTLEYS COVE', 'ADAMSTOWN', 'SALAMANDER BAY', 'ERMINGTON', 'KAHIBAH', 'CASULA', 'HILLSBOROUGH', 'BLAYNEY', 'MARYLAND', 'BRADBURY', 'TUMUT', 'CALLALA BAY', 'BILGOLA PLATEAU', 'OCEAN SHORES', 'MARYVILLE', 'WEST BATHURST', 'TOUKLEY', 'BEVERLY HILLS', 'BATEHAVEN', 'CROYDON PARK', 'CANTON BEACH', 'RAGLAN', 'MACQUARIE HILLS', 'SOLDIERS POINT', 'CARDIFF', 'ALBION PARK RAIL', 'OAK FLATS', 'PADSTOW', 'FINGAL HEAD', 'BEAUMONT HILLS', 'WADALBA', 'WAMBERAL', 'ALSTONVILLE', 'WEST TAMWORTH', 'LENNOX HEAD', 'ELEEBANA', 'CAMDEN PARK', 'FINGAL BAY', 'NEWTOWN', 'NEW LAMBTON', 'WOODCROFT', 'NARRABRI', 'AVALON BEACH', 'SOUTH NOWRA', 'HORNINGSEA PARK', 'SWANSEA', 'SHOAL BAY', 'SOUTH WEST ROCKS', 'DARLINGTON', 'GRAFTON', 'NORTH SYDNEY', 'DARLINGHURST', 'BUNGENDORE', 'BUNDEENA', 'MOUNT PLEASANT', 'BOLWARRA HEIGHTS', 'CARDIFF SOUTH', 'ADAMSTOWN HEIGHTS', 'TAHMOOR', 'TWEED HEADS', 'GWYNNEVILLE', 'TURRAMURRA', 'CRINGILA', 'SOUTH TURRAMURRA', 'POTTSVILLE', 'WAITARA', 'ELERMORE VALE', 'BANKSTOWN', 'TERRIGAL', 'SMITHFIELD', 'ABERGLASSLYN', 'OURIMBAH', 'LAKE HEIGHTS', 'GLENDALE', 'RAYMOND TERRACE', 'INVERELL', 'BROADMEADOW', 'VOYAGER POINT', 'MEREWETHER HEIGHTS', 'SMEATON GRANGE', 'DUBBO', 'SURRY HILLS', 'HOLMESVILLE', 'GULGONG', 'WOLLSTONECRAFT', 'KINCUMBER', 'PUNCHBOWL', 'NELSON BAY', 'ACACIA GARDENS', 'HAMILTON SOUTH', 'COORANBONG', 'RYDE', 'COLEDALE', 'GRIFFITH', 'CLYDE', 'FORSTER', 'SKENNARS HEAD', 'FORRESTERS BEACH', 'ALLAMBIE HEIGHTS', 'WOONONA', 'CRONULLA', 'MOSS VALE', 'VALENTINE', 'WHITEBRIDGE', 'LAMBTON', 'BOWRAVILLE', 'THE PONDS', 'LAKE MUNMORAH', 'LAVENDER BAY', 'ULLADULLA', 'HARRINGTON PARK', 'KINGSCLIFF', 'RUTHERFORD', 'CAMDEN SOUTH', 'WOLLONGONG', 'WARATAH', 'WARNERS BAY', 'HOMEBUSH WEST', 'CULBURRA BEACH', 'MOLLYMOOK', 'KARIONG', 'DAVISTOWN', 'BANGALEE', 'CATALINA', 'WYEE', 'SARATOGA', 'KINGS LANGLEY', 'MOUNT ANNAN', 'GLENHAVEN', 'GOULBURN', 'WOOLWICH', 'JERRABOMBERRA', 'YAMBA', 'TOWRADGI', 'PALM BEACH', 'BLACKHEATH', 'GLENROY', 'BLACKBUTT', 'BELLA VISTA', 'BROOKVALE', 'PICTON', 'SOUTH GRAFTON', 'BROULEE', 'LONG BEACH', 'SUFFOLK PARK', 'THIRLMERE', 'KINGSFORD', 'GERRINGONG', 'GERROA', 'ANNA BAY', 'PADDINGTON', 'WOLLONGBAR', 'ROUSE HILL', 'WEST WYALONG', 'MERIMBULA', 'ALEXANDRIA', 'BRIGHTON-LE-SANDS', 'GLEN INNES', 'NORTH LAMBTON', 'KINGSWOOD', 'NARRANDERA', 'BUNDANOON', 'JINDABYNE', 'CROSSLANDS', 'SINGLETON', 'HOMEBUSH', 'ROSEMEADOW', 'BEACONSFIELD', 'OLD EROWAL BAY', 'GLADESVILLE', 'SOUTH ALBURY', 'BOGANGAR', 'MURWILLUMBAH', 'MAIANBAR', 'SOUTH GOLDEN BEACH', 'SOUTH BATHURST', 'NORTH KELLYVILLE', 'DEE WHY', 'WYONG', 'EAST BALLINA', 'KURNELL', 'WEST PENNANT HILLS', 'CURRANS HILL', 'HINCHINBROOK', 'THURGOONA', 'PRESTONS', 'SANCTUARY POINT', 'SHOALHAVEN HEADS', 'JAMISONTOWN', 'BARRACK HEIGHTS', 'NAMBUCCA HEADS', 'MAITLAND', 'TUGGERAWONG', 'MITTAGONG', 'AUSTINMER', 'WARRAWONG', 'ENMORE', 'TWEED HEADS WEST', 'CHIPPING NORTON', 'ERSKINEVILLE', 'GATESHEAD', 'WOMBARRA', 'TELARAH', 'SCONE', 'BOORAGUL', 'CLAYMORE', 'LEICHHARDT', 'CAMPBELLTOWN', 'MOLLYMOOK BEACH', 'BLAIRMOUNT', 'BERESFIELD', 'WOY WOY', 'CAVES BEACH', 'STANMORE', 'AVOCA BEACH', 'HAMMONDVILLE', 'HUNTERS HILL', 'NARROMINE', 'CHARLESTOWN', 'SUNSHINE BAY', 'PARRAMATTA', 'GLENMORE PARK', 'ORANGE', 'NARELLAN VALE', 'MOUNT AUSTIN', 'UNANDERRA', 'STANHOPE GARDENS', 'YASS', 'SOUTH PENRITH', 'KOORINGAL', 'LEWISHAM'))
 OR (state = 'VIC' AND locality_name IN ('VENUS BAY', 'SMITHS BEACH', 'CASTLEMAINE', 'LEOPOLD', 'HARKNESS', 'HOPPERS CROSSING', 'WANDONG', 'KYABRAM', 'MOUNT WAVERLEY', 'WANGARATTA', 'TRARALGON EAST', 'DAYLESFORD', 'CRANBOURNE EAST', 'GEELONG', 'HAMLYN HEIGHTS', 'MOUNT MARTHA', 'TORQUAY', 'ROMSEY', 'NEWTOWN', 'SORRENTO', 'CRANBOURNE NORTH', 'TRARALGON', 'CRANBOURNE', 'SOUTH MELBOURNE', 'LARA', 'INDENTED HEAD', 'COHUNA', 'SYDENHAM', 'LYSTERFIELD', 'BOTANIC RIDGE', 'DANDENONG SOUTH', 'BAIRNSDALE', 'SUNSET STRIP', 'LYNBROOK', 'SILVERLEAVES', 'CHUM CREEK', 'HIGHETT', 'KEILOR LODGE', 'COWES', 'PEARCEDALE', 'SOMERS', 'RIDDELLS CREEK', 'TARNEIT', 'KIALLA', 'LAKE WENDOUREE', 'SOUTH MORANG', 'MIDDLE PARK', 'NEWPORT', 'LEONGATHA', 'BELMONT', 'SEBASTOPOL', 'RAVENHALL', 'ANGLESEA', 'BEACONSFIELD', 'LOVELY BANKS', 'PORTARLINGTON', 'SALE', 'LYNDHURST', 'WY YUNG', 'DRYSDALE', 'REDAN', 'YARRA GLEN', 'YARRAWONGA', 'GISBORNE', 'DROMANA', 'DEER PARK', 'FERNTREE GULLY', 'GREENSBOROUGH', 'SWAN HILL', 'LONG GULLY', 'SOUTHBANK', 'CORIO', 'CANNONS CREEK', 'BERWICK', 'BAYSWATER', 'CAPE PATERSON', 'LANG LANG', 'KEILOR', 'NARRE WARREN SOUTH', 'KANGAROO FLAT', 'MERNDA', 'BURNSIDE HEIGHTS', 'THOMASTOWN', 'CAROLINE SPRINGS', 'SUNSHINE WEST', 'DERRIMUT', 'STRATHDALE', 'YARRAGON', 'SEYMOUR', 'NORTH BENDIGO', 'BALLAN', 'WEST WODONGA', 'TRAFALGAR', 'CAPE WOOLAMAI', 'ROSEBUD', 'EAST BENDIGO', 'FAIRHAVEN', 'CROYDON', 'KALIMNA', 'BAYSWATER NORTH', 'NARRE WARREN NORTH', 'SOUTH GEELONG', 'SUNSHINE', 'ROWVILLE', 'WALLAN', 'HORSHAM', 'WODONGA', 'WARBURTON', 'NORLANE', 'TOORADIN', 'MEADOW HEIGHTS', 'JAN JUC', 'PORTSEA', 'BROOKFIELD', 'WENDOUREE', 'DROUIN', 'BRIGHT', 'WOORI YALLOCK', 'HASTINGS', 'SOUTH DUDLEY', 'NORTH SHORE', 'PAKENHAM', 'HAMPTON PARK', 'SAFETY BEACH', 'DALYSTON', 'CRANBOURNE WEST', 'HILLSIDE', 'TOOTGAROOK', 'FLORA HILL', 'GEELONG WEST', 'EASTWOOD', 'DELACOMBE', 'WERRIBEE', 'WONTHAGGI', 'COBRAM', 'MANIFOLD HEIGHTS', 'KOO WEE RUP', 'CONNEWARRE', 'DELAHEY', 'POINT LONSDALE', 'KENNINGTON', 'BRAYBROOK', 'CAMPERDOWN', 'BELL POST HILL', 'SOMERVILLE', 'CLIFTON SPRINGS', 'GREENVALE', 'ATTWOOD', 'HIGHTON', 'LAKE GARDENS', 'NORTH MELBOURNE', 'MARIBYRNONG', 'NARRE WARREN', 'BEACONSFIELD UPPER', 'HEATHCOTE JUNCTION', 'QUEENSCLIFF', 'LAKES ENTRANCE', 'ROCKBANK', 'COLAC', 'NORTH WONTHAGGI', 'RHYLL', 'GROVEDALE', 'KEYSBOROUGH', 'SEVILLE EAST', 'LOCH SPORT', 'CAPEL SOUND', 'PORT FAIRY', 'RESEARCH', 'AIREYS INLET', 'GOLDEN SQUARE', 'HAMILTON', 'BENALLA', 'EAST BAIRNSDALE', 'SEVILLE', 'BARWON HEADS', 'RESERVOIR', 'CAIRNLEA', 'WAURN PONDS', 'PORT MELBOURNE', 'ST ALBANS PARK', 'ST KILDA', 'MYRTLEFORD', 'DIAMOND CREEK', 'HEALESVILLE', 'SUNSHINE NORTH', 'HERNE HILL', 'MILDURA', 'KILMORE', 'CRIB POINT', 'CAMPBELLFIELD', 'APOLLO BAY', 'WARRAGUL', 'YARRAM', 'SUNBURY', 'KILSYTH SOUTH', 'LENEVA', 'MOOLAP', 'WILLIAMSTOWN NORTH', 'YARRA JUNCTION', 'RYE', 'OCEAN GROVE', 'SPRING GULLY', 'QUARRY HILL', 'BELL PARK', 'ALFREDTON', 'ST LEONARDS', 'MORNINGTON', 'WANTIRNA', 'WILLIAMSTOWN', 'SURF BEACH', 'LAUNCHING PLACE', 'PORTLAND', 'WARRNAMBOOL', 'MORWELL', 'PAYNESVILLE', 'SUNDERLAND BAY', 'HUGHESDALE', 'WARNEET', 'WHITTINGTON', 'BENDIGO', 'LUCKNOW', 'MOE', 'BURNSIDE', 'HURSTBRIDGE', 'NEWCOMB', 'BARANDUDA', 'BROADFORD', 'DOREEN', 'ECHUCA', 'GLEN WAVERLEY', 'TAYLORS LAKES', 'ROXBURGH PARK', 'ST ALBANS', 'ELTHAM', 'TYABB', 'WIMBLEDON HEIGHTS', 'WHITTLESEA', 'WANDANA HEIGHTS', 'BLIND BIGHT', 'CROYDON SOUTH', 'ALBERT PARK', 'STRATHFIELDSAYE', 'OAKLEIGH', 'MCCRAE', 'CRAIGIEBURN', 'INVERLOCH', 'TAYLORS HILL'))
 OR (state = 'QLD' AND locality_name IN ('UPPER KEDRON', 'DICKY BEACH', 'DEERAGUN', 'VARSITY LAKES', 'EAGLEBY', 'JONES HILL', 'NEWPORT', 'YEPPOON', 'MARYBOROUGH', 'BUCCAN', 'PALMWOODS', 'WHITE ROCK', 'WALKERVALE', 'LOGANLEA', 'SEVENTEEN MILE ROCKS', 'AROONA', 'WYNNUM WEST', 'WINDAROO', 'FLINDERS VIEW', 'TENERIFFE', 'RUNCORN', 'GILSTON', 'GYMPIE', 'NARANGBA', 'PADDINGTON', 'KAWUNGAN', 'ARUNDEL', 'MCDOWALL', 'GLENEAGLE', 'MAIN BEACH', 'INNISFAIL', 'MOGGILL', 'MOOLOOLABA', 'ATHERTON', 'EVERTON HILLS', 'ROCKVILLE', 'MORAYFIELD', 'COOROIBAH', 'BERRINBA', 'JIMBOOMBA', 'GOONDI BEND', 'BUSHLAND BEACH', 'MACHANS BEACH', 'FRENCHVILLE', 'SVENSSON HEIGHTS', 'KEPNOCK', 'NEWTOWN', 'ACACIA RIDGE', 'MALENY', 'CABOOLTURE', 'TUGUN', 'BULIMBA', 'JENSEN', 'KARALEE', 'LOGAN VILLAGE', 'CAMIRA', 'CLIFTON BEACH', 'TANNUM SANDS', 'PARREARRA', 'BEACHMERE', 'MOUNT COOLUM', 'IDALIA', 'BURRUM HEADS', 'BUNDALL', 'GREENBANK', 'BIRTINYA', 'SOUTH GLADSTONE', 'ALEXANDRA HEADLAND', 'TIN CAN BAY', 'BURPENGARY', 'DALBY', 'GLENVALE', 'NEWSTEAD', 'EIMEO', 'ASHGROVE', 'FOREST LAKE', 'NERANG', 'WALKERSTON', 'CARINDALE', 'CANNON VALLEY', 'ANDERGROVE', 'PINE MOUNTAIN', 'BELVEDERE', 'SPRINGFIELD LAKES', 'PETRIE', 'WOORIM', 'BUCASIA', 'CURRIMUNDI', 'YORKEYS KNOB', 'KIPPA-RING', 'WILSONTON HEIGHTS', 'LOWOOD', 'DARLING HEIGHTS', 'COOMERA', 'PARADISE POINT', 'BOHLE PLAINS', 'CLINTON', 'HERITAGE PARK', 'RICHLANDS', 'KEDRON', 'ZILZIE', 'MIRANI', 'FIG TREE POCKET', 'MAUDSLAND', 'GRIFFIN', 'AVENELL HEIGHTS', 'TAMBORINE MOUNTAIN', 'BRACKEN RIDGE', 'TOOWOOMBA CITY', 'GRANGE', 'MILLBANK', 'EVERTON PARK', 'BEAUDESERT', 'PARK AVENUE', 'DECEPTION BAY', 'SHELLY BEACH', 'WANDAL', 'ROBINA', 'EARLVILLE', 'KINGAROY', 'PIMPAMA', 'MANUNDA', 'VICTORIA POINT', 'GRACEMERE', 'PEREGIAN BEACH', 'SOUTHPORT', 'SANDSTONE POINT', 'GATTON', 'REGENTS PARK', 'TRINITY PARK', 'MANOORA', 'UPPER COOMERA', 'SUNSHINE BEACH', 'ORMEAU HILLS', 'WURTULLA', 'ASHMORE', 'SIPPY DOWNS', 'UNDERWOOD', 'BAYVIEW HEIGHTS', 'PALM COVE', 'ALLENSTOWN', 'SUNNYBANK HILLS', 'BRINSMEAD', 'OAKEY', 'WARNER', 'GAVEN', 'WANGAN', 'YAMANTO', 'BUNYA', 'ORMEAU', 'NORMAN GARDENS', 'GLADSTONE CENTRAL', 'ROSEWOOD', 'NINGI', 'MARCOOLA', 'BLACKWATER', 'CABOOLTURE SOUTH', 'STRETTON', 'KOONGAL', 'BRIDGEMAN DOWNS', 'BONGAREE', 'WELLINGTON POINT', 'MOFFAT BEACH', 'KEPERRA', 'MOSSMAN', 'UPPER CABOOLTURE', 'CLERMONT', 'CARSELDINE', 'BELLMERE', 'BANKSIA BEACH', 'MOUNT WARREN PARK', 'BUNGALOW', 'HOLLOWAYS BEACH', 'MOUNT COTTON', 'KEWARRA BEACH', 'EDENS LANDING', 'BOYNE ISLAND', 'TORQUAY', 'BEACONSFIELD', 'CANNON HILL', 'LAWNTON', 'SPRINGFIELD', 'PELICAN WATERS', 'BOWEN HILLS', 'LITTLE MOUNTAIN', 'AYR', 'LANDSBOROUGH', 'GLASS HOUSE MOUNTAINS', 'SUNNYBANK', 'PACIFIC PINES', 'MUDGEERABA', 'POINT VERNON', 'MOUNT LOUISA', 'SMITHFIELD', 'BRASSALL', 'BURLEIGH WATERS', 'YAROOMBA', 'MERIDAN PLAINS', 'CHINCHILLA', 'LOGANHOLME', 'JACOBS WELL', 'BONOGIN', 'EMERALD', 'MANSFIELD', 'EATONS HILL', 'BENOWA', 'CALOUNDRA WEST', 'DURACK', 'DYSART', 'WOREE', 'BURLEIGH HEADS', 'ROTHWELL', 'SARINA', 'KEARNEYS SPRING', 'BILOELA', 'BILINGA', 'REDLAND BAY', 'NAMBOUR', 'RURAL VIEW', 'AIRLIE BEACH', 'ANNANDALE', 'CASTAWAYS BEACH', 'CARRARA', 'URRAWEEN', 'NORTH LAKES', 'LABRADOR', 'KELSO', 'PIALBA', 'MARCUS BEACH', 'DOLPHIN HEADS', 'BLACKS BEACH', 'MOLENDINAR', 'RAINBOW BEACH', 'KINGS BEACH', 'WOODGATE', 'CRAIGLIE', 'KANIMBLA', 'BARLOWS HILL', 'PORT DOUGLAS', 'DARRA', 'MAROOCHYDORE', 'STRATFORD', 'REEDY CREEK', 'BALD HILLS', 'PEREGIAN SPRINGS', 'BUDDINA', 'SUNRISE BEACH', 'RACEVIEW', 'BARGARA', 'PROSERPINE', 'BUDERIM', 'NOOSAVILLE', 'SOUTH TOOWOOMBA', 'KAWANA', 'BELLARA', 'REDLYNCH', 'FLAGSTONE', 'COLLINGWOOD PARK', 'OXLEY', 'BATTERY HILL', 'MOUNT PLEASANT', 'CRESTMEAD', 'BAHRS SCRUB', 'KIRWAN', 'SINNAMON PARK', 'ROSSLYN', 'TWIN WATERS', 'WARANA', 'STAFFORD', 'NORVILLE', 'WAKERLEY', 'DOUGLAS', 'BRAY PARK', 'MORANBAH', 'NORTH MACKAY', 'BLI BLI', 'MUDJIMBA', 'DREWVALE', 'RASMUSSEN', 'CASHMERE', 'MEADOWBROOK', 'KURABY', 'DUNDOWRAN BEACH', 'CURRUMBIN WATERS', 'WULGURU', 'MINYAMA', 'BUNDABERG CENTRAL', 'BLACK RIVER', 'NOOSA HEADS', 'MURRUMBA DOWNS', 'ROBERTSON', 'COOLUM BEACH', 'OXENFORD', 'BRENDALE', 'CANNONVALE', 'ROMA', 'BEERWAH', 'BUNDABERG EAST', 'CORNUBIA', 'SLADE POINT', 'STEIGLITZ', 'AVOCA', 'GLENELLA', 'LOGAN RESERVE', 'JUBILEE POCKET', 'WHITFIELD', 'HARRISTOWN', 'BUNDABERG WEST', 'DEPOT HILL', 'CAPALABA', 'BURPENGARY EAST', 'THE RANGE', 'DIDDILLIBAH', 'ROCKHAMPTON CITY', 'AGNES WATER', 'WILSONTON', 'MANGO HILL', 'LAMMERMOOR', 'SURFERS PARADISE', 'MAREEBA', 'WATERFORD', 'WORONGARY', 'COOEE BAY', 'BENTLEY PARK', 'TARANGANBA', 'COOLANGATTA', 'BOWEN', 'TEWANTIN', 'CALOUNDRA', 'MOOROOBOOL', 'FERNY GROVE', 'MILTON', 'WONDUNNA', 'DRAYTON', 'COOYA BEACH', 'URANGAN', 'EDMONTON', 'BETHANIA', 'SHAILER PARK', 'TRINITY BEACH', 'NIKENBAH', 'SOUTHSIDE', 'SCARNESS', 'ALBANY CREEK', 'MARIAN', 'BERSERKER', 'NORTH IPSWICH', 'HAY POINT', 'KULUIN', 'BROWNS PLAINS', 'GOLDEN BEACH', 'FORTITUDE VALLEY', 'HEATHWOOD', 'MOUNT SHERIDAN', 'WARWICK', 'BELLBOWRIE', 'KIN KORA', 'HIGHLAND PARK', 'BUNDABERG NORTH', 'GOONDIWINDI', 'REDBANK', 'CONDON', 'BUNDABERG SOUTH', 'TULLY', 'THABEBAN', 'KARANA DOWNS', 'BURDELL', 'THE GAP', 'ARANA HILLS', 'SLACKS CREEK', 'HOLMVIEW', 'DOOLANDELLA', 'HIGHFIELDS', 'HELENSVALE', 'LAIDLEY', 'INALA', 'PACIFIC PARADISE', 'GORDONVALE', 'EIGHT MILE PLAINS', 'BOKARINA', 'FRESHWATER', 'COOROY', 'MOUNTAIN CREEK', 'FERNY HILLS', 'WESTCOURT', 'INGHAM'))
 OR (state = 'SA' AND locality_name IN ('ANGLE VALE', 'HAZELWOOD PARK', 'REYNELLA', 'GAWLER SOUTH', 'EVANDALE', 'FERRYDEN PARK', 'MUNNO PARA', 'ST PETERS', 'PENFIELD', 'GRANGE', 'LITTLEHAMPTON', 'GREENWITH', 'ABERFOYLE PARK', 'BALAKLAVA', 'BELAIR', 'WALKLEY HEIGHTS', 'CRAIGMORE', 'BOWDEN', 'EASTWOOD', 'ROXBY DOWNS', 'MOUNT GAMBIER', 'SHEIDOW PARK', 'OLD REYNELLA', 'GEPPS CROSS', 'NEW PORT', 'NORTH HAVEN', 'JOSLIN', 'SEMAPHORE SOUTH', 'SEATON', 'MORPHETT VALE', 'WOODVILLE NORTH', 'MAYLANDS', 'BLAKEVIEW', 'ELIZABETH', 'MORPHETTVILLE', 'RISDON PARK', 'WOODVILLE WEST', 'CLARE', 'CLEARVIEW', 'ELIZABETH SOUTH', 'MITCHELL PARK', 'ASCOT PARK', 'ELIZABETH NORTH', 'HALLETT COVE', 'PARK HOLME', 'FULLARTON', 'FLAGSTAFF HILL', 'ETHELTON', 'CHRISTIES BEACH', 'SALISBURY', 'ADELAIDE', 'HAPPY VALLEY', 'MARION', 'GOLDEN GROVE', 'BRIDGEWATER', 'MOUNT BARKER', 'HUNTFIELD HEIGHTS', 'ROSEWATER', 'TENNYSON', 'QUEENSTOWN', 'SEMAPHORE', 'FINDON', 'EDWARDSTOWN', 'TANUNDA', 'CHRISTIE DOWNS', 'PENNINGTON', 'COROMANDEL VALLEY', 'CROYDON PARK', 'ST MARYS', 'GLOBE DERBY PARK', 'BROOKLYN PARK', 'ROYAL PARK', 'MOONTA BAY', 'HAHNDORF', 'WOODVILLE PARK', 'BEVERLEY', 'PARKSIDE', 'NORTHFIELD', 'PORT NOARLUNGA', 'HACKHAM', 'CLOVELLY PARK', 'KILKENNY', 'HACKHAM WEST', 'MUNNO PARA WEST', 'MANSFIELD PARK', 'PARAFIELD GARDENS', 'RENMARK', 'GULFVIEW HEIGHTS', 'MYRTLE BANK', 'HAWTHORNDENE', 'LARGS NORTH', 'SOUTH PLYMPTON', 'BLACKWOOD', 'CHELTENHAM', 'WOODVILLE SOUTH', 'REYNELLA EAST', 'WEST LAKES', 'TAPEROO', 'ANGLE PARK', 'ELIZABETH DOWNS', 'POORAKA', 'WINGFIELD', 'OTTOWAY', 'GOOLWA', 'ALBERT PARK', 'SALISBURY HEIGHTS', 'MALVERN', 'EXETER', 'WEST LAKES SHORE', 'PLYMPTON PARK', 'PARALOWIE', 'GLENUNGA', 'SMITHFIELD', 'RENOWN PARK', 'ATHOL PARK', 'PORT ADELAIDE', 'EVANSTON PARK', 'PORT LINCOLN', 'SEMAPHORE PARK', 'HIGHGATE', 'OSBORNE', 'BELLEVUE HEIGHTS', 'MELROSE PARK', 'WILLASTON', 'GOOLWA BEACH', 'BURTON', 'WOODCROFT', 'FREWVILLE', 'ST CLAIR', 'THEBARTON', 'ENFIELD', 'PARA HILLS WEST', 'SMITHFIELD PLAINS', 'NAIRNE', 'PASADENA', 'GLANVILLE', 'BLAIR ATHOL', 'LINDEN PARK', 'ANDREWS FARM', 'DAVOREN PARK', 'HYDE PARK', 'HAWTHORN', 'LARGS BAY', 'MAWSON LAKES', 'KILBURN', 'NOARLUNGA DOWNS', 'O''HALLORAN HILL', 'BEAUMONT', 'WOODVILLE GARDENS', 'STEPNEY', 'PETERHEAD'))
 OR (state = 'WA' AND locality_name IN ('MARANGAROO', 'ASCOT', 'LYNWOOD', 'MARMION', 'LEEDERVILLE', 'ROSSMOYNE', 'WILLETTON', 'SPEARWOOD', 'CAMILLO', 'CLAREMONT', 'MOUNT NASURA', 'GWELUP', 'HALLS HEAD', 'NORANDA', 'WATERFORD', 'SUBIACO', 'GIRRAWHEEN', 'BALGA', 'HOCKING', 'LAKE COOGEE', 'ELLENBROOK', 'MARGARET RIVER', 'EAST FREMANTLE', 'GLENDALOUGH', 'DAWESVILLE', 'GEOGRAPHE', 'CURRAMBINE', 'BUSSELTON', 'DALYELLUP', 'ALBANY', 'NORTH BEACH', 'ATWELL', 'MIDLAND', 'VIVEASH', 'INGLEWOOD', 'SUCCESS', 'BEELIAR', 'JANDAKOT', 'LAKELANDS', 'BERTRAM', 'NORTH FREMANTLE', 'BEDFORD', 'MENORA', 'CANNING VALE', 'BURNS BEACH', 'FORRESTDALE', 'WOODBRIDGE', 'SAFETY BAY', 'TWO ROCKS', 'BICTON', 'HIGHGATE', 'FALCON', 'MOSMAN PARK', 'BROADWATER', 'CHURCHLANDS', 'MANJIMUP', 'KALGOORLIE', 'YANGEBUP', 'ASHFIELD', 'AVELEY', 'MALAGA', 'BELMONT', 'DUNSBOROUGH', 'YANCHEP', 'ROCKINGHAM', 'LOCKRIDGE', 'PADBURY', 'GOSNELLS', 'BALDIVIS', 'HAMILTON HILL', 'STIRLING', 'BAYSWATER', 'ORANA', 'PARKWOOD', 'KINROSS', 'WESTMINSTER', 'RIVERTON', 'AUSTRALIND', 'EMBLETON', 'DENMARK', 'WAIKIKI', 'DARCH', 'MELVILLE', 'JOONDANNA', 'THORNLIE', 'YAKAMIA', 'HAMERSLEY', 'BANKSIA GROVE', 'SOUTH BUNBURY', 'PORT KENNEDY', 'SOUTH KALGOORLIE', 'COOGEE', 'KARAWARA', 'STRATTON', 'SOUTHERN RIVER', 'SOUTH GUILDFORD', 'MYAREE', 'EAST CANNINGTON', 'MIRA MAR', 'SEVILLE GROVE', 'AUBIN GROVE', 'COOLBINIA', 'SCARBOROUGH', 'JANE BROOK', 'LESCHENAULT', 'BEECHBORO', 'SHOALWATER', 'MINDARIE', 'WOODLANDS', 'CLARKSON', 'SECRET HARBOUR', 'INNALOO', 'SORRENTO', 'DIANELLA', 'QUEENS PARK', 'WEST BUSSELTON', 'KARRINYUP', 'ILUKA', 'MOUNT HAWTHORN', 'HILLMAN', 'KALAMUNDA', 'TUART HILL', 'HAMMOND PARK', 'COOLBELLUP', 'GREENWOOD', 'OSBORNE PARK', 'KARDINYA', 'WANNEROO', 'CITY BEACH', 'LESMURDIE', 'PIARA WATERS', 'SWAN VIEW', 'ORELIA', 'BENTLEY', 'FREMANTLE', 'MAIDA VALE', 'CAVERSHAM', 'MARTIN', 'SPENCER PARK', 'ALFRED COVE', 'WELLARD', 'EATON', 'GUILDFORD', 'BENNETT SPRINGS', 'HIGH WYCOMBE', 'WILSON', 'WANNANUP', 'PARMELIA', 'JOONDALUP', 'SAMSON', 'MOUNT LAWLEY', 'DOUBLEVIEW', 'DAGLISH', 'THE VINES', 'BANJUP', 'KELMSCOTT', 'ARMADALE', 'COOLOONGUP', 'HILLARYS', 'ROLEYSTONE', 'TRIGG', 'LANGFORD', 'RIVERVALE', 'BIBRA LAKE', 'MADELEY', 'CARRAMAR', 'KINGSLEY', 'KEWDALE', 'BASSENDEAN', 'MIDDLE SWAN', 'BECKENHAM', 'WEMBLEY DOWNS', 'BALCATTA', 'MOUNT CLAREMONT', 'ALEXANDER HEIGHTS', 'YOKINE', 'NORTH PERTH', 'WATTLE GROVE', 'MCKAIL', 'WANDI', 'PERTH', 'NORTHAM', 'EDEN HILL', 'WARNBRO', 'CANNINGTON', 'WHITE GUM VALLEY', 'O''CONNOR', 'DUNCRAIG', 'MAYLANDS', 'ATTADALE', 'MORLEY', 'COLLIE', 'CAREY PARK', 'EDGEWATER', 'GLEN IRIS', 'SOUTH FREMANTLE', 'BYFORD', 'MERRIWA', 'REDCLIFFE', 'CLOVERDALE', 'NOLLAMARA', 'BOULDER', 'KOONDOOLA', 'FORRESTFIELD', 'NORTH COOGEE', 'WITHERS', 'TAPPING', 'FERNDALE', 'LANDSDALE', 'QUINNS ROCKS', 'KIARA', 'MIDVALE', 'PORT DENISON', 'PALMYRA', 'WILLAGEE', 'SHELLEY', 'WAROONA', 'ST JAMES', 'EAST BUNBURY', 'BUNBURY', 'BEACONSFIELD', 'SINAGRA', 'HILTON', 'HUNTINGDALE'))
 OR (state = 'TAS' AND locality_name IN ('CAMDALE', 'HAWLEY BEACH', 'BRIGHTON', 'SHOREWELL PARK', 'WEST ULVERSTONE', 'COOEE', 'HOWRAH', 'OCEAN VISTA', 'PARKLANDS', 'BURNIE', 'LATROBE', 'SHEARWATER', 'EVANDALE', 'SCAMANDER', 'LONGFORD', 'PARK GROVE', 'ULVERSTONE', 'CHIGWELL', 'TRANMERE', 'PERTH', 'NEW NORFOLK', 'CLAREMONT', 'BERRIEDALE', 'SANDY BAY', 'MARGATE', 'GRINDELWALD', 'DEVONPORT', 'LEGANA', 'HUONVILLE', 'PENGUIN', 'PORT SORELL'))
 OR (state = 'NT' AND locality_name IN ('BRAITLING', 'SADADEEN', 'EAST SIDE', 'MOUNT JOHNS', 'DESERT SPRINGS', 'LARAPINTA', 'ALICE SPRINGS', 'ROSS', 'THE GAP', 'ARALUEN', 'ILPARPA', 'GILLEN', 'CICCONE'))
 OR (state = 'ACT' AND locality_name IN ('BANKS', 'REID', 'KINGSTON', 'CASEY', 'CHARNWOOD', 'HUME', 'DUNLOP', 'MONASH', 'LYNEHAM', 'CONDER', 'AINSLIE', 'CAMPBELL', 'GORDON', 'BRADDON', 'TURNER', 'YARRALUMLA', 'DICKSON', 'MACGREGOR'))

which takes a long time (2+ minutes for me) to execute.

Current progress is 6246082 / 14660282 (42.6%)

lyricnz commented 1 year ago

Would be nice to have a state-by-state breakdown.

lyricnz commented 1 year ago

VS total GNAF DB counts

   ACT: 51141/236893 (21.6%)
   NSW: 1872217/4525195 (41.4%)
    NT: 12703/109394 (11.6%)
    OT: 0/3595 (0.0%)
   QLD: 1509373/3032216 (49.8%)
    SA: 399762/1103252 (36.2%)
   TAS: 68646/344658 (19.9%)
   VIC: 1445987/3818152 (37.9%)
    WA: 886253/1486927 (59.6%)
 Total: 6246082/14660282 (42.6%)
lyricnz commented 1 year ago

Added code in https://github.com/LukePrior/nbn-upgrade-map/pull/127 (not currently called - takes ~2 minutes)

Invoke with

    with open("results/suburbs.json", "r", encoding="utf-8") as file:
        suburb_list = json.load(file)
        db.get_and_log_progress(suburb_list["states"])
LukePrior commented 1 year ago

Looks good will have a think about how to make this info more accessible as don't want it running every time.

Maybe spin up a separate job to generate stats...

lyricnz commented 1 year ago

Yeah. I couldn't figure out how to optimise the query.