
#pragma nodebook off
#Use nodebook for better reproducibility https://github.com/uoa-eResearch/nodebook
%reload_ext nodebook.ipython
%nodebook disk phase1


# load libraries
import geopandas as gpd # vector data
import pandas as pd # tabular data, loading CSVs
import numpy as np # numeric data
from util import *
import matplotlib # plotting
import contextily as ctx # Used for contextual basemaps
from scipy.spatial import cKDTree
from matplotlib_scalebar.scalebar import ScaleBar # scalebar for plot
import matplotlib.pyplot as plt # plotting
from tqdm.auto import tqdm # progress bars
tqdm.pandas()
import requests
import requests_cache
requests_cache.install_cache('requests_cache')
import json
from shapely.geometry import Point, shape, LineString, MultiLineString, GeometryCollection, MultiPoint, Polygon  # creating points
plt.rcParams['figure.figsize'] = (20, 20)
pd.set_option('max_columns', None)
pd.set_option('max_rows', 300)


%%time
df = pd.read_excel("restricted/BCs_issued_by_AUP_TLADCs_2021DEC.xlsx")
df = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.X_Coordinate, df.Y_Coordinate), crs=4326)

CPU times: user 44.7 s, sys: 277 ms, total: 45 s
Wall time: 45.1 s

df


df["Ranged_Address_indicator"] = df.ADDRESS_1.str.contains('\d\w?\s?-', regex=True, na=False)
df["Ranged_Address_indicator"].value_counts()

False    211197
True       6665
Name: Ranged_Address_indicator, dtype: int64


%%time
# get number and name of street (but not 'road', 'street', 'place' etc)
# this can be used to match addresses with building consents

def number_name_bc(x):
    """extract street number and first complete word of the street name from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3]]).lower()
        return ' '.join(joined_address.split(' ')[:2])

def full_address_bc(x):
    """extract full address from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3] if not str(x[f'ADDRESS_{i}']) == 'nan']).lower()
        return joined_address

df['number_name'] = df.apply(number_name_bc, axis=1)
df['full_address'] = df.apply(full_address_bc, axis=1)

CPU times: user 20.1 s, sys: 368 ms, total: 20.5 s
Wall time: 20.5 s


any(df.full_address.str.contains("[āēīōū]"))

False


parcels = gpd.read_file('input/lds-nz-primary-parcels-FGDB.zip!nz-primary-parcels.gdb').to_crs(2193)
parcels = parcels.set_index("id")
parcels


addresses = gpd.read_file('input/lds-nz-addresses-pilot-FGDB.zip!nz-addresses-pilot.gdb').to_crs(2193)
addresses


def number_name_addresses(x):
    return ' '.join(x.split(' ')[:2]).lower()
addresses['address_number_name'] = addresses.full_address_ascii.apply(number_name_addresses)
addresses


%%time
parcels_addressed = gpd.sjoin(parcels, addresses)

CPU times: user 50.4 s, sys: 3.44 s, total: 53.8 s
Wall time: 54.2 s


%%time
parcels["addresses"] = parcel_address_number_name = parcels_addressed.groupby('id')['address_number_name'].apply(list)
display(parcels["addresses"])
parcels["full_addresses"] = parcels_addressed.groupby('id')["full_address"].apply(list)
display(parcels["full_addresses"])

id
4918991    [3/12 college, 1/12 college, 2/12 college, 12 ...
4919914                                         [15 abraham]
4926734    [3/20 simkin, 1/20 simkin, 2/20 simkin, 20 sim...
4926945    [3 abraham, 4/3 abraham, 3/3 abraham, 2/3 abra...
4929034                                         [49 caulton]
                                 ...                        
5227472                                        [112z bairds]
5242816                                                  NaN
7520053                                        [9 lovegrove]
7520049                                       [15 lovegrove]
4733845                                            [7 otara]
Name: addresses, Length: 547903, dtype: object

id
4918991    [3/12 College Road, Saint Johns, Auckland, 1/1...
4919914            [15 Abraham Place, Saint Johns, Auckland]
4926734    [3/20 Simkin Avenue, Saint Johns, Auckland, 1/...
4926945    [3 Abraham Place, Saint Johns, Auckland, 4/3 A...
4929034           [49 Caulton Street, Saint Johns, Auckland]
                                 ...                        
5227472                  [112Z Bairds Road, Ōtara, Auckland]
5242816                                                  NaN
7520053              [9 Lovegrove Crescent, Ōtara, Auckland]
7520049             [15 Lovegrove Crescent, Ōtara, Auckland]
4733845                      [7 Otara Road, Ōtara, Auckland]
Name: full_addresses, Length: 547903, dtype: object

CPU times: user 33 s, sys: 2.78 s, total: 35.8 s
Wall time: 36.1 s


%%time
df = gpd.sjoin(df.to_crs(2193), parcels[["addresses","full_addresses","geometry"]], how="left")

CPU times: user 31.5 s, sys: 1.51 s, total: 33 s
Wall time: 33.2 s


print(sum(df.number_name.str.contains("mt")),
sum(df.number_name.str.contains("mount")),
sum(addresses.address_number_name.str.contains("mt")),
sum(addresses.address_number_name.str.contains("mount")))

690 639 135 5840


df = df.rename(columns={"index_right": "LINZ_parcel_ID"})
df.LINZ_parcel_ID = df.LINZ_parcel_ID.astype("Int64")
df


assert df.index.is_unique


def check_match(row):
    if row.number_name in row.addresses:
        return 1
    return np.nan
match = df[~pd.isna(df.number_name) & ~pd.isna(df.addresses)].apply(check_match, axis=1)
df["LINZ_MATCH_CODE"] = match
df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    136630
NaN     81232
Name: LINZ_MATCH_CODE, dtype: int64


parcel_tree = cKDTree(list(parcels.centroid.apply(lambda x: (x.x, x.y))))
parcel_tree

<scipy.spatial.ckdtree.cKDTree at 0x7f5c86bb1f90>


sample = df[pd.isna(df["LINZ_MATCH_CODE"]) & df.number_name.isin(addresses.address_number_name)].head(1)
sample


indices_in_range = parcel_tree.query_ball_point([sample.centroid.iloc[0].x, sample.centroid.iloc[0].y], 1250)
ax = sample.to_crs(epsg=3857).plot(color="red", alpha=1)
sample.buffer(1250).to_crs(epsg=3857).plot(ax=ax, alpha=.5, color="green")
potential_matches = parcels.iloc[indices_in_range]
potential_matches.to_crs(epsg=3857).plot(ax=ax, alpha=.5, edgecolor="black")
potential_matches[potential_matches.addresses.str.contains(sample.number_name.iloc[0], na=False, regex=False)].to_crs(epsg=3857).plot(ax=ax, color="red")
ctx.add_basemap(ax, source=ctx.providers.Esri.WorldImagery)


def find_match_in_range(row):
    indices_in_range = parcel_tree.query_ball_point([row.geometry.x, row.geometry.y], 1250)
    potential_matches = parcels.iloc[indices_in_range]
    match = potential_matches[potential_matches.addresses.str.contains(row.number_name, na=False, regex=False)]
    if len(match):
        row.LINZ_parcel_ID = match.index[0]
        row.addresses = match.addresses.iloc[0]
        row.full_addresses = match.full_addresses.iloc[0]
        row.LINZ_MATCH_CODE = 2
    return row
matches = df[
    ~pd.isna(df.number_name) &
    pd.isna(df["LINZ_MATCH_CODE"]) &
    df.number_name.isin(addresses.address_number_name)].progress_apply(find_match_in_range, axis=1)


matches


df.update(matches)
df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    136630
NaN     45094
2.0     36138
Name: LINZ_MATCH_CODE, dtype: int64


df[pd.isna(df["LINZ_MATCH_CODE"]) & df.number_name.isin(addresses.address_number_name)]


%%time
number_name_lookup = {}
for parcel_id, addresses in parcels.addresses[~pd.isna(parcels.addresses)].iteritems():
    for address in addresses:
        if address not in number_name_lookup:
            number_name_lookup[address] = []
        number_name_lookup[address].append(parcel_id)

CPU times: user 8.85 s, sys: 621 ms, total: 9.47 s
Wall time: 9.53 s


%%time
def find_text_match(row):
    matching_ids = number_name_lookup.get(row.number_name)
    if len(matching_ids):
        match = parcels.loc[matching_ids]
        closest_parcel_id = match.distance(row.geometry).idxmin()
        row.LINZ_parcel_ID = closest_parcel_id
        row.addresses = match.addresses[closest_parcel_id]
        row.full_addresses = match.full_addresses[closest_parcel_id]
        row.LINZ_MATCH_CODE = 3
    return row
matches = df[pd.isna(df["LINZ_MATCH_CODE"]) & df.number_name.isin(number_name_lookup.keys())].progress_apply(find_text_match, axis=1)
df.update(matches)
df.LINZ_MATCH_CODE.value_counts(dropna=False)

CPU times: user 1min 23s, sys: 2.37 s, total: 1min 25s
Wall time: 1min 25s

1.0    136630
NaN     39293
2.0     36138
3.0      5801
Name: LINZ_MATCH_CODE, dtype: int64


df.Ranged_Address_indicator = df.Ranged_Address_indicator.astype(bool)


maybe_subdivision = df[df.number_name.str.contains("/") & pd.isna(df.LINZ_MATCH_CODE) & ~df.Ranged_Address_indicator]
maybe_subdivision


maybe_subdivision.number_name = maybe_subdivision.number_name.str.split("/").str[-1]


def check_match(row):
    if row.number_name in row.addresses:
        return 1
    return np.nan
match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
maybe_subdivision.LINZ_MATCH_CODE = match
match.value_counts(dropna=False)

1.0    730
NaN    692
dtype: int64


matches = maybe_subdivision[
    pd.isna(match) &
    maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_match_in_range, axis=1)


matches


maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    730
NaN    644
2.0    223
Name: LINZ_MATCH_CODE, dtype: int64


matches = maybe_subdivision[pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_text_match, axis=1)
maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    730
NaN    497
2.0    223
3.0    147
Name: LINZ_MATCH_CODE, dtype: int64


maybe_subdivision


maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
maybe_subdivision.LINZ_MATCH_CODE.value_counts()

4.0    730
5.0    223
6.0    147
Name: LINZ_MATCH_CODE, dtype: int64


df.update(maybe_subdivision)


maybe_subdivision = df[
    df.number_name.str.contains("[0-9][a-zA-Z] ") &
    pd.isna(df.LINZ_MATCH_CODE) &
    (df.Ranged_Address_indicator == False)
]
maybe_subdivision


def strip_char(s):
    number, name = s.split()
    number = "".join([c for c in number if c.isnumeric()])
    return number + " " + name
maybe_subdivision.number_name = maybe_subdivision.number_name.apply(strip_char)


def check_match(row):
    if row.number_name in row.addresses:
        return 1
    return np.nan
match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
maybe_subdivision.LINZ_MATCH_CODE = match
match.value_counts(dropna=False)

NaN    970
1.0    726
dtype: int64


matches = maybe_subdivision[
    pd.isna(match) &
    maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_match_in_range, axis=1)


maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

NaN    809
1.0    726
2.0    505
Name: LINZ_MATCH_CODE, dtype: int64


matches = maybe_subdivision[pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())].progress_apply(find_text_match, axis=1)
maybe_subdivision.update(matches)
maybe_subdivision.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0    726
2.0    505
NaN    453
3.0    356
Name: LINZ_MATCH_CODE, dtype: int64


maybe_subdivision


maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
maybe_subdivision.LINZ_MATCH_CODE.value_counts()

4.0    726
5.0    505
6.0    356
Name: LINZ_MATCH_CODE, dtype: int64


df.update(maybe_subdivision)


%%time
distances = df[~pd.isna(df.LINZ_parcel_ID)].distance(parcels.loc[df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]], align=False)
distances

CPU times: user 11.4 s, sys: 490 ms, total: 11.9 s
Wall time: 12 s

0         337.062690
1           0.000000
2           0.000000
3           0.000000
4           0.000000
             ...    
217857      0.000000
217858      0.000000
217859     34.223348
217860      0.000000
217861      0.000000
Length: 217690, dtype: float64


distances[distances>0].describe()

count     43170.000000
mean       2464.800447
std        8100.970251
min           0.000022
25%           9.491709
50%          28.062859
75%         196.676894
max      100594.158222
dtype: float64


df["distance_to_parcel"] = distances


df[["number_name", "addresses"]]


def check_street_match(row):
    if row.number_name and len(row.number_name)>1:
        street = row.number_name.split()[-1]
        if street in str(row.addresses):
            return 7
    return np.nan
street_matches = df[pd.isna(df.LINZ_MATCH_CODE)].progress_apply(check_street_match, axis=1)
display(street_matches.value_counts(dropna=False))
df.LINZ_MATCH_CODE.update(street_matches)

NaN    25924
7.0    10682
dtype: int64


df.LINZ_MATCH_CODE[~pd.isna(df.LINZ_parcel_ID) & pd.isna(df.LINZ_MATCH_CODE)] = 8

<string>:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


df.LINZ_MATCH_CODE = df.LINZ_MATCH_CODE.fillna(9)
df.LINZ_MATCH_CODE.value_counts()

1.0    136630
2.0     36138
8.0     25808
7.0     10682
3.0      5801
4.0      1456
5.0       728
6.0       503
9.0       116
Name: LINZ_MATCH_CODE, dtype: int64


df.LINZ_MATCH_CODE[df.Ranged_Address_indicator].value_counts()

7.0    3456
8.0    2255
1.0     461
2.0     389
3.0     103
9.0       1
Name: LINZ_MATCH_CODE, dtype: int64


ranged = df[df.Ranged_Address_indicator & (df.LINZ_MATCH_CODE > 2)]
ranged


def parse_int(value):
    return int(''.join(filter(lambda c: c.isdigit(), value)))

def char_range(c1, c2):
    """Generates the characters from `c1` to `c2`, inclusive."""
    for c in range(ord(c1), ord(c2)+1):
        yield chr(c)

def range_expand(r):
    """given a range r, like '20-23', expand the range on one side of the road, e.g. ['20', '21', '22', '23']"""
    if '/' in r:
        a, b = r.split('/')
        if "-" in a:
            r = a
            suffix = "/" + b
        else:
            r = b
            suffix = "/" + a
    else:
        suffix = ''
    r1, r2 = r.split('-')
    if r2 == "sep" or r2 == "":
        return [r1]
    if r2.isalpha():
        return [r1.replace(r1[-1], char) for char in char_range(r1[-1], r2)]
    r1 = parse_int(r1)
    r2 = parse_int(r2)
    if suffix:
        step = 1
    else:
        step = 2
    if r1 < r2:
        return [str(i) + suffix for i in range(r1, r2 + 1, step)]
    else:
        return [str(i) + suffix for i in range(r2, r1 + 1, step)]

corrections = {
    "1/ 231-235": "231-235 hinemoa",
    "2/ 16-18": "16-18 montgomery",
    "2/ 2-8": "2-8 matipo",
    "r 35-41": "35-41 birkenhead",
    "r 31-35": "31-35 cresta",
    "27 -": "27-31 victoria",
    "1 &": "2-1/96 bernleigh",
    "55121-23 morrison": "21-23 morrison",
    "1954-60 great": "1954-1960 great",
    "3-9 ": "3-9 faldo",
    "22 -": "22-26 rawalpindi",
    "36 -": "36-38 border",
    "57a -": "57-61 woodward",
    "102 -": "102-104 may"
}
ranged.number_name = ranged.number_name.replace(corrections)

for r in ranged.number_name:
    e = None
    try:
        number, name = r.split()
        range_expanded = range_expand(number)
        assert len(range_expanded) > 0 and len(range_expanded) < 1000
    except Exception as e:
        print(f"Parsing of {r} failed: {e}")
        display(ranged[ranged.number_name == r])


for test_case in ['20-28', '1-5/32a', '53a-c']:
    print(f"{test_case}: {range_expand(test_case)}")

20-28: ['20', '22', '24', '26', '28']
1-5/32a: ['1/32a', '2/32a', '3/32a', '4/32a', '5/32a']
53a-c: ['53a', '53b', '53c']


%%time
expanded_ranged_addresses = []
for i, sample in tqdm(ranged.iterrows(), total=len(ranged)):
    # Expand this ranged address into it's own dataframe, with each number as it's own row
    number, name = sample.number_name.split()
    rows = []
    for n in range_expand(number):
        row = sample.copy()
        row.number_name = n + " " + name
        row["unique_id"] = f"{row.OBS}_{n}"
        rows.append(row)
    sample = pd.DataFrame(rows).set_index("unique_id")
    if not sample.index.is_unique:
        display(sample)
        raise

    # Step 1
    def check_match(row):
        if row.number_name in row.addresses:
            return 1
        return np.nan
    match = sample[~pd.isna(sample.number_name) & ~pd.isna(sample.addresses)].apply(check_match, axis=1)
    if len(match):
        sample["LINZ_MATCH_CODE"] = match
    else:
        sample["LINZ_MATCH_CODE"] = np.nan

    #However, we set r in step 2 to a larger number in order to do a wider search.

    def find_match_in_range(row):
        if str(row.geometry) == "nan":
            return row
        indices_in_range = parcel_tree.query_ball_point([row.geometry.x, row.geometry.y], 2000)
        potential_matches = parcels.iloc[indices_in_range]
        match = potential_matches[potential_matches.addresses.str.contains(row.number_name, na=False, regex=False)]
        if len(match):
            row.LINZ_parcel_ID = match.index[0]
            row.addresses = match.addresses.iloc[0]
            row.full_addresses = match.full_addresses.iloc[0]
            row.LINZ_MATCH_CODE = 2
        return row
    matches = sample[
        ~pd.isna(sample.number_name) &
        pd.isna(sample["LINZ_MATCH_CODE"])].apply(find_match_in_range, axis=1)
    sample.update(matches)

    # If no matches are found at step 2, but at least one match in the ranged address is found at step 1 or step 2, stop and set LINZ_match_code to 10
    # This avoids matching parcels that are far away from the rest of the group
    if all(pd.isna(sample.LINZ_MATCH_CODE)): # all na here means no matches in step 1 or 2
        # Only if there is no match within the ranged addresses at either step 1 or step 2 do we proceed to step 3. 
        matches = sample[pd.isna(sample["LINZ_MATCH_CODE"]) & sample.number_name.isin(number_name_lookup.keys())].apply(find_text_match, axis=1)
        sample.update(matches)
        maybe_subdivision = sample[sample.number_name.str.contains("/") & pd.isna(sample.LINZ_MATCH_CODE)]
        if len(maybe_subdivision):
            maybe_subdivision.number_name = maybe_subdivision.number_name.str.split("/").str[-1]
            # step 1
            match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
            if len(match):
                maybe_subdivision["LINZ_MATCH_CODE"] = match
            else:
                maybe_subdivision["LINZ_MATCH_CODE"] = np.nan
            # step 2
            matches = maybe_subdivision[
                pd.isna(match) &
                maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_match_in_range, axis=1)
            maybe_subdivision.update(matches)
            # step 3
            matches = maybe_subdivision[
                pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_text_match, axis=1)
            maybe_subdivision.update(matches)
            maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
            sample.update(maybe_subdivision)
        maybe_subdivision = sample[
            sample.number_name.str.contains("[0-9][a-zA-Z] ") &
            pd.isna(sample.LINZ_MATCH_CODE)
        ]
        if len(maybe_subdivision):
            maybe_subdivision.number_name = maybe_subdivision.number_name.apply(strip_char)
            # step 1
            match = maybe_subdivision[~pd.isna(maybe_subdivision.addresses)].apply(check_match, axis=1)
            if len(match):
                maybe_subdivision["LINZ_MATCH_CODE"] = match
            else:
                maybe_subdivision["LINZ_MATCH_CODE"] = np.nan
            # step 2
            matches = maybe_subdivision[
                pd.isna(match) &
                maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_match_in_range, axis=1)
            maybe_subdivision.update(matches)
            # step 3
            matches = maybe_subdivision[
                pd.isna(maybe_subdivision["LINZ_MATCH_CODE"]) & maybe_subdivision.number_name.isin(number_name_lookup.keys())
            ].apply(find_text_match, axis=1)
            maybe_subdivision.update(matches)
            maybe_subdivision.LINZ_MATCH_CODE = maybe_subdivision.LINZ_MATCH_CODE.replace({1: 4, 2: 5, 3: 6})
            sample.update(maybe_subdivision)

        if all(pd.isna(sample.LINZ_MATCH_CODE)):
            street_matches = sample[pd.isna(sample.LINZ_MATCH_CODE)].apply(check_street_match, axis=1)
            sample.LINZ_MATCH_CODE.update(street_matches)
            sample.loc[~pd.isna(sample.LINZ_parcel_ID) & pd.isna(sample.LINZ_MATCH_CODE), "LINZ_MATCH_CODE"] = 8
            sample.LINZ_MATCH_CODE = sample.LINZ_MATCH_CODE.fillna(9)
        else:
            sample.LINZ_MATCH_CODE = sample.LINZ_MATCH_CODE.fillna(10)
    else:
        sample.LINZ_MATCH_CODE = sample.LINZ_MATCH_CODE.fillna(10)
    #We include an additional flag for each ranged address that indicates the best (i.e. lowest) LINZ_MATCH_CODE for that range.
    #E.g. for a ranged address with five addresses, if at least one of them had LINZ_MATCH_CODE = 2,
    #and all others were LINZ_MATCH_CODE > 2, then the best LINZ_MATCH_CODE would be 2. This is call LINZ_2ND_MATCH_CODE below.
    # Each address (in the ranged address set) is assigned its own unique LINZ parcel.
    # Each parcel is then assigned the information given in 1 through 11 below.
    # Because the parcels share the same consent ID, we can tell that the different parcels pertain to the same consent, thereby avoiding double counting. 
    sample["LINZ_2ND_MATCH_CODE"] = sample.LINZ_MATCH_CODE.min()
    expanded_ranged_addresses.append(sample)

/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
<string>:85: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
<string>:58: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
<string>:58: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
<string>:83: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
<string>:83: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:7511: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[col] = expressions.where(mask, this, that)
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py:5516: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self[name] = value
<string>:58: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

CPU times: user 21min 3s, sys: 13.1 s, total: 21min 16s
Wall time: 21min 10s


df = df.drop(ranged.index)
df = pd.concat([df] + expanded_ranged_addresses)
df


df.LINZ_MATCH_CODE[df.Ranged_Address_indicator].value_counts(dropna=False)

2.0     15734
10.0    13520
1.0      4339
3.0      2800
8.0      2325
7.0       810
5.0       125
4.0        45
6.0        35
Name: LINZ_MATCH_CODE, dtype: int64


df.LINZ_2ND_MATCH_CODE[df.Ranged_Address_indicator].value_counts(dropna=False)

2.0    16254
1.0    15415
3.0     3882
8.0     2325
NaN      850
7.0      810
5.0      123
4.0       43
6.0       31
Name: LINZ_2ND_MATCH_CODE, dtype: int64


df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0     140508
2.0      51483
8.0      25878
10.0     13520
3.0       8498
7.0       8036
4.0       1501
5.0        853
6.0        538
9.0        115
Name: LINZ_MATCH_CODE, dtype: int64


%%time
df = df.reset_index()
distances = df[~pd.isna(df.LINZ_parcel_ID)].distance(parcels.loc[df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]], align=False)
display(distances)
display(distances[distances>0].describe())

0         337.062690
1           0.000000
2           0.000000
3           0.000000
4           0.000000
             ...    
250925      0.000000
250926     12.429865
250927      0.000000
250928      0.000000
250929      0.000000
Length: 250759, dtype: float64

count     61487.000000
mean       2861.795532
std        8825.733245
min           0.000022
25%          11.087370
50%          40.513845
75%         243.781313
max      100594.158222
dtype: float64

CPU times: user 12.4 s, sys: 892 ms, total: 13.3 s
Wall time: 13.3 s


df["distance_to_parcel"] = distances


df[(df.LINZ_MATCH_CODE == 3) & (df.distance_to_parcel > 10000)]


%%time
def fix_full_address(row):
    number_name = ' '.join(row.full_address.split(' ')[:2])
    row.full_address = row.full_address.replace(number_name, row.number_name)
    return row
df = df.progress_apply(fix_full_address, axis=1)
df

CPU times: user 30.4 s, sys: 771 ms, total: 31.2 s
Wall time: 31.1 s


session = requests.Session()
def geocode(address):
    result = session.get("https://api-proxy.auckland-cer.cloud.edu.au/nominatim/search.php?q=" + address).json()
    if result:
        return pd.Series(result[0])
    else:
        return pd.Series()
bad_matches = df.full_address[df.LINZ_MATCH_CODE.isin([3,6,7,8,9,10])]
geocoding_results = bad_matches.progress_apply(geocode)
geocoding_results


geocoding_results = geocoding_results.dropna()
display(geocoding_results.category.value_counts())
display(geocoding_results.type.value_counts())

highway      32034
place         7880
leisure       1050
amenity        831
building       414
natural        324
landuse        292
man_made       238
shop           134
railway        120
waterway       119
tourism         71
boundary        68
aeroway         67
bridge          24
office          18
craft            6
historic         2
junction         2
club             1
emergency        1
Name: category, dtype: int64

residential               10420
secondary                  7732
house                      6693
tertiary                   6330
unclassified               3443
primary                    3092
park                       1014
suburb                      426
school                      307
town                        281
motorway                    253
yes                         253
living_street               236
village                     188
bus_stop                    180
monitoring_station          170
trunk                       153
hamlet                      147
place_of_worship            142
bay                         135
locality                    106
motorway_junction           101
construction                 89
island                       88
retail                       80
station                      72
river                        70
peak                         63
administrative               62
aerodrome                    55
service                      46
university                   44
community_centre             43
emergency_access_point       40
stream                       36
restaurant                   35
pedestrian                   34
fire_station                 34
proposed                     33
beach                        32
motel                        32
water_well                   31
apartments                   29
cliff                        28
hospital                     27
convenience                  27
industrial                   26
marae                        26
doctors                      26
recreation_ground            25
mall                         25
wood                         21
path                         20
cemetery                     20
fuel                         17
golf_course                  16
fast_food                    15
library                      15
hotel                        15
kindergarten                 15
pub                          14
ferry_terminal               14
attraction                   13
water                        12
hairdresser                  12
cinema                       12
parking                      11
sports_centre                10
bicycle                      10
surveillance                 10
bar                          10
pier                          9
volcano                       9
cafe                          9
car                           9
viewpoint                     9
track                         9
supermarket                   9
bare_rock                     9
taxiway                       8
milestone                     8
commercial                    8
detached                      7
health_food                   7
waterfall                     7
cape                          7
neighbourhood                 7
footway                       6
protected_area                6
travel_agency                 6
garden_furniture              6
office                        6
childcare                     6
car_rental                    6
stop                          6
bridge                        5
strait                        5
events_venue                  5
yard                          5
pumping_station               5
chalet                        5
caravan_site                  5
sailmaker                     4
townhall                      4
boat_storage                  4
wastewater_plant              4
tower                         4
tidal_channel                 4
post_office                   4
fitness_centre                4
theatre                       4
department_store              3
greenhouse                    3
company                       3
courthouse                    3
coworking                     3
platform                      3
estate_agent                  3
government                    3
conservation                  3
dentist                       3
association                   3
pitch                         3
horse_riding                  3
tyres                         2
boatbuilder                   2
social_facility               2
veterinary                    2
parking_position              2
bus_station                   2
mountain_range                2
manor                         2
basin                         2
farm                          2
furniture                     2
clothes                       2
police                        2
gate                          2
party                         2
cycleway                      1
diplomatic                    1
grandstand                    1
waste_transfer_station        1
arts_centre                   1
greengrocer                   1
clinic                        1
variety_store                 1
water_sports                  1
outdoor                       1
religious                     1
doityourself                  1
forest                        1
accountant                    1
electronics                   1
loading_dock                  1
dam                           1
money_lender                  1
planned                       1
parking_space                 1
wine                          1
farmland                      1
quarry                        1
corridor                      1
guest_house                   1
railway                       1
landfill                      1
dock                          1
tree                          1
sport                         1
ambulance_station             1
islet                         1
reservoir                     1
shelter                       1
research_institute            1
Name: type, dtype: int64


geocoding_results = gpd.GeoDataFrame(geocoding_results, geometry=gpd.points_from_xy(geocoding_results.lon, geocoding_results.lat), crs=4326).to_crs(2193)
geocoding_results


df["distance_from_geocoding_result"] = df.loc[geocoding_results.index].distance(geocoding_results)
df["distance_from_geocoding_result"].describe()

count    4.369600e+04
mean     8.356446e+03
std      6.271168e+04
min      2.677254e-05
25%      1.316565e+02
50%      4.588468e+02
75%      1.260955e+03
max      1.728650e+06
Name: distance_from_geocoding_result, dtype: float64


%%time
# redo join with updated coordinates
geocoding_results = gpd.sjoin(geocoding_results, parcels[["addresses","geometry"]], how="left")

CPU times: user 13.7 s, sys: 913 ms, total: 14.6 s
Wall time: 14.7 s


geocoding_results = geocoding_results.rename(columns={"index_right": "LINZ_parcel_ID"})
geocoding_results.LINZ_parcel_ID = geocoding_results.LINZ_parcel_ID.astype("Int64")
geocoding_results


geocoding_results["number_name"] = df.number_name[geocoding_results.index]
match = geocoding_results[~pd.isna(geocoding_results.number_name) & ~pd.isna(geocoding_results.addresses)].apply(check_match, axis=1)
geocoding_results["LINZ_MATCH_CODE"] = match
geocoding_results.LINZ_MATCH_CODE.value_counts(dropna=False)

NaN    41426
1.0     2270
Name: LINZ_MATCH_CODE, dtype: int64


geocoding_results["LINZ_2ND_MATCH_CODE"] = df.LINZ_2ND_MATCH_CODE[geocoding_results.index]
def find_match_in_range(row):
    if np.isnan(row.LINZ_2ND_MATCH_CODE):
        r = 1250
    else:
        r = 2000
    indices_in_range = parcel_tree.query_ball_point([row.geometry.x, row.geometry.y], r)
    potential_matches = parcels.iloc[indices_in_range]
    match = potential_matches[potential_matches.addresses.str.contains(row.number_name, na=False, regex=False)]
    if len(match):
        row.LINZ_parcel_ID = match.index[0]
        row.addresses = match.addresses.iloc[0]
        row.full_addresses = match.full_addresses.iloc[0]
        row.LINZ_MATCH_CODE = 2
    return row
matches = geocoding_results[
    ~pd.isna(geocoding_results.number_name) &
    pd.isna(geocoding_results["LINZ_MATCH_CODE"]) &
    geocoding_results.number_name.isin(number_name_lookup.keys())].progress_apply(find_match_in_range, axis=1)


geocoding_results.update(matches)
geocoding_results.LINZ_MATCH_CODE.value_counts(dropna=False)

NaN    41213
1.0     2270
2.0      213
Name: LINZ_MATCH_CODE, dtype: int64


geocoding_results["original_LINZ_MATCH_CODE"] = df.LINZ_MATCH_CODE[geocoding_results.index]


geocoding_results


text_match = geocoding_results.original_LINZ_MATCH_CODE.isin([3,6]) & pd.isna(geocoding_results.LINZ_MATCH_CODE)
print(sum(text_match))
geocoding_results = geocoding_results[~text_match]

5946


street_matches = geocoding_results[pd.isna(geocoding_results.LINZ_MATCH_CODE)].progress_apply(check_street_match, axis=1)
display(street_matches.value_counts(dropna=False))
geocoding_results.LINZ_MATCH_CODE.update(street_matches)

NaN    31775
7.0     3492
dtype: int64


geocoding_results.LINZ_MATCH_CODE[
    pd.isna(geocoding_results.LINZ_MATCH_CODE) &
    pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE) &
    ~pd.isna(geocoding_results.LINZ_parcel_ID)] = 8
geocoding_results.LINZ_MATCH_CODE[
    pd.isna(geocoding_results.LINZ_MATCH_CODE) &
    pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE) &
    pd.isna(geocoding_results.LINZ_parcel_ID)] = 9

<string>:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
<string>:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


geocoding_results.LINZ_MATCH_CODE.value_counts(dropna=False)

8.0    17691
NaN    13548
7.0     3492
1.0     2270
9.0      536
2.0      213
Name: LINZ_MATCH_CODE, dtype: int64


geocoding_results["OBS"] = df.OBS[geocoding_results.index]
geocoding_results.LINZ_MATCH_CODE[pd.isna(geocoding_results.LINZ_MATCH_CODE) &
    geocoding_results.LINZ_2ND_MATCH_CODE.isin([1,2,4,5])
] = 10
geocoding_results.LINZ_MATCH_CODE[pd.isna(geocoding_results.LINZ_MATCH_CODE) & ~pd.isna(geocoding_results.LINZ_parcel_ID)] = 8

<string>:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
<string>:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


# Actually, this operation is equivalent to +=3
geocoding_results.LINZ_MATCH_CODE.update(geocoding_results.LINZ_MATCH_CODE[geocoding_results.original_LINZ_MATCH_CODE.isin([4,5,6])].replace({1: 4, 2: 5, 3: 6}))


made_it_worse = geocoding_results.LINZ_MATCH_CODE > geocoding_results.original_LINZ_MATCH_CODE
print(sum(made_it_worse))
geocoding_results = geocoding_results[~made_it_worse]

4726


geocoding_results["change"] = geocoding_results.apply(lambda row: f"{row.original_LINZ_MATCH_CODE} -> {row.LINZ_MATCH_CODE}", axis=1)
geocoding_results["change"].value_counts()

8.0 -> 8.0      15466
10.0 -> 10.0    10513
3.0 -> 1.0       1880
7.0 -> 7.0       1435
8.0 -> 7.0       1158
10.0 -> 8.0       992
10.0 -> 7.0       899
6.0 -> 4.0        234
10.0 -> 1.0       156
3.0 -> 2.0        152
9.0 -> 9.0         51
10.0 -> 2.0        43
9.0 -> 8.0         18
6.0 -> 5.0         18
8.0 -> nan          6
7.0 -> nan          2
10.0 -> nan         1
Name: change, dtype: int64


display(geocoding_results.change[pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE)].value_counts())
display(geocoding_results.change[~pd.isna(geocoding_results.LINZ_2ND_MATCH_CODE)].value_counts())

8.0 -> 8.0    14052
3.0 -> 1.0     1558
7.0 -> 7.0     1300
8.0 -> 7.0      996
6.0 -> 4.0      216
3.0 -> 2.0      125
9.0 -> 9.0       51
6.0 -> 5.0       18
9.0 -> 8.0       18
Name: change, dtype: int64

10.0 -> 10.0    10513
8.0 -> 8.0       1414
10.0 -> 8.0       992
10.0 -> 7.0       899
3.0 -> 1.0        322
8.0 -> 7.0        162
10.0 -> 1.0       156
7.0 -> 7.0        135
10.0 -> 2.0        43
3.0 -> 2.0         27
6.0 -> 4.0         18
8.0 -> nan          6
7.0 -> nan          2
10.0 -> nan         1
Name: change, dtype: int64


good_changes = geocoding_results.LINZ_MATCH_CODE < geocoding_results.original_LINZ_MATCH_CODE
print(sum(good_changes))
geocoding_results = geocoding_results[good_changes]
geocoding_results["change"].value_counts()

5550

3.0 -> 1.0     1880
8.0 -> 7.0     1158
10.0 -> 8.0     992
10.0 -> 7.0     899
6.0 -> 4.0      234
10.0 -> 1.0     156
3.0 -> 2.0      152
10.0 -> 2.0      43
6.0 -> 5.0       18
9.0 -> 8.0       18
Name: change, dtype: int64


df.update(geocoding_results)
df


df.LINZ_MATCH_CODE.value_counts(dropna=False)

1.0     142544
2.0      51678
8.0      25730
10.0     11430
7.0      10093
3.0       6466
4.0       1735
5.0        871
6.0        286
9.0         97
Name: LINZ_MATCH_CODE, dtype: int64


sum(df.LINZ_MATCH_CODE < df.LINZ_2ND_MATCH_CODE)

622


group = df[~pd.isna(df.LINZ_2ND_MATCH_CODE)].groupby("OBS")
group

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f5bd7cedfa0>


updates = []
for ix, sample in tqdm(group):
    minmatch = sample.LINZ_MATCH_CODE.min()
    if minmatch <=2:
        sample.loc[sample.LINZ_MATCH_CODE>2, "LINZ_MATCH_CODE"] = 10
    elif minmatch <=5:
        sample.loc[sample.LINZ_MATCH_CODE>5, "LINZ_MATCH_CODE"] = 10
    sample.LINZ_2ND_MATCH_CODE = minmatch
    updates.append(sample)
updates = pd.concat(updates)
updates


df.update(updates)


assert sum(df.LINZ_MATCH_CODE < df.LINZ_2ND_MATCH_CODE) == 0


df.LINZ_MATCH_CODE.value_counts()

1.0     142544
2.0      51678
8.0      24729
10.0     13897
7.0       9194
3.0       5903
4.0       1731
5.0        871
6.0        286
9.0         97
Name: LINZ_MATCH_CODE, dtype: int64


df.LINZ_parcel_ID[df.LINZ_MATCH_CODE >= 9] = np.nan
df.addresses[df.LINZ_MATCH_CODE >= 9] = np.nan
df.full_addresses[df.LINZ_MATCH_CODE >= 9] = np.nan

<string>:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
<string>:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
<string>:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


distances = df[~pd.isna(df.LINZ_parcel_ID)].distance(parcels.loc[df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]], align=False)
display(distances)
display(distances[distances>0].describe())

0         337.062690
1           0.000000
2           0.000000
3           0.000000
4           0.000000
             ...    
250923      0.000000
250925      0.000000
250926     12.429865
250927      0.000000
250928      0.000000
Length: 236880, dtype: float64

count     58739.000000
mean       2442.106899
std        8608.354921
min           0.000022
25%          10.651200
50%          35.518932
75%         180.440212
max      399996.638879
dtype: float64


df["distance_to_parcel"] = distances


print(sum((df.LINZ_MATCH_CODE < 7) & (df.addresses.str.len()!=df.full_addresses.str.len())))

313


non_na_parcel_ids = df.LINZ_parcel_ID[~pd.isna(df.LINZ_parcel_ID)]
full_addrs = parcels.full_addresses[non_na_parcel_ids]
full_addrs.index = non_na_parcel_ids.index
df.full_addresses = full_addrs
print(sum((df.LINZ_MATCH_CODE < 7) & (df.addresses.str.len()!=df.full_addresses.str.len())))
display(df)

0


def get_full_addr(row):
    try:
        return row.full_addresses[row.addresses.index(row.number_name)]
    except:
        display(row)
df["matched_address"] = df[df.LINZ_MATCH_CODE < 7].progress_apply(get_full_addr, axis=1)
df


%%time
phase0 = pd.read_csv("output/parcels_phase0.csv", index_col="LINZ_parcel_ID", low_memory=False, lineterminator="\n")
phase0

CPU times: user 1min 12s, sys: 10.7 s, total: 1min 22s
Wall time: 1min 46s


len(df), len(df.LINZ_parcel_ID.unique()), len(phase0), len(phase0.index.unique())

(250930, 158882, 547902, 547902)


%%time
df = df.drop(columns="geometry").merge(phase0, on="LINZ_parcel_ID", how="left")
df

CPU times: user 10.8 s, sys: 1.96 s, total: 12.8 s
Wall time: 12.9 s


df.columns

Index(['index', 'OBS', 'CONSENT_DATE', 'MARCH_YEAR', 'ADDRESS_1', 'ADDRESS_2',
       'ADDRESS_3', 'LB_Name', 'BUILDING_TYPE_NAME', 'BUILDING_TYPE_CODE',
       'FLOOR_AREA', 'VALUE', 'BUILDINGS', 'Building_Type_Group',
       'Residential_Type', 'Business_Group', 'Business_Category',
       'Type_Class_Year', 'MBCODE', 'MBYEAR', 'AUP_BaseZone',
       'AUP_BaseZone_Group', 'ZONE', 'ZONE_ID', 'ZONE_NAME', 'ZONE_TYPE',
       'BUSINESS_TYPE', 'PlanAreaName', 'SHA_Name_154', 'X_Coordinate',
       'Y_Coordinate', 'Ranged_Address_indicator', 'number_name',
       'full_address', 'LINZ_parcel_ID', 'addresses', 'full_addresses',
       'LINZ_MATCH_CODE', 'distance_to_parcel', 'LINZ_2ND_MATCH_CODE',
       'distance_from_geocoding_result', 'matched_address', 'appellation',
       'affected_surveys', 'parcel_intent', 'topology_type',
       'statutory_actions', 'land_district', 'titles', 'survey_area',
       'calc_area', 'LINZ_parcel_centroid_lon', 'LINZ_parcel_centroid_lat',
       'LINZ_parcel_vertices_lon', 'LINZ_parcel_vertices_lat',
       'LINZ_parcel_roadvertices_lon', 'LINZ_parcel_roadvertices_lat',
       'LINZmatch_AUP_code', 'LINZmatch_AUP_name', 'LINZ_adjoining_parcel_ID',
       'LINZ_parcel_sides_zones', 'LINZ_TRNSPWR_ohead_name',
       'LINZ_TRNSPWR_ohead_indicator', 'LINZ_VWSHFT_ohead_name',
       'LINZ_VWSHFT_ohead_ID', 'LINZ_VWSHFT_ohead_indicator', 'Hdist_rural',
       'Hdist_rural_code', 'Hdist_rural_name', 'Hdist_bus', 'Hdist_bus_code',
       'Hdist_bus_name', 'Hdist_resid', 'Hdist_resid_code', 'Hdist_resid_name',
       'Hdist_SH', 'Hdist_MHS', 'Hdist_MHU', 'Hdist_THA', 'Local_Area_name',
       'SA22018_name', 'SA22018_code', 'AU2013_name', 'AU2013_code',
       'MB2018_code', 'MB2013_code', 'Hdist_coast', 'Hdist_motorway',
       'Hdist_main_road', 'Hdist_rail', 'Hdist_skytower', 'SpHA_indicator'],
      dtype='object')


df.to_csv("restricted/BCs_issued_by_AUP_TLADCs_2021DEC_augmented.csv", index=False)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry
0	244368	2021-12-01	2021-03-01	14 TE ARA O KIRI PATUPARAOA ANAKE	Tomarata	Auckland	Rodney	Houses	1111	399.0	1100000	1	Residential	Detached	NaN	NaN	2014	135300.0	2013.0	Rural - Rural Coastal Zone	Rural	46.0	RDC_15	Landscape Protection Rur	Rural	NaN	NaN	NaN	174.658935	-36.188787	POINT (174.65893 -36.18879)
1	244369	2021-12-01	2021-03-01	TE ARA O KIRI PATUPARAOA ANAKE	Tomarata	Auckland	Rodney	Restaurants, bars, and cafes	2512	134.0	1500000	1	Business	NaN	Group 2	Hospitality	2014	135300.0	2013.0	Rural - Rural Coastal Zone	Rural	46.0	RDC_15	Landscape Protection Rur	Rural	NaN	NaN	NaN	174.661091	-36.189144	POINT (174.66109 -36.18914)
2	244451	2021-12-01	2021-03-01	58 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111	224.0	770000	1	Residential	Detached	NaN	NaN	2014	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.663924	-36.584054	POINT (174.66392 -36.58405)
3	244452	2021-12-01	2021-03-01	44 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111	247.0	670000	1	Residential	Detached	NaN	NaN	2014	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662942	-36.585117	POINT (174.66294 -36.58512)
4	244453	2021-12-01	2021-03-01	32 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111	252.0	550000	1	Residential	Detached	NaN	NaN	2014	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662988	-36.585185	POINT (174.66299 -36.58518)
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
217857	151352	2014-07-01	2015-03-01	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200	0.0	3650000	0	Residential	Attached	NaN	NaN	1998	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (174.76031 -36.85153)
217858	151351	2014-07-01	2015-03-01	464	REMUERA RD	REMUERA	Orakei	New Flats	1200	0.0	120000	0	Residential	Attached	NaN	NaN	1998	490600.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_28	Residential 7c	Residential	NaN	NaN	NaN	174.801622	-36.880545	POINT (174.80162 -36.88055)
217859	151350	2014-07-01	2015-03-01	55B	PANORAMA RD	MOUNT WELLINGTON	Maungakiekie - Tamaki	New (and pre-built) house, unit, bach, crib, b...	1100	140.0	200000	1	Residential	Detached	NaN	NaN	1998	643401.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.830461	-36.904046	POINT (174.83046 -36.90405)
217860	151349	2014-07-01	2015-03-01	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Offices	1461	0.0	0	1	Business	NaN	Group 2	Offices	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768482	-36.845564	POINT (174.76848 -36.84556)
217861	151348	2014-07-01	2015-03-01	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Hotels - excludes taverns, hotel bars and ...	1411	8974.0	500000	1	Business	NaN	Group 2	Hospitality	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768482	-36.845564	POINT (174.76848 -36.84556)

	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	geometry
id
4918991	Lot 327 DP 56341	DP 56341	DCDB	Primary	None	North Auckland	NA8C/911	642.0	643.0	MULTIPOLYGON (((1764256.381 5917306.269, 17642...
4919914	Lot 178 DP 56342	DP 56342	DCDB	Primary	None	North Auckland	474924, NA8C/768	650.0	651.0	MULTIPOLYGON (((1764569.269 5917546.907, 17645...
4926734	Lot 144 DP 53562	DP 53562	DCDB	Primary	None	North Auckland	386353, 386354, 386355, 386356, NA4D/859	640.0	640.0	MULTIPOLYGON (((1764447.122 5917598.067, 17644...
4926945	Lot 183 DP 56342	DP 56342	DCDB	Primary	None	North Auckland	433646, NA8C/773	913.0	914.0	MULTIPOLYGON (((1764510.216 5917605.992, 17644...
4929034	Lot 260 DP 56341	DP 56341	DCDB	Primary	None	North Auckland	488577, NA8C/850	622.0	624.0	MULTIPOLYGON (((1764653.473 5917395.879, 17646...
...	...	...	...	...	...	...	...	...	...	...
5227472	None	None	Road	Primary	None	North Auckland	None	NaN	1330.0	MULTIPOLYGON (((1766929.317 5907928.650, 17669...
5242816	None	None	Road	Primary	None	North Auckland	None	NaN	1049.0	MULTIPOLYGON (((1766662.352 5908073.993, 17666...
7520053	Lot 5 DP 465319	DP 465319	Fee Simple Title	Primary	None	North Auckland	621250	2253.0	2252.0	MULTIPOLYGON (((1766988.975 5907984.681, 17669...
7520049	Lot 1 DP 465319	DP 465319	Fee Simple Title	Primary	None	North Auckland	619244	13064.0	13062.0	MULTIPOLYGON (((1766988.975 5907984.681, 17669...
4733845	Part Lot 59 DP 60001	DP 60001	DCDB	Primary	[Referenced] Declaration That State Housing La...	North Auckland	NA86D/103	5449.0	5447.0	MULTIPOLYGON (((1766545.381 5908050.017, 17665...

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses
0	244368	2021-12-01	2021-03-01	14 TE ARA O KIRI PATUPARAOA ANAKE	Tomarata	Auckland	Rodney	Houses	1111	399.0	1100000	1	Residential	Detached	NaN	NaN	2014	135300.0	2013.0	Rural - Rural Coastal Zone	Rural	46.0	RDC_15	Landscape Protection Rur	Rural	NaN	NaN	NaN	174.658935	-36.188787	POINT (1749165.061 5993836.774)	False	14 te	14 te ara o kiri patuparaoa anake tomarata auc...	8201494	NaN	NaN
1	244369	2021-12-01	2021-03-01	TE ARA O KIRI PATUPARAOA ANAKE	Tomarata	Auckland	Rodney	Restaurants, bars, and cafes	2512	134.0	1500000	1	Business	NaN	Group 2	Hospitality	2014	135300.0	2013.0	Rural - Rural Coastal Zone	Rural	46.0	RDC_15	Landscape Protection Rur	Rural	NaN	NaN	NaN	174.661091	-36.189144	POINT (1749358.254 5993793.842)	False	te ara	te ara o kiri patuparaoa anake tomarata auckland	8201477	NaN	NaN
2	244451	2021-12-01	2021-03-01	58 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111	224.0	770000	1	Residential	Detached	NaN	NaN	2014	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.663924	-36.584054	POINT (1748858.010 5949979.809)	False	58 paparahi	58 paparahi place upper orewa auckland	8156563	NaN	NaN
3	244452	2021-12-01	2021-03-01	44 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111	247.0	670000	1	Residential	Detached	NaN	NaN	2014	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662942	-36.585117	POINT (1748768.145 5949863.416)	False	44 paparahi	44 paparahi place upper orewa auckland	8156550	NaN	NaN
4	244453	2021-12-01	2021-03-01	32 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111	252.0	550000	1	Residential	Detached	NaN	NaN	2014	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662988	-36.585185	POINT (1748772.124 5949855.789)	False	32 paparahi	32 paparahi place upper orewa auckland	8156550	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
217857	151352	2014-07-01	2015-03-01	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200	0.0	3650000	0	Residential	Attached	NaN	NaN	1998	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.813 5920152.000)	True	164-168 hobson	164-168 hobson st auckland	6752833	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...
217858	151351	2014-07-01	2015-03-01	464	REMUERA RD	REMUERA	Orakei	New Flats	1200	0.0	120000	0	Residential	Attached	NaN	NaN	1998	490600.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_28	Residential 7c	Residential	NaN	NaN	NaN	174.801622	-36.880545	POINT (1760559.125 5916863.750)	False	464 remuera	464 remuera rd remuera	4928237	[464 remuera, 2/464 remuera, 1/464 remuera, 3b...	[464 Remuera Road, Remuera, Auckland, 2/464 Re...
217859	151350	2014-07-01	2015-03-01	55B	PANORAMA RD	MOUNT WELLINGTON	Maungakiekie - Tamaki	New (and pre-built) house, unit, bach, crib, b...	1100	140.0	200000	1	Residential	Detached	NaN	NaN	1998	643401.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.830461	-36.904046	POINT (1763079.438 5914207.500)	False	55b panorama	55b panorama rd mount wellington	4963967	[55 panorama]	[55 Panorama Road, Mount Wellington, Auckland]
217860	151349	2014-07-01	2015-03-01	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Offices	1461	0.0	0	1	Business	NaN	Group 2	Offices	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768482	-36.845564	POINT (1757677.375 5920800.000)	False	67 customs	67 customs st east auckland central	5030200	[67 customs, 8/67 customs, 7/67 customs, 4/67 ...	[67 Customs Street East, Auckland Central, Auc...
217861	151348	2014-07-01	2015-03-01	67	CUSTOMS ST EAST	AUCKLAND CENTRAL	Waitemata	New Hotels - excludes taverns, hotel bars and ...	1411	8974.0	500000	1	Business	NaN	Group 2	Hospitality	1998	438104.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_2	Central Area B	Business	Commercial	NaN	NaN	174.768482	-36.845564	POINT (1757677.375 5920800.000)	False	67 customs	67 customs st east auckland central	5030200	[67 customs, 8/67 customs, 7/67 customs, 4/67 ...	[67 Customs Street East, Auckland Central, Auc...

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
0	244368	2021-12-01	2021-03-01	14 TE ARA O KIRI PATUPARAOA ANAKE	Tomarata	Auckland	Rodney	Houses	1111	399.0	1100000	1	Residential	Detached	NaN	NaN	2014	135300.0	2013.0	Rural - Rural Coastal Zone	Rural	46.0	RDC_15	Landscape Protection Rur	Rural	NaN	NaN	NaN	174.658935	-36.188787	POINT (1749165.061 5993836.774)	False	14 te	14 te ara o kiri patuparaoa anake tomarata auc...	8201470	[14 te]	[14 Te Ara O Kiri Patuparaoa Anake, Te Arai]	2.0
6	244455	2021-12-01	2021-03-01	226 GRAND DRIVE	Orewa	Auckland	Hibiscus and Bays	Houses	1111	136.0	482540	1	Residential	Detached	NaN	NaN	2014	176411.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.668900	-36.588649	POINT (1749294.370 5949462.296)	False	226 grand	226 grand drive orewa auckland	6579709	[226 grand]	[226 Grand Drive, Wainui]	2.0
7	244456	2021-12-01	2021-03-01	47 ARA HILLS DRIVE	Upper Orewa	Auckland	Rodney	Non-building construction	3111	0.0	60000	1	Other	NaN	NaN	NaN	2014	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662215	-36.584051	POINT (1748705.140 5949982.794)	False	47 ara	47 ara hills drive upper orewa auckland	8156585	NaN	NaN	NaN
9	244535	2021-12-01	2021-03-01	12 ZADAR STREET	Huapai	Auckland	Rodney	Houses	1111	100.0	320000	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.546952	-36.775971	POINT (1738049.253 5928863.567)	False	12 zadar	12 zadar street huapai auckland	7881869	[10 zadar, 12 zadar]	[10 Zadar Street, Kumeū, 12 Zadar Street, Kumeū]	2.0
10	244537	2021-12-01	2021-03-01	2 ZADAR STREET	Huapai	Auckland	Rodney	Houses	1111	166.0	358000	1	Residential	Detached	NaN	NaN	2014	214900.0	2013.0	Road	General	27.0	RDC_7	Future Urban	Rural	NaN	Huapai Triangle	Huapai Triangle	174.546952	-36.775971	POINT (1738049.253 5928863.567)	False	2 zadar	2 zadar street huapai auckland	7881872	[2 zadar, 19 konoba]	[2 Zadar Street, Kumeū, 19 Konoba Avenue, Kumeū]	2.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
217834	151375	2014-07-01	2015-03-01	20A	HOLBROOK ST	AVONDALE	Whau	New (and pre-built) house, unit, bach, crib, b...	1100	241.0	350000	1	Residential	Detached	NaN	NaN	1998	378602.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_18	Residential 5	Residential	NaN	NaN	NaN	174.704383	-36.908561	POINT (1751837.000 5913914.750)	False	20a holbrook	20a holbrook st avondale	5086422	[20a holbrook, 20b holbrook]	[20A Holbrook Street, Blockhouse Bay, Auckland...	2.0
217839	151370	2014-07-01	2015-03-01	5B	COMMODORE DR	LYNFIELD	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100	160.0	250000	1	Residential	Detached	NaN	NaN	1998	587400.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.720223	-36.926547	POINT (1753212.268 5911893.875)	False	5b commodore	5b commodore dr lynfield	6810857	[5b commodore]	[5B Commodore Drive, Mount Roskill, Auckland]	2.0
217841	151368	2014-07-01	2015-03-01	54	ELLIS AVE	MOUNT ROSKILL	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100	158.0	268000	1	Residential	Detached	NaN	NaN	1998	584402.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.719375	-36.912989	POINT (1753163.813 5913399.500)	False	54 ellis	54 ellis ave mount roskill	7607279	[54 ellis]	[54 Ellis Avenue, Mount Roskill, Auckland]	2.0
217844	151365	2014-07-01	2015-03-01	1B	WAIRUA RD	REMUERA	Orakei	New (and pre-built) house, unit, bach, crib, b...	1100	127.0	400000	1	Residential	Detached	NaN	NaN	1998	494200.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_18	Residential 5	Residential	NaN	NaN	NaN	174.804747	-36.882127	POINT (1760834.375 5916683.000)	False	1b wairua	1b wairua rd remuera	7487630	[1b wairua]	[1B Wairua Road, Remuera, Auckland]	2.0
217859	151350	2014-07-01	2015-03-01	55B	PANORAMA RD	MOUNT WELLINGTON	Maungakiekie - Tamaki	New (and pre-built) house, unit, bach, crib, b...	1100	140.0	200000	1	Residential	Detached	NaN	NaN	1998	643401.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.830461	-36.904046	POINT (1763079.438 5914207.500)	False	55b panorama	55b panorama rd mount wellington	5068248	[55b panorama]	[55B Panorama Road, Mount Wellington, Auckland]	2.0

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
7	244456.0	2021-12-01	2021-03-01	47 ARA HILLS DRIVE	Upper Orewa	Auckland	Rodney	Non-building construction	3111.0	0.0	60000.0	1.0	Other	NaN	NaN	NaN	2014.0	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662215	-36.584051	POINT (1748705.140 5949982.794)	False	47 ara	47 ara hills drive upper orewa auckland	8156585	NaN	NaN	NaN
37	245372.0	2021-12-01	2021-03-01	180 WELLINGTON STREET	Pukekohe	Auckland	Franklin	School buildings	2312.0	750.0	1300000.0	1.0	Other	NaN	NaN	NaN	2014.0	822405.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	FDC_9	Residential	Residential	NaN	NaN	NaN	174.897891	-37.215997	POINT (1768396.392 5879478.719)	False	180 wellington	180 wellington street pukekohe auckland	4944316	[184 wellington]	[184 Wellington Street, Pukekohe]	NaN
181	244566.0	2021-12-01	2021-03-01	8 DALE ROAD	Whenuapai	Auckland	Upper Harbour	Townhouses, flats, units, and other dwellings	1129.0	153.0	300900.0	1.0	Residential	Attached	NaN	NaN	2014.0	219900.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_5	Countryside	Rural	NaN	Whenuapai Village	Whenuapai Village	174.608890	-36.787791	POINT (1743554.885 5927461.004)	False	8 dale	8 dale road whenuapai auckland	8221550	NaN	NaN	NaN
184	244569.0	2021-12-01	2021-03-01	8 DALE ROAD	Whenuapai	Auckland	Upper Harbour	Townhouses, flats, units, and other dwellings	1129.0	153.0	300900.0	1.0	Residential	Attached	NaN	NaN	2014.0	219900.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_5	Countryside	Rural	NaN	Whenuapai Village	Whenuapai Village	174.608890	-36.787791	POINT (1743554.885 5927461.004)	False	8 dale	8 dale road whenuapai auckland	8221550	NaN	NaN	NaN
185	244570.0	2021-12-01	2021-03-01	8 DALE ROAD	Whenuapai	Auckland	Upper Harbour	Townhouses, flats, units, and other dwellings	1129.0	153.0	384850.0	1.0	Residential	Attached	NaN	NaN	2014.0	219900.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_5	Countryside	Rural	NaN	Whenuapai Village	Whenuapai Village	174.608890	-36.787791	POINT (1743554.885 5927461.004)	False	8 dale	8 dale road whenuapai auckland	8221550	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
217780	151429.0	2014-07-01	2015-03-01	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	95.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.313 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
217781	151428.0	2014-07-01	2015-03-01	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	120.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.313 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
217782	151427.0	2014-07-01	2015-03-01	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	81.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.313 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
217783	151426.0	2014-07-01	2015-03-01	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	95.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.313 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN
217784	151425.0	2014-07-01	2015-03-01	62	BIRDWOOD AVE	PAPATOETOE	Otara - Papatoetoe	New (and pre-built) house, unit, bach, crib, b...	1100.0	81.0	295000.0	1.0	Residential	Detached	NaN	NaN	1998.0	665300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866824	-36.977261	POINT (1766160.313 5906021.614)	False	62 birdwood	62 birdwood ave papatoetoe	7594476	[7 tukutuku]	[7 Tukutuku Lane, Papatoetoe, Auckland]	NaN

INPUTS¶

OUTPUTS¶

	address_id	source_dataset	change_id	full_address_number	full_road_name	full_address	territorial_authority	unit_type	unit_value	level_type	level_value	address_number_prefix	address_number	address_number_suffix	address_number_high	road_name_prefix	road_name	road_type_name	road_suffix	water_name	water_body_name	suburb_locality	town_city	address_class	address_lifecycle	gd2000_xcoord	gd2000_ycoord	road_name_ascii	water_name_ascii	water_body_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry
0	999891	AIMS	887328	10	Barnfield Place	10 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	10	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.651783	-36.891240	Barnfield	None	None	Glendene	Auckland	Barnfield Place	10 Barnfield Place, Glendene, Auckland	POINT (1747183.936 5915918.849)
1	999921	AIMS	887357	11	Barnfield Place	11 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	11	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652010	-36.891441	Barnfield	None	None	Glendene	Auckland	Barnfield Place	11 Barnfield Place, Glendene, Auckland	POINT (1747203.803 5915896.158)
2	999986	AIMS	887418	14	Barnfield Place	14 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	14	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652128	-36.891016	Barnfield	None	None	Glendene	Auckland	Barnfield Place	14 Barnfield Place, Glendene, Auckland	POINT (1747215.175 5915943.129)
3	1000015	AIMS	887447	16	Barnfield Place	16 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	16	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652287	-36.890874	Barnfield	None	None	Glendene	Auckland	Barnfield Place	16 Barnfield Place, Glendene, Auckland	POINT (1747229.606 5915958.610)
4	1000050	AIMS	887473	18	Barnfield Place	18 Barnfield Place, Glendene, Auckland	Auckland	None	None	None	None	None	18	None	NaN	None	Barnfield	Place	None	None	None	Glendene	Auckland	Thoroughfare	Current	174.652338	-36.890719	Barnfield	None	None	Glendene	Auckland	Barnfield Place	18 Barnfield Place, Glendene, Auckland	POINT (1747234.400 5915975.823)
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
667637	3070850	CADS	70850	27	Wairiki Road	27 Wairiki Road, Mount Eden, Auckland	Auckland	None	None	None	None	None	27	None	NaN	None	Wairiki	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754973	-36.890654	Wairiki	None	None	Mount Eden	Auckland	Wairiki Road	27 Wairiki Road, Mount Eden, Auckland	POINT (1756380.800 5915819.700)
667638	3070851	CADS	70851	44	Wairiki Road	44 Wairiki Road, Mount Eden, Auckland	Auckland	None	None	None	None	None	44	None	NaN	None	Wairiki	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.755018	-36.890903	Wairiki	None	None	Mount Eden	Auckland	Wairiki Road	44 Wairiki Road, Mount Eden, Auckland	POINT (1756384.300 5915792.000)
667639	3075863	CADS	75863	2/50	Ellerton Road	2/50 Ellerton Road, Mount Eden, Auckland	Auckland	None	2	None	None	None	50	None	NaN	None	Ellerton	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754430	-36.892419	Ellerton	None	None	Mount Eden	Auckland	Ellerton Road	2/50 Ellerton Road, Mount Eden, Auckland	POINT (1756328.800 5915624.800)
667640	3075874	CADS	75874	1/15	Douglas Road	1/15 Douglas Road, Mount Eden, Auckland	Auckland	None	1	None	None	None	15	None	NaN	None	Douglas	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754200	-36.889190	Douglas	None	None	Mount Eden	Auckland	Douglas Road	1/15 Douglas Road, Mount Eden, Auckland	POINT (1756314.900 5915983.400)
667641	3075875	CADS	75875	2/15	Douglas Road	2/15 Douglas Road, Mount Eden, Auckland	Auckland	None	2	None	None	None	15	None	NaN	None	Douglas	Road	None	None	None	Mount Eden	Auckland	Thoroughfare	Current	174.754200	-36.889190	Douglas	None	None	Mount Eden	Auckland	Douglas Road	2/15 Douglas Road, Mount Eden, Auckland	POINT (1756314.900 5915983.400)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
22	244924.0	2021-12-01	2021-03-01	19/1 NGAPIPI ROAD	ORAKEI	Auckland	Orakei	Domestic outbuildings	1211.0	0.0	400000.0	1.0	Other	NaN	NaN	NaN	2014.0	496900.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.810234	-36.856192	POINT (1761377.944 5919551.101)	False	19/1 ngapipi	19/1 ngapipi road orakei auckland	5226479	NaN	NaN	NaN
20978	212327.0	2020-03-01	2020-03-01	27033/1 Railway Land	Panmure	Auckland	Maungakiekie - Tamaki	Non-building construction	3111.0	0.0	45000.0	1.0	Other	NaN	NaN	NaN	2014.0	628100.0	2013.0	Strategic Transport Corridor Zone	General	26.0	ACC_34	Special Purpose 3	Other	NaN	NaN	NaN	174.849439	-36.898071	POINT (1764783.296 5914837.830)	False	27033/1 railway	27033/1 railway land panmure auckland	5205106	NaN	NaN	NaN
28016	200118.0	2019-07-01	2020-03-01	57F/3 FOREST HILL ROAD	Henderson	Auckland	Henderson - Massey	Houses	1111.0	139.0	287000.0	1.0	Residential	Detached	NaN	NaN	2014.0	281403.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.615666	-36.898033	POINT (1743952.711 5915220.304)	False	57f/3 forest	57f/3 forest hill road henderson auckland	7941070	[3/57f forest]	[3/57F Forest Hill Road, Henderson, Auckland]	NaN
28017	200119.0	2019-07-01	2020-03-01	57F/1 FOREST HILL ROAD	Henderson	Auckland	Henderson - Massey	Houses	1111.0	139.0	287000.0	4.0	Residential	Detached	NaN	NaN	2014.0	281403.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.616060	-36.898146	POINT (1743987.636 5915207.207)	False	57f/1 forest	57f/1 forest hill road henderson auckland	7941072	[1/57f forest]	[1/57F Forest Hill Road, Henderson, Auckland]	NaN
39425	182824.0	2018-07-01	2019-03-01	1/334 GLENBROOK ROAD	PUKEKOHE	Auckland	Franklin	Houses	1111.0	649.0	1000000.0	1.0	Residential	Detached	NaN	NaN	2014.0	809900.0	2013.0	Rural - Rural Production Zone	Rural	16.0	FDC_11	Rural	Rural	NaN	NaN	NaN	174.849579	-37.149424	POINT (1764253.486 5886949.969)	False	1/334 glenbrook	1/334 glenbrook road pukekohe auckland	7670282	[2 homestead]	[2 Homestead Hill Lane, Kingseat]	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
215708	78099.0	2002-02-01	2002-04-01	2/40	ELLIS AVE	MT ROSKILL	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100.0	262.0	200000.0	2.0	Residential	Detached	NaN	NaN	1998.0	584402.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.720303	-36.913598	POINT (1753245.256 5913330.470)	False	2/40 ellis	2/40 ellis ave mt roskill	4903343	[40b ellis, 40a ellis, 40 ellis, 42 ellis]	[40B Ellis Avenue, Mount Roskill, Auckland, 40...	NaN
216216	148687.0	2014-01-01	2014-03-01	2/76	GLAMORGAN DR	TORBAY	Hibiscus and Bays	New (and pre-built) house, unit, bach, crib, b...	1100.0	247.0	370500.0	1.0	Residential	Detached	NaN	NaN	1998.0	165300.0	2013.0	Residential - Single House Zone	Residential	19.0	NSC_542	Residential 4B	Residential	NaN	NaN	NaN	174.742103	-36.701771	POINT (1755615.613 5936796.169)	False	2/76 glamorgan	2/76 glamorgan dr torbay	5189573	[76b glamorgan, 76c glamorgan, 76 glamorgan, 7...	[76B Glamorgan Drive, Torbay, Auckland, 76C Gl...	NaN
216840	150759.0	2014-05-01	2015-03-01	2/55	ARAWA ST	NaN	Whau	New (and pre-built) house, unit, bach, crib, b...	1100.0	225.0	320000.0	1.0	Residential	Detached	NaN	NaN	1998.0	269400.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	WCC_8	Living 1	Residential	NaN	New Lynn Strategic Area	New Lynn - Strategic Area	174.670965	-36.910209	POINT (1748856.495 5913784.605)	False	2/55 arawa	2/55 arawa st	8132319	[55a arawa]	[55A Arawa Street, New Lynn, Auckland]	NaN
216870	150729.0	2014-05-01	2015-03-01	3B/12	SEAWAY RD	NaN	Franklin	New (and pre-built) house, unit, bach, crib, b...	1100.0	23.0	45000.0	1.0	Residential	Detached	NaN	NaN	1998.0	810605.0	2013.0	Business - Neighbourhood Centre Zone	Business	44.0	FDC_19	Village Business	Business	Commercial	NaN	NaN	174.701889	-37.135781	POINT (1751163.817 5888709.273)	False	3b/12 seaway	3b/12 seaway rd	6728635	[14 seaway, 12 seaway]	[14 Seaway Road, Clarks Beach, 12 Seaway Road,...	NaN
217335	151875.0	2014-07-01	2015-03-01	2/42A	SEAVIEW RD	CASTOR BAY	Devonport - Takapuna	New (and pre-built) house, unit, bach, crib, b...	1100.0	377.0	590000.0	1.0	Residential	Detached	NaN	NaN	1998.0	313400.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	NSC_541	Residential 4A	Residential	NaN	NaN	NaN	174.761845	-36.764401	POINT (1757251.442 5929815.559)	False	2/42a seaview	2/42a seaview rd castor bay	5176247	[42 seaview]	[42 Seaview Road, Castor Bay, Auckland]	NaN

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE
502	245146.0	2021-12-01	2021-03-01	125C MURPHYS ROAD	Flat Bush	Auckland	Howick	Non-building construction	3111.0	0.0	500000.0	1.0	Other	NaN	NaN	NaN	2014.0	713501.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	MCC_S1	Future Development Stage	Special	NaN	Flat Bush Strategic Area	Flat Bush Strategic Area	174.921127	-36.984668	POINT (1770977.572 5905103.596)	False	125c murphys	125c murphys road flat bush auckland	7849289	[121 murphys]	[121 Murphys Road, Flat Bush, Auckland]	NaN
503	245147.0	2021-12-01	2021-03-01	125C MURPHYS ROAD	Flat Bush	Auckland	Howick	Non-building construction	3111.0	0.0	215000.0	1.0	Other	NaN	NaN	NaN	2014.0	713501.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	MCC_S1	Future Development Stage	Special	NaN	Flat Bush Strategic Area	Flat Bush Strategic Area	174.921127	-36.984668	POINT (1770977.572 5905103.596)	False	125c murphys	125c murphys road flat bush auckland	7849289	[121 murphys]	[121 Murphys Road, Flat Bush, Auckland]	NaN
929	244003.0	2021-11-01	2021-03-01	30R BEIHLERS ROAD	Weymouth	Auckland	Manurewa	Non-building construction	3111.0	0.0	460000.0	1.0	Other	NaN	NaN	NaN	2014.0	773400.0	2013.0	Open Space - Informal Recreation Zone	Public Open Space	32.0	MCC_POS5	Public Open Space 5	Other	NaN	NaN	NaN	174.865728	-37.050755	POINT (1765902.824 5897869.308)	False	30r beihlers	30r beihlers road weymouth auckland	4761589	NaN	NaN	NaN
1682	243876.0	2021-11-01	2021-03-01	37A MCELDOWNIE ROAD	DRURY	Auckland	Franklin	Non-building construction	3111.0	0.0	16965.0	1.0	Other	NaN	NaN	NaN	2014.0	809104.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	FDC_11	Rural	Rural	NaN	Quarry Road, Drury	Quarry Road, Drury	174.969973	-37.138873	POINT (1774970.569 5887905.334)	False	37a mceldownie	37a mceldownie road drury auckland	8187004	[37 mceldownie]	[37 McEldownie Road, Ramarama]	NaN
1686	243880.0	2021-11-01	2021-03-01	37A MCELDOWNIE ROAD	DRURY	Auckland	Franklin	Non-building construction	3111.0	0.0	20104.0	1.0	Other	NaN	NaN	NaN	2014.0	809104.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	FDC_11	Rural	Rural	NaN	Quarry Road, Drury	Quarry Road, Drury	174.969973	-37.138873	POINT (1774970.569 5887905.334)	False	37a mceldownie	37a mceldownie road drury auckland	8187004	[37 mceldownie]	[37 McEldownie Road, Ramarama]	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
217724	151485.0	2014-07-01	2015-03-01	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	147.0	183000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.999 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
217749	151460.0	2014-07-01	2015-03-01	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	151.0	215000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.999 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
217752	151457.0	2014-07-01	2015-03-01	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	154.0	198000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.999 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
217756	151453.0	2014-07-01	2015-03-01	398S	WEYMOUTH RD	WEYMOUTH	Manurewa	New (and pre-built) house, unit, bach, crib, b...	1100.0	154.0	198000.0	1.0	Residential	Detached	NaN	NaN	1998.0	773813.0	2013.0	Road	General	27.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.866009	-37.039187	POINT (1765952.999 5899152.369)	False	398s weymouth	398s weymouth rd weymouth	5260612	NaN	NaN	NaN
217790	151419.0	2014-07-01	2015-03-01	253S	BUCKLAND RD	MANGERE EAST	Mangere - Otahuhu	New Schools - excludes school hostels	1431.0	685.0	2362000.0	1.0	Other	NaN	NaN	NaN	1998.0	735100.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.818644	-36.967455	POINT (1761892.302 5907192.497)	False	253s buckland	253s buckland rd mangere east	4852120	NaN	NaN	NaN

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE	distance_to_parcel
17	244776.0	2021-12-01	2021-03-01	50-54 LANCASTER ROAD	BEACH HAVEN	AUCKLAND	Kaipatiki	Townhouses, flats, units, and other dwellings	1129.0	516.0	156100.0	5.0	Residential	Attached	NaN	NaN	2014.0	343200.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	174.695165	-36.791558	POINT (1751246.087 5926910.167)	True	50-54 lancaster	50-54 lancaster road beach haven auckland	5262084	NaN	NaN	8.0	0.0
18	244780.0	2021-12-01	2021-03-01	24-28 TAURUS CRESCENT	BEACH HAVEN	Auckland	Kaipatiki	Townhouses, flats, units, and other dwellings	1129.0	232.0	1326000.0	2.0	Residential	Attached	NaN	NaN	2014.0	344800.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	NSC_542	Residential 4B	Residential	NaN	NaN	NaN	174.694473	-36.801547	POINT (1751164.697 5925803.008)	True	24-28 taurus	24-28 taurus crescent beach haven auckland	4863757	[28 taurus]	[28 Taurus Crescent, Beach Haven, Auckland]	7.0	0.0
19	244784.0	2021-12-01	2021-03-01	32-38 TAURUS CRESCENT - AREA 2 RETAINING	BEACH HAVEN	AUCKLAND	Kaipatiki	Non-building construction	3111.0	0.0	152950.0	1.0	Other	NaN	NaN	NaN	2014.0	344800.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	NSC_542	Residential 4B	Residential	NaN	NaN	NaN	174.694837	-36.801229	POINT (1751197.876 5925837.768)	True	32-38 taurus	32-38 taurus crescent - area 2 retaining beach...	4863761	[32 taurus]	[32 Taurus Crescent, Beach Haven, Auckland]	7.0	0.0
20	244846.0	2021-12-01	2021-03-01	11-13 MERCURY LANE	Newton	Auckland	Waitemata	Public transport buildings	2522.0	3875.0	8000000.0	1.0	Other	NaN	NaN	NaN	2014.0	412100.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_9	Central Area I	Business	Commercial	NaN	NaN	174.758774	-36.858477	POINT (1756785.340 5919383.369)	True	11-13 mercury	11-13 mercury lane newton auckland	5170727	[13 mercury, 11 mercury]	[13 Mercury Lane, Auckland Central, Auckland, ...	7.0	0.0
21	244872.0	2021-12-01	2021-03-01	121-123 Beach Road	Auckland Central	Auckland	Waitemata	Apartments	1121.0	77.0	200000.0	1.0	Residential	Attached	NaN	NaN	2014.0	438302.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_4	Central Area D	Business	Commercial	NaN	NaN	174.774076	-36.848621	POINT (1758169.848 5920451.549)	True	121-123 beach	121-123 beach road auckland central auckland	4761485	[2/121 beach, 7/121 beach, 6/121 beach, 1/121 ...	[2/121 Beach Road, Auckland Central, Auckland,...	7.0	0.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
217689	151520.0	2014-07-01	2015-03-01	2-6	TITIRANGI RD	NEW LYNN	Whau	New Shops	1451.0	0.0	10100000.0	0.0	Business	NaN	Group 2	Retail	1998.0	264901.0	2013.0	Business - Metropolitan Centre Zone	Business	10.0	WCC_3	Community	Business	Commercial	NaN	NaN	174.677785	-36.910257	POINT (1749463.948 5913768.524)	True	2-6 titirangi	2-6 titirangi rd new lynn	4882242	[10 titirangi]	[10 Titirangi Road, New Lynn, Auckland]	7.0	0.0
217701	151508.0	2014-07-01	2015-03-01	433-435	HOBSONVILLE RD	HOBSONVILLE	Upper Harbour	New (and pre-built) house, unit, bach, crib, b...	1100.0	206.0	288680.0	1.0	Residential	Detached	NaN	NaN	1998.0	221907.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_16	Special Area	Special	NaN	Scott Point, Sunderland Precinct, Hobsonville ...	Scott Point, Sunderland Precinct, Hobsonville ...	174.654389	-36.794236	POINT (1747602.554 5926676.720)	True	433-435 hobsonville	433-435 hobsonville rd hobsonville	7447549	[70 hobsonville]	[70 Hobsonville Point Road, Hobsonville, Auckl...	7.0	0.0
217852	151357.0	2014-07-01	2015-03-01	9-11	FENTON ST	EDEN TERRACE	Waitemata	New Flats	1200.0	3760.0	3000000.0	37.0	Residential	Attached	NaN	NaN	1998.0	419702.0	2013.0	Business - Mixed Use Zone	Business	12.0	ACC_10	Business MU	Business	Commercial	NaN	NaN	174.757447	-36.868315	POINT (1756646.875 5918294.000)	True	9-11 fenton	9-11 fenton st eden terrace	7523952	[501/11 fenton, 401/11 fenton, 402/11 fenton, ...	[501/11 Fenton Street, Mount Eden, Auckland, 4...	7.0	0.0
217854	151355.0	2014-07-01	2015-03-01	138A-138C	GOWING DR	MEADOWBANK	Orakei	New (and pre-built) house, unit, bach, crib, b...	1100.0	214.0	390000.0	1.0	Residential	Detached	NaN	NaN	1998.0	461300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.838299	-36.867962	POINT (1763854.986 5918197.478)	True	138a-138c gowing	138a-138c gowing dr meadowbank	7533935	NaN	NaN	8.0	0.0
217857	151352.0	2014-07-01	2015-03-01	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.813 5920152.000)	True	164-168 hobson	164-168 hobson st auckland	6752833	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	7.0	0.0

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE	distance_to_parcel	LINZ_2ND_MATCH_CODE
0	244368.0	2021-12-01	2021-03-01	14 TE ARA O KIRI PATUPARAOA ANAKE	Tomarata	Auckland	Rodney	Houses	1111.0	399.0	1100000.0	1.0	Residential	Detached	NaN	NaN	2014.0	135300.0	2013.0	Rural - Rural Coastal Zone	Rural	46.0	RDC_15	Landscape Protection Rur	Rural	NaN	NaN	NaN	174.658935	-36.188787	POINT (1749165.061 5993836.774)	False	14 te	14 te ara o kiri patuparaoa anake tomarata auc...	8201470	[14 te]	[14 Te Ara O Kiri Patuparaoa Anake, Te Arai]	2.0	337.06269	NaN
1	244369.0	2021-12-01	2021-03-01	TE ARA O KIRI PATUPARAOA ANAKE	Tomarata	Auckland	Rodney	Restaurants, bars, and cafes	2512.0	134.0	1500000.0	1.0	Business	NaN	Group 2	Hospitality	2014.0	135300.0	2013.0	Rural - Rural Coastal Zone	Rural	46.0	RDC_15	Landscape Protection Rur	Rural	NaN	NaN	NaN	174.661091	-36.189144	POINT (1749358.254 5993793.842)	False	te ara	te ara o kiri patuparaoa anake tomarata auckland	8201477	NaN	NaN	8.0	0.00000	NaN
2	244451.0	2021-12-01	2021-03-01	58 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111.0	224.0	770000.0	1.0	Residential	Detached	NaN	NaN	2014.0	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.663924	-36.584054	POINT (1748858.010 5949979.809)	False	58 paparahi	58 paparahi place upper orewa auckland	8156563	NaN	NaN	8.0	0.00000	NaN
3	244452.0	2021-12-01	2021-03-01	44 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111.0	247.0	670000.0	1.0	Residential	Detached	NaN	NaN	2014.0	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662942	-36.585117	POINT (1748768.145 5949863.416)	False	44 paparahi	44 paparahi place upper orewa auckland	8156550	NaN	NaN	8.0	0.00000	NaN
4	244453.0	2021-12-01	2021-03-01	32 PAPARAHI PLACE	Upper Orewa	Auckland	Rodney	Houses	1111.0	252.0	550000.0	1.0	Residential	Detached	NaN	NaN	2014.0	176406.0	2013.0	Future Urban Zone	New growth	4.0	RDC_35	Special 11	Special	NaN	NaN	NaN	174.662988	-36.585185	POINT (1748772.124 5949855.789)	False	32 paparahi	32 paparahi place upper orewa auckland	8156550	NaN	NaN	8.0	0.00000	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
151357.0_11	151357.0	2014-07-01	2015-03-01	9-11	FENTON ST	EDEN TERRACE	Waitemata	New Flats	1200.0	3760.0	3000000.0	37.0	Residential	Attached	NaN	NaN	1998.0	419702.0	2013.0	Business - Mixed Use Zone	Business	12.0	ACC_10	Business MU	Business	Commercial	NaN	NaN	174.757447	-36.868315	POINT (1756646.875 5918294.000)	True	11 fenton	9-11 fenton st eden terrace	7523952.0	[501/11 fenton, 401/11 fenton, 402/11 fenton, ...	[501/11 Fenton Street, Mount Eden, Auckland, 4...	1.0	0.00000	1.0
151355.0_138	151355.0	2014-07-01	2015-03-01	138A-138C	GOWING DR	MEADOWBANK	Orakei	New (and pre-built) house, unit, bach, crib, b...	1100.0	214.0	390000.0	1.0	Residential	Detached	NaN	NaN	1998.0	461300.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.838299	-36.867962	POINT (1763854.986 5918197.478)	True	138 gowing	138a-138c gowing dr meadowbank	7445958	[138 gowing]	[138 Gowing Drive, Meadowbank, Auckland]	2.0	0.00000	2.0
151352.0_164	151352.0	2014-07-01	2015-03-01	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.813 5920152.000)	True	164 hobson	164-168 hobson st auckland	6752833.0	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	1.0	0.00000	1.0
151352.0_166	151352.0	2014-07-01	2015-03-01	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.813 5920152.000)	True	166 hobson	164-168 hobson st auckland	6752833.0	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	1.0	0.00000	1.0
151352.0_168	151352.0	2014-07-01	2015-03-01	164-168	HOBSON ST	AUCKLAND	Waitemata	New Flats	1200.0	0.0	3650000.0	0.0	Residential	Attached	NaN	NaN	1998.0	434200.0	2013.0	Business - City Centre Zone	Business	35.0	CBD_1	Central Area A	Business	Commercial	NaN	NaN	174.760314	-36.851526	POINT (1756936.813 5920152.000)	True	168 hobson	164-168 hobson st auckland	6752833.0	[37 cook, 1305/168 hobson, 1301/168 hobson, 16...	[37 Cook Street, Auckland Central, Auckland, 1...	10.0	0.00000	1.0

	place_id	licence	osm_type	osm_id	boundingbox	lat	lon	display_name	place_rank	category	type	importance
1	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
5	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...
250918	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300
250922	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300
250923	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300
250924	2244421.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	7.638381e+06	[-36.8684516, -36.8681022, 174.7563297, 174.75...	-36.8682559	174.7569026	Fenton Street, Eden Terrace, Auckland, Waitema...	26.0	highway	unclassified	0.500
250929	2192207.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	node	8.654487e+09	[-36.8514673, -36.8513673, 174.7602459, 174.76...	-36.8514173	174.7602959	168, Hobson Street, Aotea Arts Quarter, Auckla...	30.0	place	house	0.411

	place_id	licence	osm_type	osm_id	boundingbox	lat	lon	display_name	place_rank	category	type	importance	geometry
16	2524131.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	9.121949e+08	[-36.8341713, -36.8334755, 174.5952555, 174.59...	-36.8339809	174.5957606	Horokaka Street, Cardinal West, Henderson-Mass...	26.0	highway	construction	0.500	POINT (1742297.820 5922356.275)
17	753338.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	node	5.479883e+09	[-36.8558663, -36.8557663, 174.8102533, 174.81...	-36.8558163	174.8103033	1/20, Ngapipi Road, Orakei, Ōrākei, Auckland, ...	30.0	place	house	0.501	POINT (1761384.895 5919592.687)
25	2214185.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	5.449970e+07	[-37.2085343, -37.2040983, 174.8971405, 174.89...	-37.2060501	174.8973834	Wellington Street, Rosa Birch Park, Pukekohe, ...	26.0	highway	residential	0.500	POINT (1768373.424 5880583.280)
44	2467967.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.249247e+08	[-36.957088, -36.9570348, 174.4797794, 174.479...	-36.9570557	174.4798123	Piha Road, Piha, Waitākere Ranges, Auckland, N...	26.0	highway	secondary	0.500	POINT (1731745.987 5908868.559)
50	2331561.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	9.406398e+07	[-36.3798636, -36.3456343, 174.7328525, 174.83...	-36.3736053	174.7789914	Takatu Road, Takatu, Campbells Beach, Rodney, ...	26.0	highway	tertiary	0.400	POINT (1759584.267 5973142.421)
...	...	...	...	...	...	...	...	...	...	...	...	...	...
250918	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300	POINT (1746183.658 5925616.600)
250922	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300	POINT (1746183.658 5925616.600)
250923	2500994.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	6.617114e+08	[-36.804097, -36.8039616, 174.6385867, 174.638...	-36.8040093	174.6386947	Hobsonville Road, Hobsonville, Upper Harbour, ...	26.0	highway	secondary	0.300	POINT (1746183.658 5925616.600)
250924	2244421.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	way	7.638381e+06	[-36.8684516, -36.8681022, 174.7563297, 174.75...	-36.8682559	174.7569026	Fenton Street, Eden Terrace, Auckland, Waitema...	26.0	highway	unclassified	0.500	POINT (1756598.507 5918301.495)
250929	2192207.0	Data © OpenStreetMap contributors, ODbL 1.0. h...	node	8.654487e+09	[-36.8514673, -36.8513673, 174.7602459, 174.76...	-36.8514173	174.7602959	168, Hobson Street, Aotea Arts Quarter, Auckla...	30.0	place	house	0.411	POINT (1756935.430 5920164.089)

	index	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	Ranged_Address_indicator	number_name	full_address	LINZ_parcel_ID	addresses	full_addresses	LINZ_MATCH_CODE	distance_to_parcel	LINZ_2ND_MATCH_CODE	distance_from_geocoding_result
239183	112.0_137	112.0	1991-04-01	1992-04-01	137-139	SOLAR RD	GLEN EDEN	Waitakere Ranges	New (and pre-built) house, unit, bach, crib, b...	1100.0	97.0	60685.0	1.0	Residential	Detached	NaN	NaN	1998.0	278406.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.630125	-36.914665	POINT (1745209.589 5913353.159)	True	137 solar	137 solar rd glen eden	5118671.0	[139 solar]	[139 Solar Road, Glen Eden, Auckland]	10.0	0.000000	1.0	NaN
239184	112.0_139	112.0	1991-04-01	1992-04-01	137-139	SOLAR RD	GLEN EDEN	Waitakere Ranges	New (and pre-built) house, unit, bach, crib, b...	1100.0	97.0	60685.0	1.0	Residential	Detached	NaN	NaN	1998.0	278406.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.630125	-36.914665	POINT (1745209.589 5913353.159)	True	139 solar	139 solar rd glen eden	5118671.0	[139 solar]	[139 Solar Road, Glen Eden, Auckland]	1.0	0.000000	1.0	NaN
239181	128.0_109	128.0	1991-04-01	1992-04-01	109-111	HUTCHINSON AVE	NEW LYNN	Whau	New Flats	1200.0	85.0	85000.0	1.0	Residential	Attached	NaN	NaN	1998.0	262001.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	New Lynn Strategic Area	New Lynn - Strategic Area	174.671724	-36.918903	POINT (1748907.187 5912818.837)	True	109 hutchinson	109 hutchinson ave new lynn	4852262	[109 hutchinson]	[109 Hutchinson Avenue, New Lynn, Auckland]	2.0	253.056336	2.0	NaN
239182	128.0_111	128.0	1991-04-01	1992-04-01	109-111	HUTCHINSON AVE	NEW LYNN	Whau	New Flats	1200.0	85.0	85000.0	1.0	Residential	Attached	NaN	NaN	1998.0	262001.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	New Lynn Strategic Area	New Lynn - Strategic Area	174.671724	-36.918903	POINT (1748907.187 5912818.837)	True	111 hutchinson	111 hutchinson ave new lynn	5234122	NaN	NaN	10.0	0.000000	2.0	660.400258
239179	282.0_11	282.0	1991-04-01	1992-04-01	11-13	MARION AVE	MT ROSKILL	Puketapapa	New (and pre-built) house, unit, bach, crib, b...	1100.0	123.0	134156.0	1.0	Residential	Detached	NaN	NaN	1998.0	579300.0	2013.0	Business - Light Industry Zone	Business	17.0	ACC_4	Business 4	Business	Commercial	NaN	NaN	174.730030	-36.910799	POINT (1754117.441 5913625.292)	True	11 marion	11 marion ave mt roskill	4928143	[11 marion, 1/11 marion, 2/11 marion]	[11 Marion Avenue, Mount Roskill, Auckland, 1/...	2.0	164.830467	2.0	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
212116	245579.0_1	245579.0	2021-12-01	2021-03-01	1-9 CROWS ROAD	Swanson	Auckland	Waitakere Ranges	Houses	1111.0	272.0	762000.0	1.0	Residential	Detached	NaN	NaN	2014.0	226602.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_16	Special Area	Special	NaN	Crows Road, Swanson	Crows Road, Swanson	174.595270	-36.854353	POINT (1742216.310 5920096.902)	True	1 crows	1 crows road swanson auckland	4777571	[7a crows, 7 crows, 9 crows, 5a crows, 9a crow...	[7A Crows Road, Swanson, Auckland, 7 Crows Roa...	1.0	0.000000	1.0	NaN
212117	245579.0_3	245579.0	2021-12-01	2021-03-01	1-9 CROWS ROAD	Swanson	Auckland	Waitakere Ranges	Houses	1111.0	272.0	762000.0	1.0	Residential	Detached	NaN	NaN	2014.0	226602.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_16	Special Area	Special	NaN	Crows Road, Swanson	Crows Road, Swanson	174.595270	-36.854353	POINT (1742216.310 5920096.902)	True	3 crows	3 crows road swanson auckland	4777571	[7a crows, 7 crows, 9 crows, 5a crows, 9a crow...	[7A Crows Road, Swanson, Auckland, 7 Crows Roa...	1.0	0.000000	1.0	NaN
212118	245579.0_5	245579.0	2021-12-01	2021-03-01	1-9 CROWS ROAD	Swanson	Auckland	Waitakere Ranges	Houses	1111.0	272.0	762000.0	1.0	Residential	Detached	NaN	NaN	2014.0	226602.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_16	Special Area	Special	NaN	Crows Road, Swanson	Crows Road, Swanson	174.595270	-36.854353	POINT (1742216.310 5920096.902)	True	5 crows	5 crows road swanson auckland	4777571	[7a crows, 7 crows, 9 crows, 5a crows, 9a crow...	[7A Crows Road, Swanson, Auckland, 7 Crows Roa...	1.0	0.000000	1.0	NaN
212119	245579.0_7	245579.0	2021-12-01	2021-03-01	1-9 CROWS ROAD	Swanson	Auckland	Waitakere Ranges	Houses	1111.0	272.0	762000.0	1.0	Residential	Detached	NaN	NaN	2014.0	226602.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_16	Special Area	Special	NaN	Crows Road, Swanson	Crows Road, Swanson	174.595270	-36.854353	POINT (1742216.310 5920096.902)	True	7 crows	7 crows road swanson auckland	4777571	[7a crows, 7 crows, 9 crows, 5a crows, 9a crow...	[7A Crows Road, Swanson, Auckland, 7 Crows Roa...	1.0	0.000000	1.0	NaN
212120	245579.0_9	245579.0	2021-12-01	2021-03-01	1-9 CROWS ROAD	Swanson	Auckland	Waitakere Ranges	Houses	1111.0	272.0	762000.0	1.0	Residential	Detached	NaN	NaN	2014.0	226602.0	2013.0	Residential - Single House Zone	Residential	19.0	WCC_16	Special Area	Special	NaN	Crows Road, Swanson	Crows Road, Swanson	174.595270	-36.854353	POINT (1742216.310 5920096.902)	True	9 crows	9 crows road swanson auckland	4777571	[7a crows, 7 crows, 9 crows, 5a crows, 9a crow...	[7A Crows Road, Swanson, Auckland, 7 Crows Roa...	1.0	0.000000	1.0	NaN

	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	LINZ_parcel_centroid_lon	LINZ_parcel_centroid_lat	LINZ_parcel_vertices_lon	LINZ_parcel_vertices_lat	LINZ_parcel_roadvertices_lon	LINZ_parcel_roadvertices_lat	LINZmatch_AUP_code	LINZmatch_AUP_name	LINZ_adjoining_parcel_ID	LINZ_parcel_sides_zones	LINZ_TRNSPWR_ohead_name	LINZ_TRNSPWR_ohead_indicator	LINZ_VWSHFT_ohead_name	LINZ_VWSHFT_ohead_ID	LINZ_VWSHFT_ohead_indicator	Hdist_rural	Hdist_rural_code	Hdist_rural_name	Hdist_bus	Hdist_bus_code	Hdist_bus_name	Hdist_resid	Hdist_resid_code	Hdist_resid_name	Hdist_SH	Hdist_MHS	Hdist_MHU	Hdist_THA	Local_Area_name	SA22018_name	SA22018_code	AU2013_name	AU2013_code	MB2018_code	MB2013_code	Hdist_coast	Hdist_motorway	Hdist_main_road	Hdist_rail	Hdist_skytower	SpHA_indicator
LINZ_parcel_ID
4918991	Lot 327 DP 56341	DP 56341	DCDB	Primary	NaN	North Auckland	NA8C/911	642.0	643.0	174.843218	-36.876042	[174.842994; 174.843152; 174.84337; 174.843471...	[-36.875922; -36.875957; -36.876006; -36.87602...	[174.842994; 174.842997]	[-36.875922; -36.876074]	18.0	Residential - Mixed Housing Suburban Zone	[5061331, 5002265, 5228435, 5061335, 4722548, ...	['18', '18', '27', '18', '18', '18']	NaN	0	NaN	NaN	0	7228.891309	3	Rural - Countryside Living Zone	96.953993	44	Business - Neighbourhood Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1499.258244	0.000000	463.752596	342.351869	Orakei	Saint Johns East	144500.0	St Johns	517201	4006757.0	465102	1626.866018	3904.389673	30.431054	675.367028	7850.889144	False
4919914	Lot 178 DP 56342	DP 56342	DCDB	Primary	NaN	North Auckland	474924, NA8C/768	650.0	651.0	174.846572	-36.873551	[174.84645; 174.846486; 174.846701; 174.846648]	[-36.873699; -36.873386; -36.873407; -36.873721]	[174.846486; 174.846701]	[-36.873386; -36.873407]	18.0	Residential - Mixed Housing Suburban Zone	[4789870, 4877424, 5005927, 4804873, 5178022, ...	['18', '18', '18', '18', '18', '27']	NaN	0	NaN	NaN	0	7571.289947	3	Rural - Countryside Living Zone	219.321245	17	Business - Light Industry Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1504.842901	0.000000	380.053451	411.291837	Orakei	Saint Johns East	144500.0	St Johns	517201	4006774.0	464800	1666.931163	4307.322450	275.948865	296.455456	8026.797757	False
4926734	Lot 144 DP 53562	DP 53562	DCDB	Primary	NaN	North Auckland	386353, 386354, 386355, 386356, NA4D/859	640.0	640.0	174.845299	-36.873269	[174.845069; 174.845157; 174.845513; 174.84546]	[-36.87326; -36.873123; -36.87327; -36.873421]	[174.845069; 174.845157]	[-36.87326; -36.873123]	18.0	Residential - Mixed Housing Suburban Zone	[5236861, 5048379, 4926945, 4797737]	['27', '18', '18', '18']	NaN	0	NaN	NaN	0	7574.099005	3	Rural - Countryside Living Zone	197.076711	44	Business - Neighbourhood Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1551.382113	0.000000	336.510990	468.042305	Orakei	Saint Johns East	144500.0	St Johns	517201	4006774.0	464800	1556.501519	4232.755849	170.250936	317.577857	7909.576996	False
4926945	Lot 183 DP 56342	DP 56342	DCDB	Primary	NaN	North Auckland	433646, NA8C/773	913.0	914.0	174.845585	-36.873350	[174.845775; 174.84557; 174.845408; 174.84546;...	[-36.873177; -36.873603; -36.87357; -36.873421...	[174.845775; 174.845575; 174.845689]	[-36.873177; -36.873095; -36.873142]	18.0	Residential - Mixed Housing Suburban Zone	[5233326, 5048379, 4797737, 5054505, 7342193, ...	['27', '18', '18', '18', '18', '18', '18', '18']	NaN	0	NaN	NaN	0	7556.977156	3	Rural - Countryside Living Zone	180.285687	44	Business - Neighbourhood Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1529.480918	0.000000	333.915495	445.926909	Orakei	Saint Johns East	144500.0	St Johns	517201	4006774.0	464800	1582.400173	4248.261840	195.278997	314.544307	7936.587996	False
4929034	Lot 260 DP 56341	DP 56341	DCDB	Primary	NaN	North Auckland	488577, NA8C/850	622.0	624.0	174.847627	-36.874955	[174.847427; 174.847674; 174.847826; 174.84757...	[-36.875045; -36.874775; -36.874864; -36.87513...	[174.847674; 174.847826]	[-36.874775; -36.874864]	18.0	Residential - Mixed Housing Suburban Zone	[4860765, 5186671, 5186670, 5057572, 5262615]	['18', '18', '18', '18', '27']	NaN	0	NaN	NaN	0	7452.133541	3	Rural - Countryside Living Zone	162.913359	17	Business - Light Industry Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1580.494775	0.000000	489.570672	263.495431	Orakei	Saint Johns East	144500.0	St Johns	517201	464600.0	464600	1834.490817	4299.136620	434.784957	395.754035	8169.879981	False
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
5227472	NaN	NaN	Road	Primary	NaN	North Auckland	NaN	NaN	1330.0	174.874361	-36.959668	[174.875038; 174.874961; 174.874884; 174.87480...	[-36.959943; -36.959973; -36.960003; -36.96003...	[174.875038; 174.874961; 174.874884; 174.87480...	[-36.959943; -36.959973; -36.960003; -36.96003...	27.0	Road	[5214220, 4906751, 5142410, 4891625, 5256195, ...	['27', '17', '17', '17', '27', '17', '17', '17...	NaN	0	NaN	NaN	0	3963.447367	3	Rural - Countryside Living Zone	0.000000	17	Business - Light Industry Zone	86.562395	8	Residential - Terrace Housing and Apartment Bu...	760.946689	239.919686	387.444562	86.562395	Otara	Otara Central	154400.0	Otara North	523501	700602.0	700602	983.514550	658.806820	45.095107	2934.237955	15888.342992	False
5242816	NaN	NaN	Road	Primary	NaN	North Auckland	NaN	NaN	1049.0	174.872269	-36.958674	[174.872009; 174.872304; 174.872476; 174.87240...	[-36.958681; -36.958453; -36.958569; -36.95863...	[174.872009; 174.872304; 174.872476; 174.87240...	[-36.958681; -36.958453; -36.958569; -36.95863...	27.0	Road	[4733845, 5231272, 4866075, 4785948, 5234594, ...	['64', '27', '33', '32', '27', '27', '8']	NaN	0	NaN	NaN	0	4211.055507	3	Rural - Countryside Living Zone	82.178171	17	Business - Light Industry Zone	0.000000	8	Residential - Terrace Housing and Apartment Bu...	785.375433	262.434622	328.712745	0.000000	Otara	Otara Central	154400.0	Otara North	523501	700601.0	700601	801.720167	611.330344	5.025442	2831.983258	15685.595260	False
7520053	Lot 5 DP 465319	DP 465319	Fee Simple Title	Primary	NaN	North Auckland	621250	2253.0	2252.0	174.875600	-36.959503	[174.875695; 174.875801; 174.876171; 174.87630...	[-36.959428; -36.959472; -36.959629; -36.95968...	[174.875149; 174.875145; 174.875141; 174.87513...	[-36.959472; -36.959456; -36.95944; -36.959424...	17.0	Business - Light Industry Zone	[5214220, 7520050, 7520049]	['27', '17', '17']	['Otahuhu - Whakamaru C']	1	NaN	NaN	0	3911.904824	3	Rural - Countryside Living Zone	0.000000	17	Business - Light Industry Zone	150.152069	8	Residential - Terrace Housing and Apartment Bu...	845.357322	197.645867	386.422868	150.152069	Otara	Otara Central	154400.0	Otara North	523501	700602.0	700602	905.449427	757.841845	137.798477	3038.063157	15943.954833	False
7520049	Lot 1 DP 465319	DP 465319	Fee Simple Title	Primary	NaN	North Auckland	619244	13064.0	13062.0	174.876401	-36.959399	[174.875695; 174.875635; 174.875514; 174.87543...	[-36.959428; -36.959402; -36.959351; -36.95931...	[174.875187; 174.875207; 174.875323; 174.87533...	[-36.959212; -36.959182; -36.959006; -36.95899...	17.0	Business - Light Industry Zone	[7520050, 5214220, 7520053, 7520052]	['17', '27', '17', '17']	['Otahuhu - Whakamaru C']	1	NaN	NaN	0	3808.115618	3	Rural - Countryside Living Zone	0.000000	17	Business - Light Industry Zone	139.072671	18	Residential - Mixed Housing Suburban Zone	870.886671	139.072671	274.592516	143.276761	Otara	Otara Central	154400.0	Otara North	523501	700602.0	700602	859.190377	823.461575	202.451909	3105.040962	15980.395818	False
4733845	Part Lot 59 DP 60001	DP 60001	DCDB	Primary	[Referenced] Declaration That State Housing La...	North Auckland	NA86D/103	5449.0	5447.0	174.871298	-36.958743	[174.870701; 174.870815; 174.870818; 174.87082...	[-36.958917; -36.958509; -36.9585; -36.958492;...	[174.870701; 174.870815; 174.870818; 174.87082...	[-36.958917; -36.958509; -36.9585; -36.958492;...	64.0	Special Purpose - Tertiary Education Zone	[5187557, 4758156, 5254506, 4866075, 5225006, ...	['33', '64', '27', '33', '27', '27', '27', '27']	NaN	0	NaN	NaN	0	4250.207218	3	Rural - Countryside Living Zone	124.492091	17	Business - Light Industry Zone	20.115492	8	Residential - Terrace Housing and Apartment Bu...	751.195267	304.268175	310.515280	20.115492	Otara	Otara Central	154400.0	Otara North	523501	700700.0	700700	769.844893	561.066825	37.791191	2753.967151	15637.574122	False

Phase 1: Residential Building Consent Dataset¶

INPUTS¶

OUTPUTS¶