Versions Compared

Version Old Version 2 New Version Current
Changes made by

Samuel Valdez

Samuel Valdez

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
minLevel1
maxLevel7
outlinetrue
absoluteUrltrue

Introduction

The EAS Flat File Export (FFE) is a compressed CSV file that is written to the DT Secure FTP Server and contains every address data transaction that has ever occurred in the Enterprise Addressing System (EAS). This data product is best suited for those consumers who need access to the entire history of address data transactions that are in the EAS, specifically, all of the base address creations and retirements, all of the unit address creations and retirements, and all of the parcel-link creations and retirements, that have ever occurred in the EAS.

The EAS data model is complex enough that rather than export individual database tables and views, and then describe how these table and views relate to each other, the FFE was devised to present a “flattened” representation of a subset of the EAS data model.

Please send an email to the DT-EAS-Support email distribution group if you are interested in learning how to consume the EAS FFE.

Record Description

Each FFE record represents a specific combination of “base address (table record identifier)” and “unit address (table record identifier)” and “parcel link (table record identifier)”. A record from this data set is not an “EAS record”, nor an “EAS address record”.

Info

It is probably better to think of each record as representing a specific address data transaction.

Basic Definitions

The following definitions may be useful:

  • base address - The combination of the Complete Address Number and the Complete Street Name of the object address of an address data transaction. Examples include “2701 VAN NESS AVE“ and “188 MINNA ST“.

  • unit address - A Complete Subaddress, if there is one, of the base address of an address data transaction. Examples include “apartment 403“ and “condominium 22 F“. Note that this data set only provides the Subaddress Identifier (“403” and “22 F”) and does not provide the Subaddress Type of a unit address (subaddress), for example “apartment“, “suite”, and “condominium“.

  • object address - The address that is the object of an address data transaction. The object address may be either:

    • a base address only (for example “188 MINNA ST“), or

    • a base address and a unit address (for example “188 MINNA ST, 22 F“)

Note that each object address in this data set will have its own value of eas_address_id. See field 2 in the table that is in the section Schema that is below.

Schedule

The EAS FFE nightly job is initiated on Monday through Friday at 6:00 PM, and takes less than three minutes to post these data to the DT Secure FTP Server.

Email Notification

An email is sent to the email distribution group DT-EAS-Flat-File-Export when the FFE has successfully completed. This same email distribution group is also used to notify subscribers when the DT Secure FTP Server is undergoing maintenance.

Schema

The schema of the EAS Flat File Export is described in the following table.

Info

Some of the descriptions that are in the table that is below are taken directly from the https://www.fgdc.gov/standards/projects/address-data, and these descriptions are indicated by the term “FGDC“ at the very beginning of the last line of the description.

FIELD NAME

PostgreSQL 11 DATA TYPE

PostGIS 2.5 DATA TYPE

DESCRIPTION

1

id

text

eas_address_base_id (field 3)

+ “-” + eas_unit_address_id (field 13)

+ “-” + eas_address_x_parcel_id (field 23)

Conceptually:

“base address table record identifier”

+ “-” + “unit address table record identifier”

+ “-” + “parcel link table record identifier” = address data transaction identifier

2

eas_address_id

integer

Coming soon.

Uniquely identifies the address that is the object of this address data transaction. You can think of this value as the universal EAS address identifier, which can be used to retrieve any address-related information that is in the EAS database.

Use this field to relate this record to additional address attributes that will become available in other data sets and Web services. As a natural consequence of the availability of this field, it is possible that some of the address attributes that are provided here (for example address_type [field 31], and landmark_aliases [field 32]) will be moved to external data sets to reduce the “non-essential attribute clutter” that is in the FFE.

3

eas_address_base_id

integer

Uniquely identifies the base address of an address data transaction.

Also see the description for the column EAS BaseID that is given in the data set https://data.sfgov.org/Geographic-Locations-and-Boundaries/San-Francisco-Addresses-Enterprise-Addressing-Syst/3mea-di5p/about_data that is available at https://datasf.org/opendata/.

4

base_address_num

integer

The numeric identifier for a land parcel, house, building, or other location along a thoroughfare or within a community.

FGDC page 37

5

base_address_suffix

character varying(10)

The portion of the street_full_street_name (field 10) which follows the base_address_num (field 4) itself.

FGDC page 39

6

street_name

character varying(60)

The portion of the street_full_street_name (field 10) that identifies the particular thoroughfare (as opposed to the Street Name Pre Modifier, Street Name Post Modifier, Street Name Pre Directional, street_post_direction [field 8], Street Name Pre Type, street_type [field 7], and Separator Element (if any) in street_full_street_name [field 10].)

FGDC page 54

7

street_type

character varying(6)

A word or phrase that follows street_name (field 6) and identifies a type of thoroughfare in street_full_street_name (field 10).

FGDC page 56

8

street_post_direction

character varying(10)

A word following the street_name (field 6) that indicates the direction or position of the thoroughfare relative to an arbitrary starting point or line, or the sector where it is located.

FGDC page 59

9

street_cnn

integer

The unique identifier of the street centerline segment (more commonly known as the related transportation feature) to which the object address in this record is associated. This is also known as the centerline network number (CNN).

Refer to https://datasf.gitbook.io/draft-publishing-standards/standard-reference-data/basemap/street-centerlines-nodes for more information.

10

street_full_street_name

character varying(255)

Official name of a thoroughfare as assigned by a governing authority, or an alternate (alias) name that is used and recognized. For any street name that is in this data set, the governing authority is the City and County of San Francisco.

Refer to https://datasf.gitbook.io/draft-publishing-standards/standard-reference-data/basemap/street-namesfor more information.

FGDC page 63

11

base_address_create_tms

timestamp without time zone

The time stamp of the creation of the base address in the EAS.

12

base_address_retire_tms

timestamp without time zone

The time stamp of the retirement of the base address in the EAS.

The retirement of a base address also retires all base address parcel links, if there are any, and retires all unit addresses (subaddresses), if there are any, and all of their parcel links, if there are any.

13

eas_unit_address_id

integer

Uniquely identifies the unit address (subaddress) of an address data transaction.

Also see the description for the column EAS SubID that is given in the data set https://data.sfgov.org/Geographic-Locations-and-Boundaries/San-Francisco-Addresses-with-Units-Enterprise-Addr/ramy-di5m/about_data that is available at https://datasf.org/opendata/.

14

unit_address_base_flg

boolean

Note

This field is superfluous and may be deprecated in the future; the presence or absence of a value in unit_address (field 15) implies the value of this field.

If the value of this field is TRUE (“t”), then there will be no value in unit_address (field 15).

If the value of this field is FALSE (“f”), then there will be a value in unit_address (field 15).

15

unit_address

character varying(10)

The letters, numbers, words or combination thereof used to
distinguish different [unit addresses] subaddresses of the same type when several occur within the same feature.

This is not a Complete Subaddress, if there is a [unit address] subaddress, of the base address of an address data transaction but only the Subaddress Identifier. Examples include “403“ and “22 F“. Note that this data set does not provide the Subaddress Type of a unit address (subaddress), for example “apartment“, “suite”, and “condominium“.

FGDC page 78

16

unit_address_create_tms

timestamp without time zone

The time stamp of the creation of the unit address (subaddress) in the EAS, if there is one.

17

unit_address_retire_tms

timestamp without time zone

The time stamp of the retirement of the unit address (subaddress) in the EAS, if there is one.

The retirement of a unit address (subaddress), if there is one, also retires all of its parcel links, if there are any.

18

disposition_description

character varying(20)

Note

The concept of an official address is not fully developed in the EAS and this field may be deprecated in the future.

This field is meant to apply to either the base address or the unit address (subaddress) for a given address data transaction, depending upon the value of unit_address_base_flg (field 14).

However, this field only contains one value, “official”, throughout the entire data set, so this field is superfluous in practice. In addition, the term “official” is not defined, so it should not be interpreted to have any meaning at all.

19

map_block_lot

character varying(10)

See the description for the column mapblklot that is available at https://data.sfgov.org/Geographic-Locations-and-Boundaries/Parcels-Active-and-Retired/acdm-wktn.

Refer to https://datasf.gitbook.io/draft-publishing-standards/standard-reference-data/basemap/parcels for more information.

20

block_lot

character varying(9)

This is the local name for the assessor parcel number (APN), and it identifies the parcel that `is part of the address data transaction, if there is one.

See the description for the column blklot that is available at https://data.sfgov.org/Geographic-Locations-and-Boundaries/Parcels-Active-and-Retired/acdm-wktn.

Refer to https://datasf.gitbook.io/draft-publishing-standards/standard-reference-data/basemap/parcels for more information.

21

parcel_date_map_add

date

See the description for the column date_map_add that is available at https://data.sfgov.org/Geographic-Locations-and-Boundaries/Parcels-Active-and-Retired/acdm-wktn.

Refer to https://datasf.gitbook.io/draft-publishing-standards/standard-reference-data/basemap/parcels for more information.

22

parcel_date_map_drop

date

See the description for the column date_map_drop that is available at https://data.sfgov.org/Geographic-Locations-and-Boundaries/Parcels-Active-and-Retired/acdm-wktn.

Refer to https://datasf.gitbook.io/draft-publishing-standards/standard-reference-data/basemap/parcels for more information.

23

eas_address_x_parcel_id

integer

Uniquely identifies the association (link) between a base address and an APN, or a unit address (subaddress) and an APN, depending upon the value of unit_address_base_flg (field 14). The result of this association is sometimes called “address cross parcel”, or “AxP” for short.

24

address_x_parcel_create_tms

timestamp without time zone

The time stamp of the creation of the AxP, if there is one. Colloquially, link a parcel to an address.

25

address_x_parcel_retire_tms

timestamp without time zone

The time stamp of the retirement of the AxP, if there is one. Colloquially, unlink a parcel from an address.

26

zipcode

character varying(50)

A system of 5-digit codes that identifies the individual Post Office or metropolitan area delivery station associated with an address.

FGDC page 96

27

longitude

float

In the WGS 84 geodetic coordinate reference system. See WGS84 - World Geodetic System 1984.

Refer to https://datasf.gitbook.io/draft-publishing-standards/data-structure-and-formats/formats/location-coordinates for more information.

28

latitude

float

In the WGS 84 geodetic coordinate reference system. See WGS84 - World Geodetic System 1984.

Refer to https://datasf.gitbook.io/draft-publishing-standards/data-structure-and-formats/formats/location-coordinates for more information.

29

geometry

geometry

In the NAD83 geodetic coordinate reference system. See NAD83 / California zone 3 (ftUS).

Refer to https://datasf.gitbook.io/draft-publishing-standards/data-structure-and-formats/formats/location-coordinates for more information.

30

complete_landmark_name

character varying(150)

One or more Landmark Names which identify a relatively permanent feature of the man-made landscape that has recognizable identity within a particular cultural context.

FGDC page 86

31

address_type

text

Note

The field name may be deprecated in the future, and replaced with address_class.

Indicates the two United States address classes that may be found in this data set, specifically:

  • Numbered Thoroughfare Address

  • Landmark Address

Technically, this field applies only to the base address but both address classes may contain unit addresses (subaddress).

By definition, a numbered thoroughfare address may have a complete landmark name, while a landmark address will never have values in the base_address_num (field 4), base_address_suffix (field 5), street_name (field 6), street_type (field 7), street_post_direction (field 8), street_cnn (field 9), and street_full_street_name (field 10) fields.

FGDC pages 228 and 244

32

landmark_aliases

character varying[] (150)

An array of complete landmark name aliases.

Change Detection

The FFE only contains certain timestamps for a subset of the changes that can occur in the EAS database. Specifically, there are dedicated timestamps for when a:

  • base address was created (see the field base_address_create_tms in the table that is above)

  • base address was retired (see the field base_address_retire_tms in the table that is above))

  • unit (apartment or condominium) (sub)address was created (see the field unit_address_create_tms in the table that is above))

  • unit (apartment or condominium) (sub)address was retired (see the field unit_address_retire_tms in the table that is above))

  • (base or unit) address is linked to a parcel (see the field address_x_parcel_create_tms in the table that is above))

  • parcel in unlinked from a (base or unit) address (see the field address_x_parcel_retire_tms in the table that is above))

The FFE does not contain timestamps for every possible change that can occur in the EAS database. Specifically, there are no dedicated timestamps for a changed:

  • address complete street name

  • address location (coordinates)

  • address ZIP Code

  • address complete landmark name

  • address landmark name alias(es)

Examples

The following examples are all written using PostgreSQL 11 SQL.

Filter for all records that have changed within the last 7 days:

Code Block
languagesql
SELECT *
FROM   eas_flat_file_export
WHERE  (base_address_create_tms > (CURRENT_TIMESTAMP - INTERVAL '7 days'))
       OR (base_address_retire_tms > (CURRENT_TIMESTAMP - INTERVAL '7 days'))
       OR (unit_address_create_tms > (CURRENT_TIMESTAMP - INTERVAL '7 days'))
       OR (unit_address_retire_tms > (CURRENT_TIMESTAMP - INTERVAL '7 days'))
       OR (parcel_date_map_drop > (CURRENT_TIMESTAMP - INTERVAL '7 days'))
       OR (address_x_parcel_create_tms > (CURRENT_TIMESTAMP - INTERVAL '7 days'))
       OR (address_x_parcel_retire_tms > (CURRENT_TIMESTAMP - INTERVAL '7 days'));

Filter for all active base addresses only:

Code Block
languagesql
SELECT *
FROM   eas_flat_file_export
WHERE  (unit_address_base_flg)
       AND (base_address_retire_tms IS NULL);

Filter for all numbered thoroughfare addresses that have a complete landmark name:

Code Block
languagesql
SELECT *
FROM   eas_flat_file_export
WHERE  (address_type = 'Numbered Thoroughfare Address'));

...


       AND NOT(complete_landmark_name IS NULL);

Filter for all landmark addresses only:

Code Block
languagesql
SELECT *
FROM   eas_flat_file_export
WHERE  (address_type = 'Landmark Address');