sign table structure

For non-signalized intersections, this table lists what type of stop sign is posted in each approximation

Table Structure

Field	Type	NULL allowed	Default Value	Foreign key	Description
sign_id*	INTEGER	NO			sign identifier
link	INTEGER	NO		Link(link)	bidirectional link in which the sign is in
dir	INTEGER	NO			direction (0 for AB, 1 for BA) in which the sign is in
nodes	INTEGER	NO	-1		incident node of the sign (Set by POLARIS)
sign	TEXT	NO	‘’		sign type (ALL_STOP/STOP)

(* - Primary key)

The SQL statement for table and index creation is below.

create TABLE IF NOT EXISTS "Sign" (
    sign_id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    link        INTEGER NOT NULL,
    dir         INTEGER NOT NULL,
    nodes       INTEGER NOT NULL DEFAULT -1,
    sign        TEXT    NOT NULL DEFAULT '',

    CONSTRAINT "link_fk" FOREIGN KEY("link") REFERENCES "Link"("link") DEFERRABLE INITIALLY DEFERRED -- check
);

CREATE INDEX IF NOT EXISTS "Sign_node_idx" ON "Sign" ("nodes");
CREATE INDEX IF NOT EXISTS "Sign_link_idx" ON "Sign" ("link");

CREATE UNIQUE INDEX IF NOT EXISTS "Sign_uniqueness" ON "Sign" (nodes, link, dir);