location table structure

Contents

location table structure#

The Location table holds all the activity and household locations in the model, on which all trips will ultimately start and end.

Given its central importance in the model, this table is connected to multiple other tables, both through data links and geographic ones.

Required by all models.

Table Structure#

Field

Type

NULL allowed

Default Value

Foreign key

Description

location*

INTEGER

NO

Unique ID referencing a particular location in the model

link

INTEGER

NO

Link(link)

Foreign key reference to one link that is the closest spatially to the location

dir

INTEGER

NO

0

The direction of the link (ab or ba) that is closest to the location. Can be used to infer which side of the link this location may lie (but not trivial).

offset

REAL

NO

0

Auto-generated value of how far from the location (perpendicular) the link is present.

setback

REAL

NO

0

Auto-generated value of how far from node_a of the link this location is present along the length of the link

zone

INTEGER

YES

Zone(zone)

Foreign key reference to the zone where this location in situated. Auto-generated through geo-consistency checks.

x

REAL

NO

0

Auto-generated value of the x coordinate of the location specified in meters (UTM transform)

y

REAL

NO

0

Auto-generated value of the y coordinate of the location specified in meters (UTM transform)

area_type

INTEGER

NO

0

Foreign-key reference to Area_Type to define the general characteristic of the area where the location is situated (downtown, suburban, rural, etc.)

lu_area

REAL

NO

0

The area of the same land use aggregated around th

notes

TEXT

YES

“”

Text area to specify notes about the location. Optional.

census_zone

REAL

NO

0

FIPS code representing the census tract that this location is contained within. Census tract FIPS code is 11-digit long.

land_use

TEXT

NO

“ALL”

Land_Use(land_use)

Text describing the land use type represented by this location. Values at land_use.

walk_link

INTEGER

YES

Foreign key reference to the nearest walk link from the Transit_Walk table

walk_offset

REAL

YES

Same as offset above, but for the walk link.

bike_link

INTEGER

YES

Foreign key reference to the nearest bike link from the Transit_Bike table

bike_offset

REAL

YES

Same as offset above, but for the bike link.

avg_parking_cost

REAL

YES

0

Unused, replaced by Parking table and cost is weighted by all parking locatied at location.

res_charging

REAL

YES

Denotes the proportion of households at this location that have residential charging available.

stop_flag

INTEGER

YES

0

Boolean to denote whether or not a location is a TNC stop, primarily for aggregated pickups and dropoffs. May be deprecated.

geo

POINT

NO

‘’

(* - Primary key)

The SQL statement for table and index creation is below.

create TABLE IF NOT EXISTS "Location" (
    "location"         INTEGER NOT NULL PRIMARY KEY,
    "link"             INTEGER NOT NULL,
    "dir"              INTEGER NOT NULL DEFAULT 0,
    "offset"           REAL    NOT NULL DEFAULT 0,
    "setback"          REAL    NOT NULL DEFAULT 0,
    "zone"             INTEGER,
    "x"                REAL    NOT NULL DEFAULT 0,
    "y"                REAL    NOT NULL DEFAULT 0,
    "area_type"        INTEGER NOT NULL DEFAULT 0,
    "lu_area"          REAL    NOT NULL DEFAULT 0,
    "notes"            TEXT             DEFAULT "",
    "census_zone"      REAL    NOT NULL DEFAULT 0,
    "land_use"         TEXT    NOT NULL DEFAULT "ALL",
    "walk_link"        INTEGER,
    "walk_offset"      REAL,
    "bike_link"        INTEGER,
    "bike_offset"      REAL,
    "avg_parking_cost" REAL             DEFAULT 0,
    "res_charging"     REAL,
    "stop_flag"        INTEGER          DEFAULT 0,

    CONSTRAINT "link_fk" FOREIGN KEY("link") REFERENCES "Link"("link") DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT "zone_fk" FOREIGN KEY("zone") REFERENCES "Zone"("zone") DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT "land_use_fk" FOREIGN KEY("land_use") REFERENCES "Land_Use"("land_use") DEFERRABLE INITIALLY DEFERRED
);

select AddGeometryColumn( 'Location', 'geo', SRID_PARAMETER, 'POINT', 'XY', 1 );
select CreateSpatialIndex( 'Location' , 'geo');

create INDEX IF NOT EXISTS "notes_idx" ON "Location" ("notes");
create INDEX IF NOT EXISTS "loc_zone" ON "Location" ("zone");
create INDEX IF NOT EXISTS "location_idx" ON "Location" ("location");

Enums#

The following enums are used in this table.

land_use#

Enum Values#

Value

Description

0

LU_AGRICULTURE

1

LU_ALL

2

LU_BUSINESS

3

LU_CIVIC_RELIGIOUS

4

LU_CULTURAL

5

LU_DISTRIBUTION

6

LU_INTERMODAL

7

LU_EDUCATION

8

LU_HIGHER_EDUCATION

9

LU_HOTEL

10

LU_INDUSTRIAL

11

LU_MANUFACTURING

12

LU_SHOPPING

13

LU_MEDICAL

14

LU_MIXED_USE

15

LU_NONE

16

LU_NON_RESIDENTIAL

17

LU_RESIDENTIAL

18

LU_RESIDENTIAL_MULTI

19

LU_RECREATION

20

LU_SPECIAL_GENERATOR

21

TRANSIT_STOP

22

LU_SERVICE

23

LU_RETAIL

24

LU_RESTAURANT
Contents