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.3. Table: territorial_authority¶
Description: Territorial authorities of New Zealand
Column Name Data Type Length Precision Scale Allows Nulls Description territorial_authority_id integer 32 No Unique identifier for territorial_authority. external_territorial_authority_id integer 32 Yes The external_territorial_authority_id provided by the external source. name varchar 100 Yes The name of the territorial authority. 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