Introduction

The EAS Flat File Export 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, for example all of the address creations and retirements, and all of the parcel-link creations and retirements that have ever occurred in the EAS.

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

Record Description

Schedule

The EAS Flat File Export 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 flat file export 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 Flat File Export:

	FIELD NAME	PostgreSQL 11 DATA TYPE	PostGIS 2.5 DATA TYPE	DESCRIPTION
1	id	text
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 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
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.

Example

The following is an example of PostgreSQL 11 SQL to filter for any 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'));