United States Thoroughfare, Landmark, and Postal Address Data Standard (Final Draft)


How to Prepare Data for Quality Control



Download 4.55 Mb.
Page33/58
Date17.08.2017
Size4.55 Mb.
#33941
1   ...   29   30   31   32   33   34   35   36   ...   58

4.4 How to Prepare Data for Quality Control


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

Performing a suite of measures for any given address data set will be aided by creating the views and tables required for those measures. Views are used to illustrate the majority of queries 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.


4.4.1 Views


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

4.4.1.2 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

4.4.1.3 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"

Address Range Directionality

Address attribute

StCenterlineGeometry

Line geometry for the street centerline or transportation feature associated with the address

4.4.2 Tables

4.4.2.1 Nodes and StreetsNodes


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. This particular example is given in PostgreSQL/PostGIS. The specifics will vary across systems.

The tables are:


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

  2. Nodes, a table to hold the nodes themselves.

Nodes

Where street segments intersect, multiple nodes will have the same geometry. This table selects unique node points. The geometries are matched back to the StreetNodes 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 table Nodes

(

id serial primary key



)

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


end;

StreetsNodes

The transaction below creates and fills the table.

begin;
create table StreetsNodes

(

id serial primary key,



Nodesfk integer references Nodes,

RelatedTransportationFeatureID integer,

CompleteStreetName varchar(100),

seg_end varchar(4)

)

;
select addgeometrycolumn( 'nodes', 'StreetsNodes',



'geom',-1,'POINT',2);
insert into StreetsNodes( RelatedTransportationFeatureID, CompleteStreetName, seg_end, geom )

(

select



id,

CompleteStreetName,

'from',

st_startpoint( geom )



from

StCenterlineCollection

)

union


(

select


id,

CompleteStreetName,

'to',

st_endpoint( 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


inner join StreetsNodes b

on equals( a.geom, b.geom ) equals( a.geom, b.geom )

) as foo

where


foo.id = StreetsNodes.id

;
end;


4.4.2.2 Elevation Polygon Collection


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


Download 4.55 Mb.

Share with your friends:
1   ...   29   30   31   32   33   34   35   36   ...   58



The database is protected by copyright ©ininet.org 2024
send message
    Main page
national aeronautics
national archives
same domain
working area
elements needed
geographic boundaries
model presented