Introduction

FGDC-STD-016-2011

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'));