Versions Compared

Version Old Version 15 New Version 16
Changes made by

Samuel Valdez

Samuel Valdez

Saved on

Key

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

...

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.

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.

Example

The following is an example of examples are all written using PostgreSQL 11 SQL to filter for any .

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   public.vw_addresses_flat_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');