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


# 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 matplotlib_scalebar.scalebar import ScaleBar # scalebar for plot
import matplotlib.pyplot as plt # plotting
from tqdm.auto import tqdm # progress bars
tqdm.pandas()
import json
from scipy.spatial import cKDTree
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)


ls()

Total: 2152.0MB


ls("restricted")

Total: 897.0MB


df = pd.read_csv("restricted/QPIDs_Auckland_1990_2020.csv")
df = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.CL_Longitude, df.CL_Latitude), crs="EPSG:4326")
df.index = df.CL_QPID_output2
df


print(sum(pd.isna(df.CL_Longitude)))
df = df.dropna()
assert df.CL_QPID_output2.is_unique
print(len(df))

7178
376094


%%time
parcels = gpd.read_file('input/lds-nz-primary-parcels-FGDB.zip!nz-primary-parcels.gdb')
parcels = parcels.to_crs(df.crs)
parcels = parcels.set_index("id")
parcels["parcel_geometry"] = parcels.geometry

CPU times: user 42.2 s, sys: 1.01 s, total: 43.2 s
Wall time: 43.5 s


parcels


%%time
df = gpd.sjoin(df, parcels, how="left")

CPU times: user 40.9 s, sys: 1.09 s, total: 42 s
Wall time: 42.3 s


df.parcel_intent.value_counts()

DCDB                                                                 275339
Fee Simple Title                                                     100655
Legalisation                                                             79
Road                                                                     16
Vesting on Deposit for Local Purpose Reserve                              3
Vesting on Deposit in Lieu of a Reserve (Territorial Authority)           1
Vesting on Deposit for Recreation Reserve (Territorial Authority)         1
Name: parcel_intent, dtype: int64


# Roads shouldn't be in this dataset - might have to give these 3 points a little nudge


sold_roads = df[df.parcel_intent == "Road"]
for i in range(len(sold_roads)):
    sold_road = sold_roads.iloc[i:i+1]
    display(sold_road)
    ax = sold_road.parcel_geometry.to_crs(epsg=3857).plot(alpha=.5)
    sold_road.parcel_geometry.centroid.to_crs(epsg=3857).plot(ax=ax, color="red")
    sold_road.to_crs(epsg=3857).plot(ax=ax, color="green")
    ctx.add_basemap(ax, source=ctx.providers.Esri.WorldImagery, zoom=21 if i == 0 else "auto")
    ax.set_title("Red = parcel centroid, green = QPID latlong")
    plt.show()

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

<string>:6: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.


df.at[258637, "parcel_intent"] = "Glitch?"


print(f"{1e-4} degrees equates to {df.loc[[1528194]].to_crs(epsg=2193).distance(df.loc[[1528194]].translate(yoff=-1e-4).to_crs(epsg=2193)).iloc[0]} meters")

0.0001 degrees equates to 11.09551261419352 meters


problems = [1528194, 1779952]
display(df.loc[problems])
# Nudge point south a little bit
df.geometry[1528194] = df.loc[[1528194]].translate(yoff=-1e-4)
# Nudge point west a little bit
df.geometry[1779952] = df.loc[[1779952]].translate(xoff=-1e-4)

# Redo the join for these newly adjusted points
subset = df.loc[df.index.isin(problems), ["CL_Longitude", "CL_Latitude", "QPID_vintage", "geometry"]]
df.loc[problems] = gpd.sjoin(subset, parcels)
display(df.loc[problems])


df.parcel_intent.value_counts()

DCDB                                                                 275341
Fee Simple Title                                                     100655
Legalisation                                                             79
Road                                                                     13
Vesting on Deposit for Local Purpose Reserve                              3
Glitch?                                                                   1
Vesting on Deposit in Lieu of a Reserve (Territorial Authority)           1
Vesting on Deposit for Recreation Reserve (Territorial Authority)         1
Name: parcel_intent, dtype: int64


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


sample_parcels = parcels.cx[174.782:174.783, -36.870:-36.871]


ax = sample_parcels.to_crs(epsg=3857).plot(column="parcel_intent", legend=True, alpha=.5, categorical=True, edgecolor="black")
sample_parcels.centroid.to_crs(epsg=3857).plot(ax=ax, color="red")
df[df.LINZ_parcel_ID.isin(sample_parcels.index)].to_crs(epsg=3857).plot(ax=ax, color="green")
ctx.add_basemap(ax, source=ctx.providers.Esri.WorldImagery)
plt.title("Red = parcel centroid, green = QPID latlong")

<string>:2: UserWarning: Geometry is in a geographic CRS. Results from 'centroid' are likely incorrect. Use 'GeoSeries.to_crs()' to re-project geometries to a projected CRS before this operation.

Text(0.5, 1.0, 'Red = parcel centroid, green = QPID latlong')


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

CPU times: user 12.3 s, sys: 1.27 s, total: 13.6 s
Wall time: 14.2 s


%%time
df = df[["CL_QPID_output2", "CL_Longitude", "CL_Latitude", "QPID_vintage", "LINZ_parcel_ID"]].merge(phase0, on="LINZ_parcel_ID", how="left")
df

CPU times: user 10.6 s, sys: 1.6 s, total: 12.2 s
Wall time: 12.4 s


df.keys()

Index(['CL_QPID_output2', 'CL_Longitude', 'CL_Latitude', 'QPID_vintage',
       'LINZ_parcel_ID', '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/QPIDs_Auckland_1990_2020_augmented.csv")

	name	filesize (MB)	last modified
0	2013-mb-dataset-Total-New-Zealand-Household.csv	37.12	2014-06-04 10:56:30.000000
1	2013-mb-dataset-Total-New-Zealand-Individual-Part-1.csv	31.66	2014-04-01 10:13:15.197000
2	2013_census_mean_income_by_AU2013.xlsx	0.59	2021-09-07 06:21:24.000000
3	2016_aup.zip	1192.41	2021-10-14 14:58:54.110000
4	2018-census-electoral-population-meshblock-2020-data.csv	5.60	2021-08-09 00:39:18.000000
5	2018_census_dwellings_by_SA2.xlsx	0.43	2021-07-12 14:30:28.000000
6	AC_Special_Housing_Area.zip	0.29	2021-09-02 10:36:16.760000
7	AUP_viewshafts.zip	0.21	2021-09-22 10:08:05.233857
8	AucklandArea.gpkg	0.09	2021-09-02 10:36:17.220000
9	Geocoordinates_Direct_Transit_stops_AKL.xlsx	0.01	2020-10-08 07:52:29.000000
10	Individual_part1_totalNZ-wide_format_updated_16-7-20.csv	36.58	2020-07-14 16:12:24.000000
11	MASTER_UP_BaseZone_SHP.zip	66.92	2021-07-19 02:23:51.137347
12	Modified_Community_Boards_SHP.zip	1.30	2021-07-19 02:16:07.650000
13	Transmission_Lines_exTRANSPOWER.zip	0.50	2021-09-22 09:31:01.376235
14	area-unit-2013.gdb.zip	14.21	2021-09-02 10:36:16.070000
15	kx-nz-state-highway-on-ramps-off-ramps-SHP.zip	0.14	2021-08-25 09:52:06.341291
16	lds-nz-coastline-mean-high-water-FGDB.zip	4.88	2021-08-31 16:25:16.250000
17	lds-nz-coastlines-and-islands-polygons-topo-150k-FGDB.zip	4.25	2021-08-05 15:57:49.020000
18	lds-nz-primary-parcels-CLIPPED-4326.gpkg	273.35	2021-09-02 10:37:32.410000
19	lds-nz-primary-parcels-FGDB.zip	78.05	2021-11-04 15:56:52.736022
20	lds-nz-railway-centrelines-topo-150k-SHP.zip	0.87	2021-08-31 10:01:46.315326
21	lds-nz-road-centrelines-topo-150k-FGDB.zip	36.06	2021-08-31 09:43:07.931704
22	lds-nz-street-address-FGDB.zip	30.48	2021-11-04 15:53:13.331299
23	meshblock-2013.gdb.zip	106.57	2021-09-02 10:36:59.860000
24	meshblock-2018-clipped-generalised.gdb.zip	32.83	2021-09-02 10:36:26.010000
25	nz-primary-parcels.gdb.zip	55.00	2021-09-02 10:36:39.020000
26	statsnzarea-unit-2013-FGDB.zip	13.79	2021-08-31 16:56:17.150000
27	statsnzmeshblock-higher-geographies-2018-generalised-FGDB.zip	34.54	2021-08-05 14:53:54.350000
28	statsnzpopulation-by-meshblock-2013-census-FGDB.zip	82.11	2021-07-19 13:53:55.150631
29	statsnzstatistical-area-2-higher-geographies-2018-clipped-generalis-FGDB.zip	10.72	2021-08-30 17:12:55.591895

	name	filesize (MB)	last modified
0	BCs_issued_by_AUP_TLADCs_2021FEB.csv	64.34	2021-04-13 11:52:08.000
1	BCs_issued_by_AUP_TLADCs_2021FEB_augmented.csv	493.06	2021-11-08 15:12:03.410
2	QPIDs_Auckland_1990_2020.csv	17.40	2021-07-07 13:09:39.000
3	QPIDs_Auckland_1990_2020_augmented.csv	277.36	2021-09-22 20:00:13.410
4	road_intersections.pkl	44.84	2021-09-22 10:50:09.480

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry
CL_QPID_output2
75494	75494	174.588941	-36.186076	2020	POINT (174.58894 -36.18608)
75499	75499	174.581811	-36.200345	2020	POINT (174.58181 -36.20034)
75639	75639	174.496590	-36.228740	2020	POINT (174.49659 -36.22874)
75640	75640	174.498361	-36.228827	2020	POINT (174.49836 -36.22883)
75654	75654	174.493736	-36.229990	2020	POINT (174.49374 -36.22999)
...	...	...	...	...	...
3160123	3160123	174.728112	-36.408104	2018	POINT (174.72811 -36.40810)
3160124	3160124	174.728164	-36.408113	2018	POINT (174.72816 -36.40811)
3160630	3160630	174.655992	-36.509925	2018	POINT (174.65599 -36.50992)
3164546	3164546	174.537978	-36.774834	2018	POINT (174.53798 -36.77483)
3166852	3166852	174.644137	-36.854796	2018	POINT (174.64414 -36.85480)

	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	geometry	parcel_geometry
id
4918991	Lot 327 DP 56341	DP 56341	DCDB	Primary	None	North Auckland	NA8C/911	642.0	643.0	MULTIPOLYGON (((174.84299 -36.87592, 174.84315...	MULTIPOLYGON (((174.84299 -36.87592, 174.84315...
4919914	Lot 178 DP 56342	DP 56342	DCDB	Primary	None	North Auckland	474924, NA8C/768	650.0	651.0	MULTIPOLYGON (((174.84645 -36.87370, 174.84649...	MULTIPOLYGON (((174.84645 -36.87370, 174.84649...
4926734	Lot 144 DP 53562	DP 53562	DCDB	Primary	None	North Auckland	386353, 386354, 386355, 386356, NA4D/859	640.0	640.0	MULTIPOLYGON (((174.84507 -36.87326, 174.84516...	MULTIPOLYGON (((174.84507 -36.87326, 174.84516...
4926945	Lot 183 DP 56342	DP 56342	DCDB	Primary	None	North Auckland	433646, NA8C/773	913.0	914.0	MULTIPOLYGON (((174.84578 -36.87318, 174.84557...	MULTIPOLYGON (((174.84578 -36.87318, 174.84557...
4929034	Lot 260 DP 56341	DP 56341	DCDB	Primary	None	North Auckland	488577, NA8C/850	622.0	624.0	MULTIPOLYGON (((174.84743 -36.87505, 174.84767...	MULTIPOLYGON (((174.84743 -36.87505, 174.84767...
...	...	...	...	...	...	...	...	...	...	...	...
5227472	None	None	Road	Primary	None	North Auckland	None	NaN	1330.0	MULTIPOLYGON (((174.87504 -36.95994, 174.87496...	MULTIPOLYGON (((174.87504 -36.95994, 174.87496...
5242816	None	None	Road	Primary	None	North Auckland	None	NaN	1049.0	MULTIPOLYGON (((174.87201 -36.95868, 174.87230...	MULTIPOLYGON (((174.87201 -36.95868, 174.87230...
7520053	Lot 5 DP 465319	DP 465319	Fee Simple Title	Primary	None	North Auckland	621250	2253.0	2252.0	MULTIPOLYGON (((174.87570 -36.95943, 174.87580...	MULTIPOLYGON (((174.87570 -36.95943, 174.87580...
7520049	Lot 1 DP 465319	DP 465319	Fee Simple Title	Primary	None	North Auckland	619244	13064.0	13062.0	MULTIPOLYGON (((174.87570 -36.95943, 174.87564...	MULTIPOLYGON (((174.87570 -36.95943, 174.87564...
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 (((174.87070 -36.95892, 174.87082...	MULTIPOLYGON (((174.87070 -36.95892, 174.87082...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1528194	NaN	174.642663	-36.590970	2020	POINT (174.64266 -36.59107)	5201612	Part Allot 74 PSH OF Waiwera	LT 549520, SO 1693/B	DCDB	Primary	None	North Auckland	NA1129/238	2289.0	2287.0	MULTIPOLYGON (((174.64203 -36.59121, 174.64232...
1779952	NaN	174.749001	-36.823758	2020	POINT (174.74890 -36.82376)	4839993	Lot 1 DP 154975	DP 154975	DCDB	Primary	None	North Auckland	NA125D/837, NA125D/838	811.0	760.0	MULTIPOLYGON (((174.74859 -36.82361, 174.74901...

Phase 3: Residential Property Transaction Dataset¶

INPUTS¶

OUTPUTS¶

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
258637	258637	174.84187	-36.903922	2020	POINT (174.84187 -36.90392)	7515185	Section 1 SO 471988	SO 471988, SO 509515	Road	Primary	[Create] Land declared Road New Zealand Gazett...	North Auckland	None	690.0	688.0	MULTIPOLYGON (((174.84229 -36.90402, 174.84226...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1601568	1601568	174.505929	-36.936271	2020	POINT (174.50593 -36.93627)	5223585	None	None	Road	Primary	None	North Auckland	None	NaN	17159.0	MULTIPOLYGON (((174.50649 -36.93640, 174.50698...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1601572	1601572	174.504944	-36.935804	2020	POINT (174.50494 -36.93580)	5246595	None	None	Road	Primary	None	North Auckland	None	NaN	2968.0	MULTIPOLYGON (((174.50492 -36.93577, 174.50511...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1678546	1678546	174.867444	-36.911853	2020	POINT (174.86744 -36.91185)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1678548	1678548	174.867678	-36.91188	2020	POINT (174.86768 -36.91188)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1678549	1678549	174.86775	-36.911824	2020	POINT (174.86775 -36.91182)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1679074	1679074	174.868011	-36.91182	2020	POINT (174.86801 -36.91182)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1679075	1679075	174.868011	-36.91182	2020	POINT (174.86801 -36.91182)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
1679078	1679078	174.868598	-36.911623	2020	POINT (174.86860 -36.91162)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
2187169	2187169	174.868811	-36.911634	2020	POINT (174.86881 -36.91163)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	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

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	LINZ_parcel_ID	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
0	75494.0	174.588941	-36.186076	2020	5128312	Part Allot S25 PSH OF Arai	NaN	DCDB	Primary	NaN	North Auckland	NA589/73	NaN	2913.0	174.589185	-36.186039	[174.588771; 174.588883; 174.589331; 174.58955...	[-36.186324; -36.185872; -36.18579; -36.186183...	[174.588771; 174.588883; 174.589331; 174.58955...	[-36.186324; -36.185872; -36.18579; -36.186183...	16.0	Rural - Rural Production Zone	[5224880, 7558496, 6744495, 5207551, 4941664]	['27', '16', '16', '27', '16']	NaN	0	NaN	NaN	0	0.000000	16	Rural - Rural Production Zone	10280.452781	7	Business - Local Centre Zone	10272.461535	19	Residential - Single House Zone	10272.461535	24600.551180	24403.577507	45633.545531	Rodney-Wellsford	Cape Rodney	110400.0	Cape Rodney	506660	135400.0	135400	4769.728933	8822.047299	805.546859	9900.327553	75106.142046	False
1	75499.0	174.581811	-36.200345	2020	5128309	Lot 2 DP 130303	DP 130303	DCDB	Primary	NaN	North Auckland	NA76B/662	9850.0	8620.0	174.581884	-36.200845	[174.581541; 174.581606; 174.581586; 174.58184...	[-36.201565; -36.201229; -36.201017; -36.19972...	[174.581541; 174.581606; 174.581586; 174.58184...	[-36.201565; -36.201229; -36.201017; -36.19972...	16.0	Rural - Rural Production Zone	[4891509, 5226218, 5259707, 5210556, 7107536]	['16', '27', '27', '27', '16']	NaN	0	NaN	NaN	0	0.000000	16	Rural - Rural Production Zone	8634.030700	7	Business - Local Centre Zone	8618.247070	19	Residential - Single House Zone	8618.247070	23080.131613	22920.639090	44102.254835	Rodney-Wellsford	Cape Rodney	110400.0	Cape Rodney	506660	135400.0	135400	6226.352423	7396.778706	38.781993	8357.878451	73639.743048	False
2	75639.0	174.496590	-36.228740	2020	4823770	Part Otioro & Te Topuni A2A Block	ML 9928	DCDB	Primary	NaN	North Auckland	NA1373/48	8296.0	7490.0	174.496751	-36.228824	[174.495137; 174.497012; 174.497667; 174.49766...	[-36.228888; -36.228552; -36.228639; -36.22893...	[174.495137; 174.497012; 174.497667]	[-36.228888; -36.228552; -36.228639]	16.0	Rural - Rural Production Zone	[5233551, 6757648, 4697657, 4692290]	['27', '16', '16', '16']	NaN	0	NaN	NaN	0	0.000000	16	Rural - Rural Production Zone	2868.991231	7	Business - Local Centre Zone	2961.229637	19	Residential - Single House Zone	2961.229637	23517.526115	23638.550639	43654.254255	Rodney-Wellsford	Cape Rodney	110400.0	Cape Rodney	506660	135700.0	135700	1281.970762	145.300891	146.179706	993.351276	72734.549746	False
3	75640.0	174.498361	-36.228827	2020	4697657	Lot 1 DP 44316	DP 44316	DCDB	Primary	NaN	North Auckland	NA1373/47	3667.0	4152.0	174.498193	-36.228933	[174.498626; 174.498615; 174.498386; 174.49766...	[-36.228766; -36.229337; -36.22924; -36.228936...	[174.498626; 174.497667]	[-36.228766; -36.228639]	16.0	Rural - Rural Production Zone	[4714623, 5233551, 6757648, 4823770]	['16', '27', '16', '16']	NaN	0	NaN	NaN	0	0.000000	16	Rural - Rural Production Zone	2803.661354	7	Business - Local Centre Zone	2893.231821	19	Residential - Single House Zone	2893.231821	23432.278243	23551.835338	43583.838430	Rodney-Wellsford	Cape Rodney	110400.0	Cape Rodney	506660	135700.0	135700	1262.870165	160.965220	161.028558	1014.271888	72681.046769	False
4	75654.0	174.493736	-36.229990	2020	5202126	Lot 1 DP 52926	DP 52926	DCDB	Primary	NaN	North Auckland	NA8B/226	3331.0	3323.0	174.494172	-36.230010	[174.49427; 174.493857; 174.493618; 174.493729...	[-36.230265; -36.230116; -36.230056; -36.22977...	[174.493729; 174.494901]	[-36.229771; -36.229975]	16.0	Rural - Rural Production Zone	[5233530, 6943999, 4762702]	['26', '16', '16']	NaN	0	NaN	NaN	0	0.000000	16	Rural - Rural Production Zone	2865.919389	7	Business - Local Centre Zone	2966.125893	19	Residential - Single House Zone	2966.125893	23566.011951	23696.066236	43647.778508	Rodney-Wellsford	Cape Rodney	110400.0	Cape Rodney	506660	135700.0	135700	1158.282593	32.568112	30.190813	808.076399	72686.065980	False
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
376089	3160123.0	174.728112	-36.408104	2018	7974171	Lot 82 DP 521864	DP 521864	Fee Simple Title	Primary	NaN	North Auckland	826394	39.0	40.0	174.728112	-36.408104	[174.728128; 174.728123; 174.728076; 174.72808...	[-36.408145; -36.408144; -36.408135; -36.40810...	[]	[]	18.0	Residential - Mixed Housing Suburban Zone	[7974172, 7974192]	['18', '46']	NaN	0	NaN	NaN	0	23.286539	46	Rural - Rural Coastal Zone	1035.859753	7	Business - Local Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1.822621	0.000000	6295.869484	20175.601410	Rodney-Warkworth	Snells Beach	111700.0	Snells Beach	506631	141518.0	141518	177.569248	6140.950168	693.061047	15034.072500	48948.610298	False
376090	3160124.0	174.728164	-36.408113	2018	7974172	Lot 83 DP 521864	DP 521864	Fee Simple Title	Primary	NaN	North Auckland	826395	39.0	40.0	174.728164	-36.408113	[174.728128; 174.728137; 174.728139; 174.72814...	[-36.408145; -36.408111; -36.408106; -36.40807...	[]	[]	18.0	Residential - Mixed Housing Suburban Zone	[7974192, 7974171]	['46', '18']	NaN	0	NaN	NaN	0	26.040801	46	Rural - Rural Coastal Zone	1036.785762	7	Business - Local Centre Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	1.819792	0.000000	6300.623848	20175.189685	Rodney-Warkworth	Snells Beach	111700.0	Snells Beach	506631	141518.0	141518	172.803437	6145.715846	695.797488	15038.214532	48947.257190	False
376091	3160630.0	174.655992	-36.509925	2018	7440747	Lot 2 DP 460961	DP 460961	Fee Simple Title	Primary	NaN	North Auckland	605470	4500.0	4495.0	174.655985	-36.510014	[174.656354; 174.656238; 174.656031; 174.65582...	[-36.510252; -36.510457; -36.510421; -36.51039...	[174.65585; 174.655895; 174.655934; 174.656017...	[-36.509406; -36.509415; -36.509423; -36.50944...	20.0	Residential - Rural and Coastal Settlement Zone	[7633409, 5133670, 7091070, 4794992, 7440746, ...	['27', '20', '20', '20', '20', '20']	NaN	0	NaN	NaN	0	109.835634	3	Rural - Countryside Living Zone	417.447696	44	Business - Neighbourhood Centre Zone	0.000000	20	Residential - Rural and Coastal Settlement Zone	5177.307959	5241.072845	7656.813158	9350.015598	Rodney-Warkworth	Puhoi Valley	111200.0	Tauhoa-Puhoi	506613	4011788.0	146901	2073.130233	1252.974311	1253.300181	6999.471513	38726.534618	False
376092	3164546.0	174.537978	-36.774834	2018	7987560	Lot 1 DP 533552	DP 533552	Fee Simple Title	Primary	NaN	North Auckland	878889	62163.0	62143.0	174.538969	-36.773813	[174.539778; 174.539738; 174.539702; 174.53967...	[-36.774848; -36.774902; -36.774953; -36.77500...	[174.539778; 174.539738; 174.539702; 174.53967...	[-36.774848; -36.774902; -36.774953; -36.77500...	18.0	Residential - Mixed Housing Suburban Zone	[8233431, 7428939, 5225918, 7987561, 7317106, ...	['18', '26', '27', '18', '18', '26', '26']	NaN	0	NaN	NaN	0	941.969071	3	Rural - Countryside Living Zone	29.596194	12	Business - Mixed Use Zone	0.000000	18	Residential - Mixed Housing Suburban Zone	20.109332	0.000000	1515.124568	5997.974757	Rodney-Kumeu-Riverhead	Kumeu-Huapai	115000.0	Huapai	505602	214900.0	214900	4999.490737	244.426685	244.142895	159.857880	21564.911961	True
376093	3166852.0	174.644137	-36.854796	2018	7799795	Section 36 SO 498829	SO 498829	Fee Simple Title	Primary	[Create] Fee Simple Title New Zealand Gazette ...	North Auckland	900731	1460.0	1459.0	174.644048	-36.854825	[174.64432; 174.644277; 174.644156; 174.643992...	[-36.854943; -36.854953; -36.854981; -36.85501...	[174.643431; 174.643415]	[-36.854998; -36.854967]	19.0	Residential - Single House Zone	[5032812, 5047947, 5169541, 7799793, 7799792, ...	['19', '19', '19', '19', '19', '19', '27', '19...	['Henderson - Mt Roskill A']	1	NaN	NaN	0	3636.140043	3	Rural - Countryside Living Zone	311.797855	17	Business - Light Industry Zone	0.000000	19	Residential - Single House Zone	0.000000	65.290284	37.576476	270.381811	Henderson-Massey	Te Atatu South-Central	127000.0	Wakeling	512402	252200.0	252200	134.982410	47.355652	35.297483	2761.942737	10557.928028	False

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
2230783	2230783	174.868408	-36.911747	2020	POINT (174.86841 -36.91175)	8165753	Section 1 SO 557939	SO 557939	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	3834.0	3827.0	MULTIPOLYGON (((174.86779 -36.91198, 174.86753...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
2885088	2885088	174.966414	-36.949856	2020	POINT (174.96641 -36.94986)	8177073	Section 1 SO 559920	SO 559920	Road	Primary	[Create] Land Declared Road New Zealand Gazett...	North Auckland	None	683.0	683.0	MULTIPOLYGON (((174.96648 -36.94995, 174.96644...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
3141656	3141656	174.915455	-36.980799	2020	POINT (174.91545 -36.98080)	8191816	Lot 505 DP 557698	DP 557698	Road	Primary	[Referenced] Vesting on Deposit for Road Veste...	North Auckland	None	8817.0	8816.0	MULTIPOLYGON (((174.91540 -36.98070, 174.91555...

	CL_QPID_output2	CL_Longitude	CL_Latitude	QPID_vintage	geometry	index_right	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	parcel_geometry
CL_QPID_output2
3214918	3214918	174.974731	-37.061646	2020	POINT (174.97473 -37.06165)	8149695	Lot 305 DP 549093	DP 549093	Road	Primary	[Referenced] Vesting on Deposit for Road Veste...	North Auckland	None	3344.0	3344.0	MULTIPOLYGON (((174.97573 -37.06142, 174.97574...