Preparing Data

No specific database design is required for using the quality measures presented here. The measures rely on the views, tables and pseudocode descriptions listed below.

View, Table or Description	Type	Description
AddressPtCollection	View	A view comprising elements and attributes for thoroughfare addresses, including point geometry.
StCenterlineCollection	View	A view of street centerline segments, including Two Number Address Range or Four Number Address Range attributes and line geometry
Pseudocode data	Descriptions	In some cases a measure may apply broadly. The UniquenessMeasure is one example: it may apply in many circumstances. Data is described in a more general way with pseudocode.
Elevation Polygon Collection	Table	A set of polygons built from contours of elevation, used in the Address Elevation Measure
StreetsNodes	Table	Startpoints and endpoints of street segments, with associated street name information. This table has a foreign key relationship with the Nodes table.
Nodes	Table	Unique point locations found in StreetsNodes. These are, effectively, intersection and endpoints. Where two streets intersect, for example, four records in StreetsNodes have the same Nodes foreign key value

Creating the views and tables described below will simplify using quality control measures. The majority of queries use the views, in the interest of supporting a variety of database designs. These are often useful to maintain in a database for general use. Although they are not in and of themselves a database design, they can assist in the use of a more normalized set of tables. Any of the optional elements or attributes listed in the table may be omitted if they are not required for the data set itself. The views are "wide" and, depending on the complexity of the underlying database may be best supported as materialized views. A materialized view is a table, often maintained by triggers or queries, created instead of a view in the interest of efficiency.

The AddressPtCollection and StCenterlineCollection views are listed below, followed by a brief discussion of the Streets Nodes and Nodes tables required for some of the measures. Finally, the Elevation Polygon Collection required for the Address Elevation Measure is described.

Views

The queries for composing those views will vary according to the design of the underlying database.

Address Point Collection ( AddressPtCollection )

Field Name	Description
Address ID	Address attribute
AddressCompleteStreetNameID	A unique identification number assigned to each Complete Street Name
Related Transportation Feature ID	Address Transportation Feature ID
Complete Address Number	Address element
Address Number Prefix	Address element
Address Number	Address element
AddressNumberAttached	Example of an Attached Element. Can be inserted between Complete Address Number or Complete Street Name components as needed.
Address Number Suffix	Address element
AddressCompleteStreetName	Address element: Complete Street Name
Street Name Pre Modifier	Address element
Street Name Pre Directional	Address element
Street Name Pre Type	Address element
Separator Element	Address element
Street Name	Address element
Street Name Post Type	Address element
Street Name Post Directional	Address element
Street Name Post Modifier	Address element
Place Name	Address element
Zip Code	Address element
Address Side Of Street	Address attribute
Address Number Parity	Address attribute
Address Authority	Address attribute
Address Feature Type	Address attribute
Address Elevation	Address attribute
Address Lifecycle Status	Address attribute
Official Status	Address attribute
BuildingPermit	A boolean field describing whether or not a building permit has been issued.
Address Start Date	Address attribute
Address End Date	Address attribute
Address XCoordinate	Address attribute
Address YCoordinate	Address attribute
Address Longitude	Address attribute
Address Latitude	Address attribute
Delivery Address Type	Address attribute
USNational Grid Coordinate	Address attribute
AddressPtGeometry	The point geometry for the address

Street Centerline Collection ( StCenterlineCollection )

Field Name	Description
Address Transportation Feature ID	Address Transportation Feature ID
StCenterlineCompleteStreetNameID	The unique identification number assigned to the Complete Street Name pertaining to each centerline or transportation feature associated with the address
Range.Low	A placeholder for low values in a Four Number Address Range or Two Number Address Range
Range.High	A placeholder for high values in a Four Number Address Range or Two Number Address Range
StCenterlineCompleteStreetName	Address element: Complete Street Name
StCenterlineStreetNamePreModifier	Address element: Street Name Pre Modifier
StCenterlineStreetNamePreDirectional	Address element: Street Name Pre Directional
StCenterlineStreetNamePreType	Address element: Street Name Pre Type
StCenterlinePreTypeAttachedElement	Address element: Attached Element. This is an example. Attached elements may occur anywhere a Complete Address Number or Complete Street Name.
StCenterlineStreetName	Address element: Street Name
StCenterlineStreetNamePostType	Address element: Street Name Post Type
StCenterlineStreetNamePostDirectional	Address element: Street Name Post Directional
StCenterlineStreetNamePostModifier	Address element: Street Name Post Modifier
PlaceNameLeft	Address element: Place Name
PlaceNameRight	Address element: Place Name
ZipCodeLeft	Address element: Zip Code
ZipCodeRight	Address element: Zip Code
StCenterlineGeometryDirection	The cardinal direction of the line: "east-west" or "north-south". This attribute is part of (ARS) rule set in many areas, included as an example. Elements or attributes required in a given ARS that may be included in this view to support quality control. Address Reference System
Address Range Directionality	Address attribute
StCenterlineGeometry	Line geometry for the street centerline or transportation feature associated with the address

Tables

Nodes and StreetNodes

About Nodes

Nodes are the end points for each road segment. They are used throughout Address Data Quality in checking features at intersections. The code examples below show how to create and fill one version of the tables required. There are a wide variety of variations that will work. For example, in a more normalized database the Complete Street Name field may be replaced by a foreign key. The specifics will vary across systems.

The tables are:

StreetsNodes, a table correlating nodes with the street names assigned to segments connecting at those nodes.
Nodes, a table to hold the nodes themselves.

Nodes

Where street segments intersect, multiple segment ends will share the same node geometry. This table selects unique node points. The geometries are matched back to the StreetsNodes table so that each record has a node identifier referencing an unique geometry.

The following transaction creates the table. The Nodes table must be created before StreetsNodes to provide for the reference to it in the latter table.

begin;

Create a table with a primary key

create table Nodes
(
   id serial primary key
)
;

Add a geometry column. In most cases, -1 will be replaced by an Address Coordinate Reference System ID Address Coordinate Reference System ID

select addgeometrycolumn( 'nodes', 'nodes', 'Geom',-1,'POINT',2);

end;

StreetsNodes

The transaction below creates and fills the table.

begin;

Create a table for the StreetsNodes, ideally holding only intersections and dead ends. It will also hold pseudonodes where they occur. In many cases it will be desirable to include an identifier for the Complete Street Name value in addition to the text.

create table StreetsNodes
(
   id serial primary key,
   Nodesfk integer references Nodes,
   RelatedTransportationFeatureID integer,
   CompleteStreetName varchar(100),
   SegmentEnd varchar(4)
)
;

Add a geomety column for StreetsNodes. As with the Nodes table, the -1 will likely be replaced with an Address Coordinate Reference System ID value.

select addgeometrycolumn( 'nodes', 'StreetsNodes', 'Geom',-1,'POINT',2);

Fill the StreetsNodes table with data from the StCenterlineCollection.

insert into StreetsNodes( RelatedTransportationFeatureID, CompleteStreetName, SegmentEnd, Geom )
(
  select
     id,
     CompleteStreetName,
     'from',
     st_startpoint( a.StCenterlineGeometry )
  from
     StCenterlineCollection
)
union
(
  select
     id,
     CompleteStreetName,
     'to',
     st_endpoint( a.StCenterlineGeometry )
  from
     StCenterlineCollection
)
;
end;

Fill the Nodes table from the data captured in StreetsNodes

insert into
   Nodes( Geom )
select distinct
   geom
from
   StreetsNodes
;

Finally, the statement below fills the nodesfk field in the StreetsNodes table.

update
   StreetsNodes
set
   Nodesfk = foo.Nodesfk
from
   (
     select
        a.id as Nodesfk,
        b.id
     from
        Nodes a,
        StreetsNodes b
     where
        equals( a.Geom, b.Geom )
   ) as foo
where
   foo.id = StreetsNodes.id
;

end;

ElevationPolygonCollection

Field Name	Description
ElevationPolygonID	Primary key
AddressElevationMin	Lowest elevation of the contours bounding the polygon
AddressElevationMax	Highest elevation of the contours bounding the polygon
ElevationPolygonGeometry	Polygon geometry

How to Prepare Data for Quality Control

Introduction