#!/usr/bin/env python3 """Add developers only when the quote is present in a fetched file.""" import re import sqlite3 from pathlib import Path ROOT = Path("/Users/hattr/Documents/wa-land-developers") TXT = ROOT / "raw" / "expand" / "txt" HTML = (ROOT / "raw" / "expand" / "industry-issues-18-06-2026").read_text(errors="replace") RET = "2026-10-19" DB = ROOT / "developers.sqlite" FILES = { "2018": TXT / "UDIA-WA-2018-Awards-for-Excellence-Nominated-Projects.compressed-1.txt", "2014": TXT / "2014_UDIA_Annual_Report.txt", "2022": TXT / "UDIA-WA-2022-Awards-for-Excellence-Nominated-Projects-Booklet.txt", "enviro": TXT / "enviro.txt", "blog": None, } BLOBS = {} for key, path in FILES.items(): if key == "blog": text = re.sub(r"<[^>]+>", " ", HTML) else: text = path.read_text(errors="replace") BLOBS[key] = re.sub(r"\s+", " ", text) URLS = { "2018": "https://www.udiawa.com.au/wp-content/uploads/2018/08/UDIA-WA-2018-Awards-for-Excellence-Nominated-Projects.compressed-1.pdf", "2014": "https://www.udiawa.com.au/wp-content/uploads/2017/11/2014_UDIA_Annual_Report.pdf", "2022": "https://www.udiawa.com.au/wp-content/uploads/2022/07/UDIA-WA-2022-Awards-for-Excellence-Nominated-Projects-Booklet.pdf", "enviro": "https://www.udiawa.com.au/wp-content/uploads/2020/06/UDIA-WA-02-JUN-Webinar-EnviroDevelopment-presentation-slides.pdf", "blog": "https://www.udiawa.com.au/blog/industry-issues-18-06-2026/", } TITLES = { "2018": "UDIA WA 2018 Awards for Excellence nominated projects", "2014": "UDIA WA Annual Report 2013/14", "2022": "UDIA WA 2022 Awards for Excellence nominated projects", "enviro": "UDIA WA EnviroDevelopment certified projects, June 2020", "blog": "UDIA WA Industry Issues 18 June 2026", } # trading, kind, locality, region, scale, src_key, evidence, extra_keys ROWS = [ ("Gary Dempsey Developments", "infill developer", "North Fremantle", "swis", "dwellings=unknown. Named as the company for Taskers, North Fremantle. Dwelling count is not in the booklet line.", "2018", "Taskers Gary Dempsey Developments North Fremantle WA", []), ("QUBE Property Group", "land developer", "Hammond Park", "swis", "dwellings=unknown. Named as the company for Hammond Grove Estate. Lot count is not in the booklet line.", "2018", "Hammond Grove Estate QUBE Property Group Hammond Park WA", []), ("NicheLiving", "residential developer", "Madeley", "swis", "dwellings=unknown. Named as the company for NicheLiving Madeley in the affordable-development category. Lot count is not stated.", "2018", "NicheLiving Madeley NicheLiving Madeley WA", []), ("National Lifestyle Villages", "land-lease community developer", "Baldivis", "swis", "dwellings=unknown. Named as the company for Tuart Lakes Lifestyle Village. Village size is not in the booklet line.", "2018", "Tuart Lakes Lifestyle Village National Lifestyle Villages Baldivis WA", []), ("The Bethanie Group", "seniors housing developer", "Menora", "swis", "dwellings=unknown. Named as the company for Bethanie on the Park serviced apartments. Bed count is not stated.", "2018", "Bethanie on the Park The Bethanie Group Serviced Apartments Menora WA", []), ("Modan", "development entrant with BGC", "Cloverdale", "swis", "dwellings=unknown. Named with BGC as the company for 119 Gabriel. Dwelling count is not stated.", "2018", "119 Gabriel BGC & Modan Cloverdale WA", []), ("Richard Noble", "land developer", "Hammond Park", "swis", "dwellings=unknown. Named as the developer of Vivente and Cygnia Cove, and with Gold Estates for Constellar, about 3,000 residents.", "blog", "Constellar at Cardup Junction by Richard Noble & Company, in partnership with Gold Estates, has been certified under all six elements of UDIA’s EnviroDevelopment program.", ["enviro", "2022"]), ("Gold Estates", "land developer", "Cardup", "swis", "dwellings=unknown. Constellar is described as eventually home to around 3,000 residents. That is under the 1,600-home count if counted as dwellings, and the page states residents, not dwellings.", "blog", "Constellar is a new masterplanned community in Perth’s south east that will eventually be home to around 3,000 residents", ["2022"]), ("Tamala Park Regional Council", "government land agency", "Clarkson", "swis", "dwellings=unknown. Named as the developer of Catalina Beach and Grove. Lot count is not in the certification table.", "enviro", "Catalina Beach Tamala Park Regional Council", []), ("Handle Property Group", "land developer", "South Guildford", "swis", "dwellings=unknown. Named as the developer of Rosehill Waters. Lot count is not in the certification table.", "enviro", "Rosehill Waters Handle Property Group", []), ("Hanson Property", "land developer", "Vasse", "swis", "dwellings=unknown. Named with The Perron Group as the developer of the Vasse Dawson residential estate. This is not the concrete company Hanson Construction Materials.", "enviro", "Hanson Property & The Perron Group", []), ("Frasers", "land developer", "North Coogee", "swis", "dwellings=unknown. Named as the developer of Port Coogee in the certification table. Lot count is not stated there.", "enviro", "Port Coogee Frasers", []), ("Australand", "land developer", "North Coogee", "swis", "dwellings=unknown. The 2013/14 annual report lists Port Coogee, North Coogee, against Australand. A later certification table names Frasers for the same project.", "2014", "Port Coogee, North Coogee: Australand", []), ("Capricorn Village JV", "land developer", "Yanchep", "swis", "dwellings=unknown. Listed as the developer of Capricorn at Yanchep. Lot count is not in that line.", "2014", "Capricorn at Yanchep: Capricorn Village JV", []), ("St Ives", "retirement housing developer", "Carine", "swis", "dwellings=unknown. Named with Cedar Woods and LandCorp for Carine Rise. Lot count is not in that line.", "2014", "Carine Rise: Cedar Woods, St Ives and LandCorp", []), ("Rio Tinto Iron Ore", "town housing developer", "Wickham", "horizon", "dwellings=unknown. Listed as the developer of Wickham South, a certified residential estate. This is a town housing project in the Horizon Power area, not an off-grid mine power station. Connection size is not stated.", "2014", "Wickham South: Rio Tinto Iron Ore", []), ("M/Group", "land developer", "Beeliar", "swis", "dwellings=unknown. Named as the company for Atop, Beeliar. Lot count is not in the booklet line.", "2022", "ATOP, BEELIAR M/GROUP BEELIAR", []), ("Locus Development Group", "infill developer", "Wembley", "swis", "dwellings=unknown. Named as the company for Altimo Wembley. The category is projects with fewer than 50 dwellings.", "2022", "ALTIMO WEMBLEY LOCUS DEVELOPMENT GROUP WEMBLEY", []), ("Gold Estates Holdings", "land developer", "Hammond Park", "swis", "dwellings=unknown. Named with Richard Noble & Company for Vivente in Hammond Park.", "2022", "VIVENTE IN HAMMOND PARK GOLD ESTATES HOLDINGS AND RICHARD NOBLE & COMPANY", ["blog"]), ("Curtin Heritage Living", "seniors housing developer", "Cottesloe", "swis", "dwellings=unknown. Named as the company for the Cottesloe redevelopment. Dwelling count is not in the booklet line.", "2022", "CURTIN HERITAGE LIVING’S COTTESLOE REDEVELOPMENT CURTIN HERITAGE LIVING COTTESLOE", []), ("Rosewood Care Group Inc.", "aged-care developer", "West Perth", "swis", "dwellings=unknown. Named as the company for Rosewood West Perth. Bed count is not stated.", "2022", "ROSEWOOD WEST PERTH ROSEWOOD CARE GROUP INC. WEST PERTH", []), ("Lester Group", "seniors housing developer", "Australind", "swis", "dwellings=unknown. Named with Edenlife for Edenlife Australind, the 2021 seniors-living winner. Village size is not in that line.", "2022", "Edenlife Australind by Lester Group & Edenlife", []), ("Edenlife", "seniors housing developer", "Australind", "swis", "dwellings=unknown. Named with Lester Group for Edenlife Australind. Village size is not in that line.", "2022", "Edenlife Australind by Lester Group & Edenlife", []), ("New Leaf Homes", "residential developer", "Como", "swis", "dwellings=unknown. Named with Hillam Architects as the company for Alocasia Residences. Hillam is the architect and is not added as a developer. Dwelling count is not stated. The category is under 50 dwellings.", "2022", "ALOCASIA RESIDENCES HILLAM ARCHITECTS, NEW LEAF HOMES COMO", []), ("City of Wanneroo", "local government", "Yanchep", "swis", "dwellings=unknown. Named as the company for the Yanchep Strategic Metropolitan Redevelopment Project. Lot count is not in the booklet line.", "2018", "Yanchep Strategic Metropolitan Redevelopment Project City of Wanneroo Yanchep WA", []), ] # Extra source URLs for developers already in the table. Quote must be in the file. EXTRAS = [ ("Lendlease", "2014", "Alkimos Beach: Lend Lease and LandCorp"), ("Lendlease", "2022", "PICASSO PARK, ALKIMOS VISTA LENDLEASE & DEVELOPMENTWA"), ("Hesperia", "2022", "RIVERMARK HESPERIA VIVEASH"), ("Peet Limited", "2014", "Shorehaven at Alkimos: Peet Limited"), ("Mirvac", "2014", "Highland Reserve, Jane Brook: Mirvac (WA) Pty Ltd"), ] def main(): con = sqlite3.connect(DB) # Clear ABNs that the public lookup cannot pin to one developer company. cleared = [ "Willing", # family trust, holdings, and a building company all match the word "Hesperia", # several WA companies use the name; the pages do not say which "Aegis Health", # unit trust, operating trust, and a staff company ] for name in cleared: con.execute("UPDATE developers SET abn=NULL, legal_name=NULL WHERE trading_name=?", (name,)) con.execute( "DELETE FROM source_urls WHERE developer_id IN (SELECT id FROM developers WHERE trading_name=?) AND url LIKE '%abr.business.gov.au%'", (name,), ) print("cleared ABN", name) # Point brand ABNs at the entity whose name matches the words on the page. fixes = { "Blackburne": ("61100736472", "BLACKBURNE PROPERTY GROUP PTY LTD"), "Finbar": ("97009113473", "FINBAR GROUP LIMITED"), "Far East Consortium": ("76062878862", "FAR EAST CONSORTIUM PROPERTIES PTY LIMITED"), "Nicheliving": ("34151989182", "NICHELIVING DEVELOPMENTS"), } for trading, (abn, legal) in fixes.items(): con.execute("UPDATE developers SET abn=?, legal_name=? WHERE trading_name=?", (abn, legal, trading)) print("retargeted", trading, abn, legal) added = 0 for trading, kind, locality, region, scale, src, evidence, extras in ROWS: blob = BLOBS[src] if evidence not in blob: print("QUOTE MISS", trading) print(" ", evidence[:120]) continue exists = con.execute( "SELECT id FROM developers WHERE lower(trading_name)=lower(?) OR lower(legal_name)=lower(?)", (trading, trading), ).fetchone() if exists: print("already", trading) continue cur = con.execute( """INSERT INTO developers (tier, legal_name, trading_name, abn, entity_kind, contact_name, contact_role, public_phone, public_email, website, locality, region_tag, scale_note, source_url, source_title, evidence, retrieved_at, duplicate_of) VALUES ('A', NULL, ?, NULL, ?, NULL, NULL, NULL, NULL, NULL, ?, ?, ?, ?, ?, ?, ?, NULL)""", (trading, kind, locality, region, scale, URLS[src], TITLES[src], evidence, RET), ) did = cur.lastrowid seen = set() for key in [src] + extras: ev = evidence if key == src else None if key != src: # extra url only if some identifying token is on that file token = trading.split()[0] if token.lower() not in BLOBS[key].lower() and trading.lower() not in BLOBS[key].lower(): continue u = URLS[key] if u in seen: continue seen.add(u) con.execute("INSERT INTO source_urls (developer_id, url, retrieved_at) VALUES (?,?,?)", (did, u, RET)) added += 1 print("ADDED", trading) for trading, src, evidence in EXTRAS: if evidence not in BLOBS[src]: print("EXTRA QUOTE MISS", trading, evidence[:80]) continue row = con.execute("SELECT id FROM developers WHERE trading_name=?", (trading,)).fetchone() if not row: print("EXTRA no row", trading) continue u = URLS[src] if not con.execute("SELECT 1 FROM source_urls WHERE developer_id=? AND url=?", (row[0], u)).fetchone(): con.execute("INSERT INTO source_urls (developer_id, url, retrieved_at) VALUES (?,?,?)", (row[0], u, RET)) print("extra url", trading, src) con.commit() print("added", added) print("A", con.execute("select count(*) from developers where tier='A'").fetchone()[0]) if __name__ == "__main__": main()