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 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 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)”. This is not an “EAS record”, nor an “EAS address record”.

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

This is the schema of the EAS FFE:

	FIELD NAME	PostgreSQL 11 DATA TYPE	PostGIS 2.5 DATA TYPE	DESCRIPTION

	FIELD NAME	PostgreSQL 11 DATA TYPE	PostGIS 2.5 DATA TYPE	DESCRIPTION
1	id	text		“eas_address_base_id” (field #2) + “-” + “eas_unit_address_id“ (field #12) + “-” + “eas_address_x_parcel_id“ (field #22) Conceptually: “base address table record identifier” + “-” + “unit address table record identifier” + “-” + “parcel link table record identifier”
2	eas_address_base_id	integer
3	base_address_num	integer		See Address Number in the EAS Glossary. page 37
4	base_address_suffix	character varying(10)		See Address Number Suffix in the EAS Glossary. page 39
5	street_name	character varying(60)		See Street Name in the EAS Glossary. page 54
6	street_type	character varying(6)		See Street Name Post Type in the EAS Glossary. page 56
7	street_post_direction	character varying(10)		See Street Name Post Directional in the EAS Glossary. page 59
8	street_cnn	integer
9	street_full_street_name	character varying(255)		See Complete Street Name in the EAS Glossary. page 63
10	base_address_create_tms	timestamp without time zone
11	base_address_retire_tms	timestamp without time zone
12	eas_unit_address_id	integer		See EAS Address Identifier (`eas_address_id`) in the EAS Glossary.
13	unit_address_base_flg	boolean
14	unit_address	character varying(10)		See Subaddress Identifier in the EAS Glossary. page 78
15	unit_address_create_tms	timestamp without time zone
16	unit_address_retire_tms	timestamp without time zone
17	disposition_description	character varying(20)		official, placeholder Generally, you will not be interested in placeholder addresses, for example “0 UNKNOWN”.
18	map_block_lot	character varying(10)
19	block_lot	character varying(9)		See Assessor Parcel Number in the EAS Glossary.
20	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.
21	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.
22	eas_address_x_parcel_id	integer
23	address_x_parcel_create_tms	timestamp without time zone
24	address_x_parcel_retire_tms	timestamp without time zone
25	zipcode	character varying(50)		See Zip Code in the EAS Glossary. page 96
26	longitude	float		In the WGS 84 spatial reference system. See EPSG:4326.
27	latitude	float		In the WGS 84 spatial reference system. See EPSG:4326.
28	geometry		geometry	In the NAD83 / California zone 3 (ftUS) spatial reference system. See EPSG:2227.
29	complete_landmark_name	character varying(150)
30	address_type	text		“Landmark Address“, “Numbered Thoroughfare Address“ One of the values that are above, and is used to filter records of the respective address types. By definition, a numbered thoroughfare address may have a complete landmark name, while a landmark address will never have a value in the `base_address_num`, `base_address_suffix`, `street_name`, `street_type`, `street_post_direction`, `street_cnn`, and `street_full_street_name` fields.
31	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:

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:

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:

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: