3. Internal Data
The information below describes the data model for building outlines for internal LINZ purposes.
3.1. Data Model
To assist you in understanding these datasets, the structure and details of the data fields is described in tables below. The relationship between tables and directly related datasets is provided in a data model diagram. No attempt has been made to indicate cardinality, however, the arrows drawn between datasets point from the dataset containing the unique record, to the dataset that may contain one or more references to that record (i.e. primary key -> foreign key).
Primary keys in tables are shown by a field name with bold font.
This data model has been designed to manage building data with multiple representations, allowing for future enhancements in building data management. Not all of this data is currently available and data capture for these new fields will occur over time.
3.2. Schema: buildings
Description: Schema that holds production buildings data.
3.2.1. Table: lifecycle_stage
Description: Lookup table that holds all of the lifecycle stages for a building.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
lifecycle_stage_id
integer
32
No
Unique identifier for the lifecycle stage.
value
varchar
40
No
The stage of a buildings lifecycle.
3.2.2. Table: use
Description: Lookup table that holds all of the uses for a building. These uses are the same as those used in the Topo50 map series.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
use_id
integer
32
No
Unique identifier for the use.
value
varchar
40
No
The building use maintained for the Topo50 map series.
3.2.3. Table: buildings
Description: The building table maintains a unique identifier for a building. Over time, a building can be represented by multiple building outlines aligned to different imagery sources. These building outlines are linked via the building_id.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
building_id
integer
32
No
Unique identifier for the building.
begin_lifespan
date
No
The date that the building was first captured in the system.
end_lifespan
date
Yes
The date that a building was either replaced or disused.
3.2.4. Table: building_outlines
Description: The building_outline table holds a geometry, typically captured from an aerial photograph. A new record is created for each new outline that represents a building.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
building_outline_id
integer
32
No
Unique identifier for the building outline.
building_id
integer
32
No
Foreign key to the buildings.buildings table. The building id is persistant for the same building across all of the building outlines that represent it.
capture_method_id
integer
32
No
Foreign key to the buildings_common.capture_method table. Holds the method by which the geometry was captured.
capture_source_id
integer
32
No
Foreign key to the buildings_common.capture_source table.
lifecycle_stage_id
integer
32
No
Foreign key to the buildings.lifecycle_stage table.
suburb_locality_id
integer
32
No
Holds an external id for suburbs / localities from the nz_locality dataset.
town_city_id
integer
32
Yes
Holds an external id for the town / city from the nz_locality dataset.
territorial_authority_id
integer
32
No
Holds an external id for the territorial authority from the territorial_authority dataset.
begin_lifespan
date
No
The date that the building outline was added to the system.
end_lifespan
date
Yes
The date that the building outline was superceded replaced or disused.
shape
geometry
Yes
The geometry of the building outline.
3.2.5. Table: building_name
Description: The building_name table stores a name that is related to a building at a point in time. Names have an lifespan independent from the building - the name can change independent to any other changes to the building.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
building_name_id
integer
32
No
Unique identifier for a building name.
building_id
integer
32
No
Foreign key to the buildings.buildings table.
building_name
varchar
250
No
The name of the building where known.
begin_lifespan
date
No
The date that the building name was first captured in the system.
end_lifespan
date
Yes
The date that the building name was no longer attributed to a building in the system either because the name was no longer in use or because the building was replaced or disused.
3.2.6. Table: building_use
Description: The building_use table stores a use that is related to a building at a point in time. Uses have an lifespan independent from the building - the use can change independent to any other changes to the building.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
building_use_id
integer
32
No
Unique identifier for a building_use.
building_id
integer
32
No
Foreign key to the buildings.buildings table.
use_id
integer
32
No
Foreign key to the buildings.use table.
begin_lifespan
date
No
The date that the building use was first captured in the system.
end_lifespan
date
Yes
The date that the building use was no longer attributed to a building in the system either because the use was no longer in use or because the building was replaced or disused.
3.2.7. Table: lifecycle
Description: The lifecycle table stores the relationship between buildings when one building is split into two buildings or two buildings are merged into one building. This will generally occur when a building outline was erroneously captured encompassing two buildings, which later becomes clear with additional aerial imagery.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
lifecycle_id
integer
32
No
Unique identifier for a building_use.
parent_building_id
integer
32
No
Foreign key to the buildings.buildings table. All records stored as parent buildings will be end dated in the system.
building_id
integer
32
Yes
Foreign key to the buildings.buildings table.
3.3. Schema: buildings_common
Description: Schema that holds tables referenced in more than one other schema.
3.3.1. Table: capture_method
Description: Lookup table that holds all of the methods by which the geometry was captured.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
capture_method_id
integer
32
No
Unique identifier for the capture method.
value
varchar
40
No
The method by which the geometry was captured.
3.3.2. Table: capture_source_group
Description: Lookup table that holds all of the capture source groups. Capture source groups are categories of data sources, e.g. NZ Aerial Imagery
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
capture_source_group_id
integer
32
No
Unique identifier for the capture source group.
value
varchar
80
No
The name of the capture source group e.g. NZ Aerial Imagery.
description
varchar
400
No
The description of the capture source group e.g. external_source_id for this group links to the primary key of the NZ Imagery Surveys layer available on LINZ Data Service at: https://data.linz.govt.nz/layer/95677.
3.3.3. Table: capture_source
Description: Lookup table that holds all of the methods by which the geometry was captured.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
capture_source_id
integer
32
No
Unique identifier for the capture source.
capture_source_group_id
integer
32
No
Foreign key to the buildings_common.capture_source_group table.
external_source_id
varchar
250
Yes
Stores a reference to an externally managed identifier that can be linked in order to find out more information about the source.
3.4. Schema: buildings_bulk_load
Description: Schema that holds building outlines data while quality assurance is conducted. Data is also prepared to be loaded into production.
3.4.1. Table: organisation
Description: This is a lookup table that holds names of organisations that are related to buildings data. All suppliers of building outlines data must be recorded here.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
organisation_id
integer
32
No
Unique identifier for the organisation table.
value
varchar
40
No
The name of the organisation.
3.4.2. Table: bulk_load_status
Description: This is a lookup table that holds the status of building outlines through the bulk load process.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
bulk_load_status_id
integer
32
No
Unique identifier for the bulk_load_status table.
value
varchar
40
No
The bulk load status of the building outline. Options include: Supplied Added
3.4.3. Table: qa_status
Description: This is a lookup table that holds the status of building outlines during QA of the bulk load process.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
qa_status_id
integer
32
No
Unique identifier for the qa_status table.
value
varchar
40
No
The QA status of the building outlines.
3.4.4. Table: supplied_datasets
Description: This table records information about datasets supplied to LINZ for bulk load into the buildings system.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
supplied_dataset_id
integer
32
No
Unique identifier for the supplied_datasets table.
description
varchar
250
No
A general description of the supplied dataset.
supplier_id
integer
32
No
Foreign key to the buildings_bulk_load.organisation table.
processed_date
date
Yes
The date that the supplied dataset was imported into the buildings_bulk_load schema.
transfer_date
date
Yes
The date that the supplied dataset was transferred to production schema.
3.4.5. Table: supplied_outlines
Description: This dataset contains all building outline geometries as they are received from the supplier.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
supplied_outline_id
integer
32
No
Unique identifier for the supplied_outlines table.
supplied_dataset_id
integer
32
No
Foreign key to the buildings_bulk_load.supplied_datasets table.
external_outline_id
varchar
250
Yes
External identifier held in order to compare with future bulk loads from the same external dataset.
begin_lifespan
date
No
The date that the building was uploaded via bulk load tools.
shape
geometry
Yes
The geometry of the building outline as received from the supplier.
3.4.6. Table: bulk_load_outlines
Description: This dataset contains all building outline geometries received from the supplier, in addition to any new building outlines added during QA of that particular bulk load. A number of attributes are first connected to the building outline in this table, which are later loaded into the production buildings schema.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
bulk_load_outline_id
integer
32
No
Unique identifier for the bulk_load_outlines table.
supplied_dataset_id
integer
32
No
Foreign key to the buildings_bulk_load.supplied_datasets table.
external_outline_id
varchar
250
Yes
External identifier held in order to compare with future bulk loads from the same external dataset.
bulk_load_status_id
integer
32
No
Foreign key to the buildings_bulk_load.bulk_load_status table.
capture_method_id
integer
32
No
Foreign key to the buildings_common.capture_method table.
capture_source_id
integer
32
No
Foreign key to the buildings_common.capture_source table.
suburb_locality_id
integer
32
No
Holds an external id for suburbs / localities from the nz_locality dataset.
town_city_id
integer
32
Yes
Holds an external id for the town / city from the nz_locality dataset.
territorial_authority_id
integer
32
No
Holds an external id for the territorial authority from the territorial_authority dataset.
begin_lifespan
date
No
The date that the building was uploaded via bulk load tools.
shape
geometry
Yes
The geometry of the building outline as received from the supplier.
3.4.7. Table: existing_subset_extracts
Description: This table contains extracts of production building outlines over the same area covered by a new bulk load of building outlines data. Each set of extracted building outlines is related to the new bulk load via the supplied_dataset_id.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
building_outline_id
integer
32
No
Unique identifier for the existing_subset_extracts table and foreign key to the buildings.building_outlines table.
supplied_dataset_id
integer
32
No
Foreign key to the buildings_bulk_load.supplied_datasets table.
shape
geometry
Yes
The geometry of the building outline that exists in the production schema.
3.4.8. Table: added
Description: This table holds the building outlines that have been identified as new buildings within the building outlines dataset.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
bulk_load_outline_id
integer
32
No
Unique identifier for the added table and foreign key to the buildings_bulk_load.bulk_load_outlines table.
qa_status_id
integer
32
No
Foreign key to the buildings_bulk_load.qa_status table.
3.4.9. Table: removed
Description: This table holds the building outlines that have been identified as no longer existing. These building outlines were within the area of capture but were not found in a more recent capture process.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
building_outline_id
integer
32
No
Unique identifier for the removed table and foreign key to the buildings_bulk_load.existing_subset_extracts table.
qa_status_id
integer
32
No
Foreign key to the buildings_bulk_load.qa_status table.
3.4.12. Table: matched
Description: This table holds potential 1:1 matches between outlines that have been loaded into the system in bulk and those that already exist.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
bulk_load_outline_id
integer
32
No
Foreign key to the buildings_bulk_load.bulk_load_outlines table.
building_outline_id
integer
32
No
Foreign key to the buildings_bulk_load.existing_subset_extracts table.
qa_status_id
integer
32
No
Foreign key to the buildings_bulk_load.qa_status table.
3.4.13. Table: transferred
Description: This table holds the building_outline_id that was created for each bulk loaded building outline transferred to production.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
bulk_load_outline_id
integer
32
No
Unique identifier for the transferred table and foreign key to the buildings_bulk_load.bulk_load_outlines table.
new_building_outline_id
integer
32
No
Foreign key to the buildings.building_outlines table.
3.4.14. Table: deletion_description
Description: This table records information about the reason for deleting outlines during bulk load process.
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
bulk_load_outline_id
integer
32
No
Unique identifier for the deletion_description table and foreign key to the buildings_bulk_load.bulk_load_outlines table.
description
varchar
250
No
The reason for deleting the building outlines.
3.5. Schema: buildings_reference
Description: Schema that holds the reference data used in checks and processing.
3.5.1. Table: suburb_locality
Description: suburbs/Localities of New Zealand
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
suburb_locality_id
integer
32
No
Unique identifier for suburb_locality.
external_suburb_locality_id
integer
32
Yes
The id given by the external source.
suburb_4th
varchar
60
Yes
name
suburb_3rd
varchar
60
Yes
name
suburb_2nd
varchar
60
Yes
name
suburb_1st
varchar
60
Yes
name
shape
geometry
Yes
3.5.2. Table: town_city
Description: towns/cities of New Zealand
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
town_city_id
integer
32
No
Unique identifier for town_city.
external_city_id
integer
32
Yes
The id given by the external source.
name
varchar
60
Yes
The name of the town/city.
shape
geometry
Yes
3.5.4. Table: coastlines_and_islands
Description: https://data.linz.govt.nz/layer/51153-nz-coastlines-and-islands-polygons-topo-150k/
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
coastline_and_island_id
integer
32
No
Unique identifier for the coastlines_and_islands.
external_coastline_and_island_id
integer
32
Yes
The id of the coastline or island from the external provider.
shape
geometry
Yes
3.5.5. Table: river_polygons
Description: http://apps.linz.govt.nz/topo-data-dictionary/index.aspx?page=class-river_poly
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
river_polygon_id
integer
32
No
Unique identifier for the river_polygons.
external_river_polygon_id
integer
32
Yes
The id of the river from the external provider.
shape
geometry
Yes
3.5.6. Table: lake_polygons
Description: http://apps.linz.govt.nz/topo-data-dictionary/index.aspx?page=class-lake_poly
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
lake_polygon_id
integer
32
No
Unique identifier for the lake_polygons.
external_lake_polygon_id
integer
32
Yes
The id of the lake from the external provider.
shape
geometry
Yes
3.5.7. Table: pond_polygons
Description: http://apps.linz.govt.nz/topo-data-dictionary/index.aspx?page=class-pond_poly
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
pond_polygon_id
integer
32
No
Unique identifier for the pond_polygons.
external_pond_polygon_id
integer
32
Yes
The id of the pond from the external provider.
shape
geometry
Yes
3.5.8. Table: swamp_polygons
Description: http://apps.linz.govt.nz/topo-data-dictionary/index.aspx?page=class-swamp_poly
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
swamp_polygon_id
integer
32
No
Unique identifier for the swamp_polygons.
external_swamp_polygon_id
integer
32
Yes
The id of the swamp from the external provider.
shape
geometry
Yes
3.5.9. Table: lagoon_polygons
Description: http://apps.linz.govt.nz/topo-data-dictionary/index.aspx?page=class-lagoon_poly
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
lagoon_polygon_id
integer
32
No
Unique identifier for the lagoon_polygons.
external_lagoon_polygon_id
integer
32
Yes
The id of the lagoon from the external provider.
shape
geometry
Yes
3.5.10. Table: canal_polygons
Description: http://apps.linz.govt.nz/topo-data-dictionary/index.aspx?page=class-canal_poly
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
canal_polygon_id
integer
32
No
Unique identifier for the canal_polygons.
external_canal_polygon_id
integer
32
Yes
The id of the canal from the external provider.
shape
geometry
Yes
3.5.11. Table: capture_source_area
Description: https://nz-imagery-surveys.readthedocs.io/en/latest/?badge=latest
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
area_polygon_id
integer
32
No
Unique identifier for the capture_source_area.
shape
geometry
Yes
3.5.12. Table: reference_update_log
Description: Log of when reference tables have been updated
Column Name
Data Type
Length
Precision
Scale
Allows Nulls
Description
update_id
integer
32
No
Unique identifier for the log update
update_date
date
Yes
Date of the reference data update