1 'PostgreSQL' Database Schema 'econd' Layout Generated using DbSchema Move the mouse over columns and symbols to read the tool-tip comments. effect_id watermark_id parentid cityscalecode detailcode orientationcode photographerprofessioncode qualitycode subjectmastercode subjectsubcode timeofdaycode typeofstructurecode haspeople isdetail hasscaleindication isinternal hasowner effect_id mappingid parentid parentid parentid mappingid parentid intensityzonecode soilclasscode isaggregated casualtylevelid damagelevelid inventoryclassid parentid assetclasscode assetconstructioncode assetsubtypecode metriccode typeofdamagecode2 typeofdamagecode yr_built_q llrs_duct_l floor_mat floor_type story_ag_q str_hzir_p irreg_q str_irreg llrs_l mas_mort_l mas_rein_l mat_tech_l mat_type_l occupcy occpcy_dt roofsysmat roofsystyp steel_conn_l str_veir_p lookupsubjectmastercode idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupcityscalecode idvarchar( 10 ) namevarchar( 255 ) weightint4 lookuporientationcode idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupyesnonull idint4 namevarchar( 50 ) weightint4 lookuptimeofdaycode idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupdetailcode idvarchar( 100 ) namevarchar( 255 ) weightint4 lookuptypeofstructurecode idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupphotoqualitycode idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupphotographerprofessioncode idvarchar( 10 ) namevarchar( 255 ) weightint4 photologue_photoeffect idserial background_colorvarchar( 7 ) brightnessfloat8 colorfloat8 contrastfloat8 descriptiontext filtersvarchar( 200 ) namevarchar( 30 ) reflection_sizefloat8 reflection_strengthfloat8 sharpnessfloat8 transpose_methodvarchar( 15 ) photologue_photosize idserial cropbool effect_idint4 heightint4 increment_countbool namevarchar( 40 ) pre_cachebool qualityint4 upscalebool watermark_idint4 widthint4 photologue_watermark idserial descriptiontext imagevarchar( 100 ) namevarchar( 30 ) opacityfloat8 stylevarchar( 5 ) photologue_photo idserial captionvarchar( 255 ) cityscalecodevarchar( 10 ) copyright_ctext crop_fromvarchar( 10 ) date_addedtimestamptz datetakentimestamp daysaftereventfloat4 description_ctext detailcodevarchar( 100 ) effect_idint4 gpsdirectionfloat4 gpsdirectioncodevarchar( 10 ) hasownerint4 haspeopleint4 hasscaleindicationint4 is_publicbool isdetailint4 isinternalint4 lastupdatetimestamp lastupdatebyidint4 namevarchar( 255 ) orientationcodevarchar( 10 ) owneridint4 parentidint4 parenttypevarchar( 25 ) ph_originalsurveyreferencevarchar( 50 ) photographercontactinfovarchar( 255 ) photographernamevarchar( 255 ) photographerorganisationvarchar( 255 ) photographerprofessioncodevarchar( 10 ) qualitycodevarchar( 10 ) subjectcodevarchar( 50 ) subjectmastercodevarchar( 10 ) subjectsubcodevarchar( 10 ) tagsvarchar( 255 ) timeofdaytimestamp timeofdaycodevarchar( 10 ) timeofdaystringvarchar( 20 ) titlevarchar( 255 ) title_slugvarchar( 50 ) typeofstructurecodevarchar( 10 ) view_countint4 casualtylevel idserial descriptionvarchar( 255 ) lastupdatetimestamp lastupdatebyidint4 levelorderint4 mappingidint4 namevarchar( 255 ) owneridint4 parentidint4 parenttypevarchar( 25 ) lookupmaterialtechnology idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupmasonrymortartype idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupmasonryreinforcement idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupsteelconnectiontype idvarchar( 20 ) namevarchar( 255 ) weightint4 lookuploadresistingsystem idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupfloortype idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupheight idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupdateofconstruction idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupirregularitytype idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupirregularityqualifier idvarchar( 20 ) namevarchar( 255 ) weightint4 lookuphorizontalirregularity idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupverticalirregularity idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupoccupancy idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupoccupancydetail idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupductility idvarchar( 20 ) namevarchar( 255 ) weightint4 lookuproofmaterial idvarchar( 20 ) namevarchar( 255 ) weightint4 lookuprooftype idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupassetclass idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupassettype idvarchar( 10 ) namevarchar( 255 ) parentidvarchar( 10 ) weightint4 lookupassetsubtype idvarchar( 10 ) namevarchar( 255 ) parentidvarchar( 10 ) weightint4 lookupassetconstruction idvarchar( 10 ) namevarchar( 255 ) parentidvarchar( 10 ) weightint4 damagelevel idserial descriptionvarchar( 255 ) lastupdatetimestamp lastupdatebyidint4 levelorderint4 mappingidint4 namevarchar( 255 ) owneridint4 parentidint4 parenttypevarchar( 25 ) unifiedcasualtylevel idint4 namevarchar( 255 ) weightint4 unifieddamagelevel idint4 namevarchar( 255 ) weightint4 lookupintensityzone idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupyesno idint4 namevarchar( 255 ) weightint4 lookupsoilclass idvarchar( 10 ) namevarchar( 255 ) weightint4 lookupsubjectsubcode idvarchar( 10 ) namevarchar( 255 ) parentidvarchar( 10 ) weightint4 location idserial addressvarchar( 255 ) boundary_ctext boundaryidint4 generalsoilconditions_ctext guiduuid( 2147483647 ) intensityzonecodevarchar( 10 ) isaggregatedint4 lastupdatetimestamp lastupdatebyidint4 locationvarchar( 50 ) location_ctext location_qvarchar( 10 ) namevarchar( 255 ) originalsurveyreferencevarchar( 50 ) owneridint4 parentidint4 parenttypevarchar( 25 ) soilclasscodevarchar( 10 ) the_geomgeometry( 2147483647 ) lookupmetric idvarchar( 10 ) namevarchar( 255 ) weightint4 lookuptypeofdamage idvarchar( 10 ) namevarchar( 255 ) weightint4 surveyvalue idserial parentidint4 assetclasscodevarchar( 10 ) assetconstructioncodevarchar( 10 ) assetsubtypecodevarchar( 10 ) assettypecodevarchar( 10 ) casualtylevelidint4 damagelevelidint4 inventoryclassidint4 lastupdatetimestamp lastupdatebyidint4 metriccodevarchar( 5 ) namevarchar( 64 ) originalsurveyreferencevarchar( 50 ) owneridint4 parentcasualtysurveymatrixidint4 parentdamagesurveymatrixidint4 structuretypecodevarchar( 10 ) typeofdamagecodevarchar( 10 ) typeofdamagecode2varchar( 10 ) valuefloat8 value_qvarchar( 10 ) vulnerabilityclasscodevarchar( 10 ) lookupmaterialtype idvarchar( 20 ) namevarchar( 255 ) weightint4 lookupfloormaterial idvarchar( 20 ) namevarchar( 255 ) weightint4 inventoryclass idserial namevarchar( 255 ) descriptionvarchar( 255 ) parenttypevarchar( 25 ) parentidint4 inventoryclass_ctext mat_type_lvarchar( 20 ) mat_tech_lvarchar( 20 ) mas_mort_lvarchar( 20 ) mas_rein_lvarchar( 20 ) steel_conn_lvarchar( 20 ) llrs_lvarchar( 20 ) llrs_duct_lvarchar( 20 ) roofsysmatvarchar( 20 ) roofsystypvarchar( 20 ) floor_matvarchar( 20 ) floor_typevarchar( 20 ) story_ag_qvarchar( 20 ) story_ag_1int4 story_ag_2int4 yr_built_qvarchar( 20 ) yr_built_1int4 yr_built_2int4 irreg_qvarchar( 20 ) str_irregvarchar( 20 ) str_veir_pvarchar( 20 ) str_hzir_pvarchar( 20 ) occupcyvarchar( 20 ) occpcy_dtvarchar( 20 ) designcodevarchar( 300 ) retrofitvarchar( 255 ) llrs_qualvarchar( 20 ) ht_gr_gf_qvarchar( 20 ) ht_gr_gf_1int4 ht_gr_gf_2int4 floor_connvarchar( 20 ) foundn_sysvarchar( 20 ) nonstrcexwvarchar( 20 ) plan_shapevarchar( 20 ) positionvarchar( 20 ) roof_connvarchar( 20 ) roof_shapevarchar( 20 ) roofcovmatvarchar( 20 ) slopeint4 story_bg_1int4 story_bg_2int4 story_bg_qvarchar( 20 ) levelorderint4 lastupdatetimestamp lastupdatebyidint4 owneridint4

casualtylevel
casualty levels table. Levels are members of a study
id serial NOT NULL Primary Key: Internal database id
description varchar( 255 ) casualty level description
lastupdate timestamp Last record update date
lastupdatebyid int4 NOT NULL DEFO 1 ID of the last person to update this record
levelorder int4 Use this field when the scale levels need to be in a particular order, eg when used in a survey matrix
mappingid int4 NOT NULL DEFO 0 Mapping to unified casualty level in the parent scales unified scale mapping
name varchar( 255 ) NOT NULL DEFO 'casualty level name'::character varying casualty level name
ownerid int4 NOT NULL DEFO 1 ID of the creator/owner of the record
parentid int4 NOT NULL DEFO 0 The study which owns this casualty level - normally no need to change this
parenttype varchar( 25 ) The type of parent which owns this. Set on creation, this must not be changed.
Indexes
casualtylevel_pkey primary key ( id)
Foreign Keys
fk_casualtylevel ( mappingid ) ref unifiedcasualtylevel ( id )


damagelevel
Damage levels table. Levels are members of a study
id serial NOT NULL Primary Key: Internal database id
description varchar( 255 ) Damage level description
lastupdate timestamp Last record update date
lastupdatebyid int4 NOT NULL DEFO 1 ID of the last person to update this record
levelorder int4 Use this field when the scale levels need to be in a particular order, eg when used in a survey matrix
mappingid int4 NOT NULL DEFO 0 Mapping to unified damage level in the parent scales unified scale mapping
name varchar( 255 ) NOT NULL DEFO 'Damage level name'::character varying Damage level name eg "collapsed", or "D3", or "yellow"
ownerid int4 NOT NULL DEFO 1 ID of the creator/owner of the record
parentid int4 NOT NULL DEFO 0 The study which owns this damage level - normally no need to change this
parenttype varchar( 25 ) The type of parent which owns this. Set on creation, this must not be changed.
Indexes
damagelevel_pkey primary key ( id)
Foreign Keys
fk_damagelevel ( mappingid ) ref unifieddamagelevel ( id )


inventoryclass
Inventory class table
id serial NOT NULL Primary Key: Internal database id
name varchar( 255 ) NOT NULL DEFO 'Inventory class name'::character varying Inventory class name from original survey
description varchar( 255 ) Inventory class description from original survey
parenttype varchar( 25 ) The type of study which owns this inventory class. Set on creation, this must not be changed.
parentid int4 NOT NULL DEFO 0 The study which owns this inventory class. Set on creation, this must not be changed.
inventoryclass_c text A comment on the inventory class
mat_type_l varchar( 20 ) NOT NULL DEFO '0'::character varying Material type: GEM Building Taxonomy Table 2 Level 1 attribute
mat_tech_l varchar( 20 ) NOT NULL DEFO '0'::character varying Material technology: GEM Building Taxonomy Table 2 Level 2 attribute
mas_mort_l varchar( 20 ) NOT NULL DEFO '0'::character varying Masonry mortar type: GEM Building Taxonomy Table 2 Level 3 attribute
mas_rein_l varchar( 20 ) NOT NULL DEFO '0'::character varying Masonry reinforcement: GEM Building Taxonomy Table 2 Level 3 attribute
steel_conn_l varchar( 20 ) NOT NULL DEFO '0'::character varying Steel connection: GEM Basic Building Taxonomy Table 2 Level 3 attribute
llrs_l varchar( 20 ) NOT NULL DEFO '0'::character varying Type of lateral load-resisting system: GEM Building Taxonomy Table 3 Level 1 attribute
llrs_duct_l varchar( 20 ) NOT NULL DEFO '0'::character varying System ductility: GEM Building Taxonomy Table 3 Level 2 attribute
roofsysmat varchar( 20 ) NOT NULL DEFO '0'::character varying Roof material: GEM Building Taxonomy Table 12 Level 3 attribute
roofsystyp varchar( 20 ) NOT NULL DEFO '0'::character varying Roof type: GEM Building Taxonomy Table 12 Level 4 attribute
floor_mat varchar( 20 ) NOT NULL DEFO '0'::character varying Floor material: GEM Building Taxonomy Table 11 Level 1 attribute
floor_type varchar( 20 ) NOT NULL DEFO '0'::character varying Floor type: GEM Building Taxonomy Table 11 Level 2 attribute
story_ag_q varchar( 20 ) NOT NULL DEFO '0'::character varying Height: GEM Building Taxonomy Table 4
story_ag_1 int4 Upper bound of height range (storeys)
story_ag_2 int4 Lower bound of height range (storeys)
yr_built_q varchar( 20 ) NOT NULL DEFO '0'::character varying Date of construction or retrofit: GEM Building Taxonomy Table 5
yr_built_1 int4 Upper bound of date of construction range (year)
yr_built_2 int4 Lower bound of date of construction range (year)
irreg_q varchar( 20 ) NOT NULL DEFO '0'::character varying
str_irreg varchar( 20 ) NOT NULL DEFO '0'::character varying Structural irregularity type: GEM Building Taxonomy Table 9 Level 3 attribute
str_veir_p varchar( 20 ) NOT NULL DEFO '0'::character varying Structural vertical irregularity description: GEM Building Taxonomy Table 9 Level 2 attribute
str_hzir_p varchar( 20 ) NOT NULL DEFO '0'::character varying Structural horizontal irregularity description: GEM Building Taxonomy Table 9 Level 2 attribute
occupcy varchar( 20 ) NOT NULL DEFO '0'::character varying Occupancy: GEM Building Taxonomy Table 6 Level 1 attribute
occpcy_dt varchar( 20 ) NOT NULL DEFO '0'::character varying Occupancy detail: GEM Building Taxonomy Table 6 Level 2 attribute
designcode varchar( 300 ) Applicable design code from original survey
retrofit varchar( 255 ) Type of retrofit from original survey
llrs_qual varchar( 20 ) DEFO 'D99'::character varying
ht_gr_gf_q varchar( 20 ) DEFO '0'::character varying
ht_gr_gf_1 int4
ht_gr_gf_2 int4
floor_conn varchar( 20 ) DEFO '0'::character varying
foundn_sys varchar( 20 ) DEFO '0'::character varying
nonstrcexw varchar( 20 ) DEFO '0'::character varying
plan_shape varchar( 20 ) DEFO '0'::character varying
position varchar( 20 ) DEFO '0'::character varying
roof_conn varchar( 20 ) DEFO '0'::character varying
roof_shape varchar( 20 ) DEFO '0'::character varying
roofcovmat varchar( 20 ) DEFO '0'::character varying
slope int4
story_bg_1 int4
story_bg_2 int4
story_bg_q varchar( 20 ) DEFO '0'::character varying
levelorder int4 Use this field when the inventory classes need to be in a particular order, eg when used in a survey matrix
lastupdate timestamp Last record update date
lastupdatebyid int4 NOT NULL DEFO 1 ID of the last person to update this record
ownerid int4 NOT NULL DEFO 1 ID of the creator/owner of the record
Indexes
inventoryclass_pkey primary key ( id)
Foreign Keys
fk_inventoryclass_13 ( yr_built_q ) ref lookupdateofconstruction ( id )
fk_inventoryclass_7 ( llrs_duct_l ) ref lookupductility ( id )
fk_inventoryclass_10 ( floor_mat ) ref lookupfloormaterial ( id )
fk_inventoryclass_11 ( floor_type ) ref lookupfloortype ( id )
fk_inventoryclass_12 ( story_ag_q ) ref lookupheight ( id )
fk_inventoryclass_15 ( str_hzir_p ) ref lookuphorizontalirregularity ( id )
fk_inventoryclass_19 ( irreg_q ) ref lookupirregularityqualifier ( id )
fk_inventoryclass_14 ( str_irreg ) ref lookupirregularitytype ( id )
fk_inventoryclass_6 ( llrs_l ) ref lookuploadresistingsystem ( id )
fk_inventoryclass_3 ( mas_mort_l ) ref lookupmasonrymortartype ( id )
fk_inventoryclass_4 ( mas_rein_l ) ref lookupmasonryreinforcement ( id )
fk_inventoryclass_2 ( mat_tech_l ) ref lookupmaterialtechnology ( id )
fk_inventoryclass_1 ( mat_type_l ) ref lookupmaterialtype ( id )
fk_inventoryclass_17 ( occupcy ) ref lookupoccupancy ( id )
fk_inventoryclass_18 ( occpcy_dt ) ref lookupoccupancydetail ( id )
fk_inventoryclass_8 ( roofsysmat ) ref lookuproofmaterial ( id )
fk_inventoryclass_9 ( roofsystyp ) ref lookuprooftype ( id )
fk_inventoryclass_5 ( steel_conn_l ) ref lookupsteelconnectiontype ( id )
fk_inventoryclass_16 ( str_veir_p ) ref lookupverticalirregularity ( id )


location
A location can be a geometry (point, line, polygon) or a reference to a predefined boundary (eg GADM) or an Intensity Zone. Other location specific data such as soil type can be provided.
id serial NOT NULL Primary Key: Internal database id
address varchar( 255 ) Optional address
boundary_c text A comment on the precision of the location or boundary
boundaryid int4 NOT NULL DEFO 0 Optional. A boundary within the geobase, which is defined in the parent study.
generalsoilconditions_c text
guid uuid( 2147483647 ) Optional globally unique identifier for compatibility with other GEM components
intensityzonecode varchar( 10 ) NOT NULL DEFO '0'::character varying Optional. Only use this if the location is an intensity zone: The intensity zone on a standard scale.
isaggregated int4 NOT NULL DEFO 1 This field determines if this location is a single asset (value 0) or an aggregation of assets (value 1, default)
lastupdate timestamp DEFO now() Last record update date
lastupdatebyid int4 NOT NULL DEFO 1 ID of the last person to update this record
location varchar( 50 ) Optional. The location in WKT format eg POINT (long lat). Can be null in which case use the boundary or intensity zone
location_c text An optional comment on the location and loss at the location
location_q varchar( 10 ) NOT NULL DEFO '0'::character varying The status of the data
name varchar( 255 ) NOT NULL DEFO 'Location name'::character varying Location name
originalsurveyreference varchar( 50 ) Optional original survey reference
ownerid int4 NOT NULL DEFO 1 ID of the creator/owner of the record
parentid int4 NOT NULL DEFO 0 The primary study or subevent which owns this location. Set on creation, this must not be changed.
parenttype varchar( 25 ) The type of primary study or subevent which owns this location. Set on creation, this must not be changed.
soilclasscode varchar( 10 ) NOT NULL DEFO '0'::character varying Optional. For future NEHRP Soil Class.
the_geom geometry( 2147483647 )
Indexes
location_pkey primary key ( id)
idx_location_the_geom ( the_geom)
location_parentid_idx ( parentid)
Foreign Keys
fk_location ( intensityzonecode ) ref lookupintensityzone ( id )
fk_location_0 ( soilclasscode ) ref lookupsoilclass ( id )
fk_location_1 ( isaggregated ) ref lookupyesno ( id )
Constraints
enforce_dims_the_geom public.st_ndims(the_geom) = 2
enforce_srid_the_geom public.st_srid(the_geom) = 4326


lookupassetclass
Asset class
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupassetclass_pkey primary key ( id)


lookupassetconstruction
Asset form of construction
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
parentid varchar( 10 ) NOT NULL DEFO 0
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupassetconstruction_pkey primary key ( id)
Foreign Keys
fk_lookupassetconstruction ( parentid ) ref lookupassetsubtype ( id )


lookupassetsubtype
Asset sub type
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
parentid varchar( 10 ) NOT NULL DEFO 0
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupassetsubtype_pkey primary key ( id)
Foreign Keys
fk_lookupassetsubtype ( parentid ) ref lookupassettype ( id )


lookupassettype
Asset type
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
parentid varchar( 10 ) NOT NULL DEFO 0
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupassettype_pkey primary key ( id)
Foreign Keys
fk_lookupassettype ( parentid ) ref lookupassetclass ( id )


lookupcityscalecode
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
pk_lookupcityscalecode primary key ( id)


lookupdateofconstruction
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupdateofconstruction_pkey primary key ( id)


lookupdetailcode
Photo detail code
id varchar( 100 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupdetailcode_pkey primary key ( id)


lookupductility
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupductility_pkey primary key ( id)


lookupfloormaterial
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupfloormaterial_pkey primary key ( id)


lookupfloortype
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupfloortype_pkey primary key ( id)


lookupheight
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupheight_pkey primary key ( id)


lookuphorizontalirregularity
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookuphorizontalirregularity_pkey primary key ( id)


lookupintensityzone
Intensity zone
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupintensityzone_pkey primary key ( id)


lookupirregularityqualifier
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupirregularityqualifier_pkey primary key ( id)


lookupirregularitytype
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupirregularitytype_pkey primary key ( id)


lookuploadresistingsystem
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookuploadresistingsystem_pkey primary key ( id)


lookupmasonrymortartype
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupmasonrymortartype_pkey primary key ( id)


lookupmasonryreinforcement
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupmasonryreinforcement_pkey primary key ( id)


lookupmaterialtechnology
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupmaterialtechnology_pkey primary key ( id)


lookupmaterialtype
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupmaterialtype_pkey primary key ( id)


lookupmetric
Metrics
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupmetric_pkey primary key ( id)


lookupoccupancy
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupoccupancy_pkey primary key ( id)


lookupoccupancydetail
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupoccupancydetail_pkey primary key ( id)


lookuporientationcode
Photo orientation code
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookuporientationcode_pkey primary key ( id)


lookupphotographerprofessioncode
Photographer profession code
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupphotographerprofessioncode_pkey primary key ( id)


lookupphotoqualitycode
Photo quality code
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupphotoqualitycode_pkey primary key ( id)


lookuproofmaterial
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookuproofmaterial_pkey primary key ( id)


lookuprooftype
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookuprooftype_pkey primary key ( id)


lookupsoilclass
For future NEHRP Soil Class.
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookupsoiltype_pkey primary key ( id)


lookupsteelconnectiontype
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupsteelconnectiontype_pkey primary key ( id)


lookupsubjectmastercode
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
pk_lookupsubjectmastercode primary key ( id)


lookupsubjectsubcode
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
parentid varchar( 10 ) NOT NULL DEFO 0
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
pk_lookupsubjectsubcode primary key ( id)
Foreign Keys
fk_lookupsubjectsubcode ( parentid ) ref lookupsubjectmastercode ( id )


lookuptimeofdaycode
Photo time of day code
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookuptimeofdaycode_pkey primary key ( id)


lookuptypeofdamage
Type of damage
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
lookuptypeofdamage_pkey primary key ( id)


lookuptypeofstructurecode
id varchar( 10 ) NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
pk_lookuptypeofstructurecode primary key ( id)


lookupverticalirregularity
id varchar( 20 ) NOT NULL
name varchar( 255 )
weight int4
Indexes
lookupverticalirregularity_pkey primary key ( id)


lookupyesno
id int4 NOT NULL
name varchar( 255 ) DEFO ''::character varying
weight int4 DEFO 0
Indexes
lookupyesno_pkey primary key ( id)


lookupyesnonull
id int4 NOT NULL
name varchar( 50 )
weight int4
Indexes
pk_lookupyesnonull primary key ( id)


photologue_photo
id serial NOT NULL
caption varchar( 255 )
cityscalecode varchar( 10 ) DEFO 0
copyright_c text
crop_from varchar( 10 ) NOT NULL DEFO 'center'::character varying
date_added timestamptz DEFO now()
datetaken timestamp
daysafterevent float4
description_c text
detailcode varchar( 100 ) DEFO '0'::character varying
effect_id int4
gpsdirection float4
gpsdirectioncode varchar( 10 )
hasowner int4 NOT NULL DEFO 0
haspeople int4 NOT NULL DEFO 0
hasscaleindication int4 NOT NULL DEFO 0
is_public bool NOT NULL DEFO true
isdetail int4 NOT NULL DEFO 0
isinternal int4 NOT NULL DEFO 0
lastupdate timestamp DEFO now() Last record update date
lastupdatebyid int4 NOT NULL DEFO 1 ID of the last person to update this record
name varchar( 255 ) DEFO 'unknown'::character varying
orientationcode varchar( 10 ) DEFO '0'::character varying
ownerid int4 NOT NULL DEFO 1 ID of the creator/owner of the record
parentid int4 NOT NULL DEFO 0
parenttype varchar( 25 )
ph_originalsurveyreference varchar( 50 )
photographercontactinfo varchar( 255 )
photographername varchar( 255 )
photographerorganisation varchar( 255 )
photographerprofessioncode varchar( 10 ) DEFO '0'::character varying
qualitycode varchar( 10 ) DEFO '0'::character varying
subjectcode varchar( 50 )
subjectmastercode varchar( 10 ) DEFO 0
subjectsubcode varchar( 10 ) DEFO 0
tags varchar( 255 )
timeofday timestamp
timeofdaycode varchar( 10 ) DEFO '0'::character varying
timeofdaystring varchar( 20 )
title varchar( 255 ) NOT NULL DEFO 'untitled'::character varying
title_slug varchar( 50 ) NOT NULL DEFO 'untitled'::character varying
typeofstructurecode varchar( 10 ) DEFO 0
view_count int4 NOT NULL DEFO 0
Indexes
pk_photo primary key ( id)
photologue_photo_parentid_idx ( parentid)
Foreign Keys
fk_photologue_photo_4 ( parentid ) ref location ( id )
fk_photologue_photo_13 ( cityscalecode ) ref lookupcityscalecode ( id )
fk_photologue_photo ( detailcode ) ref lookupdetailcode ( id )
fk_photologue_photo_0 ( orientationcode ) ref lookuporientationcode ( id )
fk_photologue_photo_3 ( photographerprofessioncode ) ref lookupphotographerprofessioncode ( id )
fk_photologue_photo_1 ( qualitycode ) ref lookupphotoqualitycode ( id )
fk_photologue_photo_10 ( subjectmastercode ) ref lookupsubjectmastercode ( id )
fk_photologue_photo_11 ( subjectsubcode ) ref lookupsubjectsubcode ( id )
fk_photologue_photo_2 ( timeofdaycode ) ref lookuptimeofdaycode ( id )
fk_photologue_photo_12 ( typeofstructurecode ) ref lookuptypeofstructurecode ( id )
fk_photologue_photo_9 ( haspeople ) ref lookupyesnonull ( id )
fk_photologue_photo_5 ( isdetail ) ref lookupyesnonull ( id )
fk_photologue_photo_6 ( hasscaleindication ) ref lookupyesnonull ( id )
fk_photologue_photo_7 ( isinternal ) ref lookupyesnonull ( id )
fk_photologue_photo_8 ( hasowner ) ref lookupyesnonull ( id )
effect_id_refs_id_52d77fd803aefc8e ( effect_id ) ref photologue_photoeffect ( id )


photologue_photoeffect
id serial NOT NULL
background_color varchar( 7 ) NOT NULL DEFO '#FFFFFF'::character varying
brightness float8 NOT NULL DEFO 1.0
color float8 NOT NULL DEFO 1.0
contrast float8 NOT NULL DEFO 1.0
description text NOT NULL
filters varchar( 200 ) NOT NULL
name varchar( 30 ) NOT NULL
reflection_size float8 NOT NULL DEFO 0
reflection_strength float8 NOT NULL DEFO 0.6
sharpness float8 NOT NULL DEFO 1.0
transpose_method varchar( 15 ) NOT NULL
Indexes
photologue_photoeffect_pkey primary key ( id)
photologue_photoeffect_name_key unique ( name)


photologue_photosize
id serial NOT NULL
crop bool NOT NULL DEFO false
effect_id int4
height int4 NOT NULL DEFO 0
increment_count bool NOT NULL DEFO false
name varchar( 40 ) NOT NULL
pre_cache bool NOT NULL DEFO false
quality int4 NOT NULL DEFO 70
upscale bool NOT NULL DEFO false
watermark_id int4
width int4 NOT NULL DEFO 0
Indexes
photologue_photosize_pkey primary key ( id)
photologue_photosize_name_key unique ( name)
photologue_photosize_effect_id ( effect_id)
photologue_photosize_watermark_id ( watermark_id)
Foreign Keys
effect_id_refs_id_7b5e1a978c67f1f5 ( effect_id ) ref photologue_photoeffect ( id )
watermark_id_refs_id_3eb6674c887783e0 ( watermark_id ) ref photologue_watermark ( id )
Constraints
photologue_photosize_height_check height >= 0
photologue_photosize_quality_check quality >= 0
photologue_photosize_width_check width >= 0


photologue_watermark
id serial NOT NULL
description text NOT NULL
image varchar( 100 ) NOT NULL
name varchar( 30 ) NOT NULL
opacity float8 NOT NULL DEFO 1
style varchar( 5 ) NOT NULL DEFO 'scale'::character varying
Indexes
photologue_watermark_pkey primary key ( id)
photologue_watermark_name_key unique ( name)


surveyvalue
Survey values table. Each record represents a single numeric value
id serial NOT NULL Primary Key: Internal database id
parentid int4 NOT NULL DEFO 0 The location which owns this survey value record - normally no need to change this
assetclasscode varchar( 10 ) NOT NULL DEFO '0'::character varying Asset class
assetconstructioncode varchar( 10 ) NOT NULL DEFO '0'::character varying Asset form of construction
assetsubtypecode varchar( 10 ) NOT NULL DEFO '0'::character varying Asset sub type
assettypecode varchar( 10 ) NOT NULL DEFO '0'::character varying Asset type
casualtylevelid int4 NOT NULL DEFO 0 casualty level in the chosen casualty scale
damagelevelid int4 NOT NULL DEFO 0 Damage level in the chosen damage scale
inventoryclassid int4 NOT NULL DEFO 0 Inventory class
lastupdate timestamp DEFO now() Last record update date
lastupdatebyid int4 NOT NULL DEFO 1 ID of the last person to update this record
metriccode varchar( 5 ) NOT NULL DEFO 'N'::character varying The metric of the value - number of buildings/assets, number of people, length m, height m, area sq m, volume cu m, cost $, ratio, damage per km
name varchar( 64 ) NOT NULL DEFO 'initialising'::character varying
originalsurveyreference varchar( 50 )
ownerid int4 NOT NULL DEFO 1 ID of the creator/owner of the record
parentcasualtysurveymatrixid int4 NOT NULL DEFO 0 If this survey value was created through a survey matrix this records its id
parentdamagesurveymatrixid int4 NOT NULL DEFO 0 If this survey value was created through a survey matrix this records its id
structuretypecode varchar( 10 ) NOT NULL DEFO '0'::character varying
typeofdamagecode varchar( 10 ) NOT NULL DEFO '0'::character varying Type of damage
typeofdamagecode2 varchar( 10 ) NOT NULL DEFO '0'::character varying
value float8 The survey value, a single number or percentage
value_q varchar( 10 ) NOT NULL DEFO '0'::character varying The status of the data
vulnerabilityclasscode varchar( 10 ) NOT NULL DEFO '0'::character varying
Indexes
surveyvalue_pkey primary key ( id)
idx_surveyvalue ( parentid, inventoryclassid, damagelevelid)
idx_surveyvalue_1 ( parentid, inventoryclassid, casualtylevelid)
surveyvalue_parentid_idx ( parentid)
Foreign Keys
fk_surveyvalue_3 ( casualtylevelid ) ref casualtylevel ( id )
fk_surveyvalue_2 ( damagelevelid ) ref damagelevel ( id )
fk_surveyvalue ( inventoryclassid ) ref inventoryclass ( id )
fk_surveyvalue_0 ( parentid ) ref location ( id )
fk_surveyvalue_5 ( assetclasscode ) ref lookupassetclass ( id )
fk_surveyvalue_8 ( assetconstructioncode ) ref lookupassetconstruction ( id )
fk_surveyvalue_7 ( assetsubtypecode ) ref lookupassetsubtype ( id )
fk_surveyvalue_6 ( assettypecode ) ref lookupassettype ( id )
fk_surveyvalue_1 ( metriccode ) ref lookupmetric ( id )
fk_surveyvalue_9 ( typeofdamagecode2 ) ref lookuptypeofdamage ( id )
fk_surveyvalue_4 ( typeofdamagecode ) ref lookuptypeofdamage ( id )


unifiedcasualtylevel
unifiedcasualtylevel
id int4 NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
unifiedcasualtylevel_pkey primary key ( id)


unifieddamagelevel
unifieddamagelevel
id int4 NOT NULL Primary Key: lookup code
name varchar( 255 ) DEFO ''::character varying The name
weight int4 DEFO 0 Controls order in which items are displayed in menus
Indexes
unifieddamagelevel_pkey primary key ( id)