Click or drag to resize

IMPORT STREETS Statement

Verizon Connect Logo
Print this page
Learn more about Verizon Connect GeoBase.
Get information about the latest release
IMPORT STREETS Statement

This statement converts GIS-formatted street data to GBFS data.

Caution note Caution

Several IMPORT STREETS statements can be used in succession, but a COMMIT statement must be made before the destination table is updated.

If you're importing turn restrictions (using the IMPORT CONSTRAINTS statement) ensure that all restrictions relevant to the streets in this IMPORT STREETS statement have been imported before you COMMIT the street data, otherwise the IMPORT CONSTRAINTS statement will fail.

Note Note

For streets with more than one name, these available names will be sorted in the following order:

  1. Any name which is stale (STALENAME = 'Y') or is a postal name (POSTALNAME = 'Y') is given a lower priority

  2. For highways (ROUTE_TYPE != ' '), the most important classification will win (for example, a name with ROUTE_TYPE = '1' will beat ROUTE_TYPE = '3'). If both names have the same classification, they will be sorted according to the remaining criteria

  3. A name which is displayed on a road sign (NAMEONRDSN = 'Y') will be given preference

  4. If all other criteria are the same, and it is not a highway, names will be sorted in the order in which they appear in the source data.

Note, however, that some older GBFS files may be sorted alphabetically in step four.

Syntax

Alchemy
IMPORT STREETS [id = %id_col, gbfs1 = %col1] FROM "path\src_file" WHERE condition;

Nomenclature

id_colA column of unique Link ID identifiers in the source table. Note that Link ID values of 0 and 1 are reserved and cannot be imported.
gbfsX One or more GBFS columns. See the 'Columns' section below for a list of appropriate GBFS columns.
colX An expression referencing one or more GIS columns in src_table. This column will be converted to GBFS data.
path\src_file Specifies the path and file containing the GIS shapefile data.
condition A logic expression that is satisfied by a subset of the data in path\src_file. This expression, along with the WHERE keyword, is optional.

Example

By opening the sample 'travel_route.dbf' shapefile (found in the 'GeoBase Examples\Alchemy\Allegheny' folder) using a spreadsheet package, we see something similar to the following: (Note that a number of columns and rows have been hidden).

sfi alleghenyroads

We will use the IMPORT STREETS command to import this data. The 'ID' column (above) provides a unique ID for each road, and we will use the 'ROAD_NAME' column as the name. We will conditionally mark roads as suitable for vehicles using the ar_auto flag (see 'Columns' section, below) - if the 'TRAILTYPE' is 'hiking' we set ar_auto to false otherwise ar_auto is true. We will mark all roads as accessible to pedestrians (using the ar_pedest flag).

Alchemy
IMPORT STREETS [
  id = %ID,
  name = %ROAD_NAME,
  ar_auto = not string.contains(%TRAILTYPE, "hiking"),
  ar_pedest = true
] FROM "travel_route";

IMPORT STREETS with CSV Files

To import streets or polygons from CSV format text files, use the following format.

Alchemy
IMPORT STREETS [id = %no, name = %name, geom = wkt(%wkt)] FROM "csv://src_file";

Nomenclature

idA column of unique Link ID identifiers in the CSV source file. Note that Link ID values of 0 and 1 are reserved and cannot be imported.
name An expression referencing one or more column in the CSV source file.
geom A column of shape data stored as well known text (WKT) in the CSV source file.

Example

CSV
id,road_name,wkt_data
1,North Road,"LINESTRING(-32.16641096 18.80887248,-32.16641096 18.80887248,-32.16916157 18.81348722,
-32.16846419 18.81283813,-32.16789691 18.81174513,-32.16754688 18.81020822,-32.16714052 18.80891003,
-32.16652898 18.80870484,-32.16565726 18.80856805,-32.16475604 18.80860292,-32.16399966 18.80891003,
-32.16350479 18.80955913,-32.16321377 18.81017335,-32.16272024 18.81130122,-32.16208054 18.81225743,
-32.16085879 18.81246262,-32.15994952 18.8119141,-32.15931116 18.80993329,-32.15884579 18.80771242,
-32.15875862 18.80528771,-32.15884579 18.80303331,-32.15893162 18.80043827,-32.15893162 18.79811548)"

The IMPORT STREETS statement required to import data from the example CSV file shown above is:

Alchemy
IMPORT STREETS [
  id = %id,
  name = %road_name,
  geom = wkt(%wkt_data)
] FROM "csv://mycsvfile.csv";

The 'id' column provides a unique ID for each road, and we will use the 'road_name' column as the name. The 'wkt_data' column provides street polygons stored as LineStrings or MultiLineStrings.

Note Note

Import files should include either streets or polygons, not both, within the same file.

Geometry data stored in the CSV file should be contained within quotes, unless data is separated by pipes (|); for example:

CSV
id|road_name|wkt_data
1|North Road|LINESTRING(-32.16641096 18.80887248,-32.16641096 18.80887248,-32.16916157 18.81348722,-32.16846419 18.81283813,-32.16789691 18.81174513,-32.16754688 18.81020822,-32.16714052 18.80891003,-32.16652898 18.80870484,-32.16565726 18.80856805,-32.16475604 18.80860292,-32.16399966 18.80891003,-32.16350479 18.80955913,-32.16321377 18.81017335,-32.16272024 18.81130122,-32.16208054 18.81225743,-32.16085879 18.81246262,-32.15994952 18.8119141,-32.15931116 18.80993329,-32.15884579 18.80771242,-32.15875862 18.80528771,-32.15884579 18.80303331,-32.15893162 18.80043827,-32.15893162 18.79811548)

Note that the import script must include an appropriate suffix to indicate the field separator if using pipes. See the Alchemy Input Files section for more details. For pipes, this suffix should be ^P. For example:

Alchemy
IMPORT STREETS [
  id = %id,
  name = %road_name,
  geom = wkt(%wkt_data)
] FROM "csv://mycsvfile.csv^P";

Column Names

Note Note

Street Orientation: Left, Right, Reference- and Non-Reference-Nodes

In order to aid location finding and route mapping, each street segment or "link" has one of its ends designated as the "reference node"; similarly, the opposite end is the "non-reference node". Accordingly, the left and right sides of a link are defined for an observer standing at the reference node, facing towards the non-reference node. These definitions affect columns such as RPOSTCODE, LADDRSCH and RNREFADDR, among others.

The following GBFS columns are available for population through the Alchemy IMPORT STREETS statement.

Tip Tip

To see which columns are required for certain types of functionality refer to the Functionality and Column Usage topic.

Column

Description

Type

Length

Values

Used For

AR_AUTO

Automobile access.

Boolean

1

Y: allowed

N: not allowed

Navigation

Routing

AR_BUS

Bus access.

Boolean

1

Y: allowed

N: not allowed

Navigation

Routing

AR_CARPOOL

Carpool access.

Boolean

1

Y: allowed

N: not allowed

Navigation

Routing

AR_DELIV

Delivery access.

Boolean

1

Y: allowed

N: not allowed

Navigation

Routing

AR_EMERVEH

Emergency vehicle access.

Boolean

1

Y: allowed

N: not allowed

Routing

AR_PEDEST

Pedestrian access.

Boolean

1

Y: allowed

N: not allowed

Routing

AR_TAXIS

Taxi access.

Boolean

1

Y: allowed

N: not allowed

Routing

AR_TRAFF

Automobile through traffic access.

Public streets with posted or legal restrictions such as "No Through Traffic" or "Residents Only", links that are internal to a parking lot and links that are on a military or privately maintained road and do not allow public thoroughfare are also included in this class.

Boolean

1

Y: allowed

N: not allowed

Routing

AR_TRUCK

Truck access.

Boolean

1

Y: allowed

N: not allowed

Routing

BASENAME

Base name: name in an official language. Note that there can be multiple official languages for a given administrative area.

Text

80

GeoCoding

BRIDGE

Bridge, or overbridge.

Boolean

1

Y: bridge

N: not a bridge

Navigation

CONTRACC

Controlled access: identifies high speed, high volume roads with limited entrances and exits - such as a freeway.

Boolean

1

Y: controlled access

N: not controlled

Navigation

Routing

DIR_TRAVEL

Direction of travel: indicates allowed direction of travel for a given link.

Text

1

(space): not applicable

B: both directions

F: from reference node

T: to reference node

Navigation

Routing

DIRONSIGN

Direction on sign: official directional identifiers used for highways. 'E' for "East-Bound Highway 120".

Text

1

Blank: not applicable

E: East

N: North

S: South

W: West

DIVIDER

Traffic-blocking divider: indicates access restrictions that prevent left turns (in right-side driving countries) or right turns (in left-side driving countries) and u-turns. If restrictions are present, they can apply to the link only, or to the link and one or other or both of its nodes.

Text

1

A: both nodes and link

L: link only

N: no divider

1: reference node and link

2: non-reference node and link

Routing

DIVIDERLEG

Legal divider: used in combination with DIVIDER to indicate whether a divider is a physical barrier (such as a concrete wall), or whether it is only a statutory barrier (such as painted lines). Statutory barriers could be ignored in the event of an emergency.

Boolean

1

Y: divider is a statutory barrier

N: divider is a physical barrier

Routing

EXITNUMBER

Determines whether the name of this link is an exit number (the (alpha-) numeric name given to a highway exit).

Boolean

1

Y: exit name

N: not an exit name

Navigation

FERRY_TYPE

Ferry type: indicates the type of ferry necessary to access a certain route.

Text

1

B: sea ferry required

R: rail ferry required

H: road - accessible to automobiles

Navigation

FRONTAGE

Frontage or service road: runs parallel to a main road, allowing access to residences and businesses. Has the same name and addresses as the main road.

Boolean

1

Y: frontage road

N: not a frontage road

FULL_GEOM

Full geometry: indicates that all of the intersections connecting with a link are mapped.

Boolean

1

Y: full geometry

N: not full geometry - some intersections may not be mapped

FUNC_CLASS

Functional class: indicates the volume and speeds that a road can sustain. 1 is for highways and main routes, 5 for low-speed, low-volume intra-neighborhood roads. Affects the value of ROAD_TYPE.

Text

1

(space): not applicable

1: level 1

2: level 2

3: level 3

4: level 4

5: level 5

Routing

ID

An identifier unique to each link. Required. Cannot be 0 or 1.

Number

Must fit in 64-bits

Required

INDESCRIB

Indescribable link: links such as turning lanes, traffic islands, u-turn lanes and on-ramps. Often associated with road intersections.

Boolean

1

Y: indescribable

N: not indescribable

INPROCDATA

In process data: indicates that some routing data for a particular link may be missing or inaccurate.

Boolean

1

Y: in process data

N: not in process data

INTERINTER

Internal intersection link. This indicates that a road segment should be viewed as part of the intersection, instead of an individual piece of road.

Boolean

1

Y: intersection internal

N: not intersection internal

Navigation

Routing

ISMAJOR

Major highway, such as those used to link states or countries.

Boolean

1

Y: major highway

N: not a major highway

JUNCTIONNM

Junction name: this name applies to all named exits, entries and ramp systems for this junction.

Boolean

1

Y: junction name

N: not a junction name

L_REGION n

Left region: an n-tiered hierarchical label indicating the administrative, physical, or political localities that lie to the left of a road. Required for GeoCoding

Text

Variable width

GeoCoding (Required)

LADDRSCH

Left address scheme: address numbers on the left side of the link.

Text

1

E: even

M: mixed

O: odd

LANE_CAT

Lane category: identifies roads by the number of lanes in each direction.

Char

1

(space): not applicable

1: one lane per direction

2: two or three lanes per direction

3: four or more lanes per direction

Routing

LANG

Language code: the language associated with a road name. Only languages officially recognized by a country can be used to name roads in that country.

Text

3

ENG: English

FRE: French

SPA: Spanish

complete list

LNREFADDR

Left non-reference address: the address or house number on the left side of the link at the non-reference end.

Text

10

LPOSTCODE

Left postal code: the postal code on the left side of the link.

Text

11

GeoCoding

LREFADDR

Left reference address: the address or house number on the left side of the link at the reference end

Text

10

MANEUVER

Maneuver: indicates a group of one or more links that only require one maneuver for the purposes of explication.

Boolean

1

Y: maneuver

N: not maneuver

Navigation

Routing

MAXATTR

Maximum attributes: indicates whether a link is part of a detailed coverage area.

Boolean

1

Y: part of detailed coverage area

N: not part of detailed coverage area

MULTIDIGIT

Multiply digitized: indicates one traffic direction per link, rather than one road per link.

Boolean

1

Y: multiply digitized

N: not multiply digitized

Navigation

NAME

Street name: includes any prefixes or suffixes, as well as the street type.

Text

Variable width

GeoCoding

Navigation

NAMEONRDSN

Name on road sign: indicates the official name of a road, which is normally the one posted on the road sign.

Boolean

1

Y: name on road sign

N: not name on road sign

NM_PREF

The prefix link type embedded within NAME.

Text

Variable width

NM_SUFF

The link suffix type embedded within NAME (such as 'weg' in 'Bichlweg').

Text

Variable width

PAVED

Paved road: differentiates between sealed or paved roads, and dirt roads.

Boolean

1

Y: paved

N: not paved

Routing

POIACCESS

Point of interest (POI) access road: indicate the only links between POIs and the road network.

Boolean

1

Y: POI access

N: not POI access

POSTALNAME

Postal name: auxiliary link names supplied by postal service files.

Boolean

1

Y: postal name

N: not a postal name

POSTED_SPEED

Posted speed: confirms that the value in SPEED_LIM is factual.

Boolean

1

Y: posted / confirmed

N: not posted

Routing

PRIVATE

Private: indicates private roads.

Boolean

1

Y: private road

N: not a private road

Routing

R_REGION n

Right region: an n-tiered hierarchical label indicating the administrative, physical, or political localities that lie to the right of a road. Required for GeoCoding

Text

Variable width

GeoCoding (Required)

RADDRSCH

Right address scheme: address numbers on the right side of the link.

Text

1

E: even

M: mixed

O: odd

RAMP

Ramp: a link that connects two roads that would otherwise not intersect. Ramps also connect controlled access roads to uncontrolled access roads.

Boolean

1

Y: ramp

N: not a ramp

Navigation

Routing

RNREFADDR

Right non-reference address: the address or house number on the right side of the link at the non-reference end.

Text

10

ROUNDABOUT

Roundabout: a one-way traffic circle that controls the traffic flow between two or more roads.

Boolean

1

Y: roundabout

N: not a roundabout

GeoCoding

Navigation

Routing

ROUTE_TYPE

Route type: an integer value indicating that this link is part of a larger route, where often the route number and road name are the same. Affects the value of ROAD_TYPE.

Text

1

(space): not applicable

1: U.S. interstate or European level 1 road

2: U.S. federal or European level 2 road

3: U.S. state or European level 3 road

4: U.S. county or European level 4 road

5: European level 5 road

6: European level 6 road

Navigation

Routing

RPOSTCODE

Right postal code: the postal code on the right side of the link.

Text

11

GeoCoding

RREFADDR

Right non-reference address: the address or house number on the right side of the link at the reference end.

Text

10

SPECTRFIG

Special traffic figure: an intersection similar to a roundabout, but with either more than one direction of travel around it or a road crossing through it.

Boolean

1

Y: special traffic figure

N: not special traffic figure

SPEED_CAT

Speed category: represents the speed 'trend' of a road, which is determined by several factors including the legal speed limit and any access restrictions to the road. Speed category helps to provide more accurate route timing information. Affects the value of ROAD_TYPE.

Text

1

(space): not applicable

1: > 130 kph, > 80 mph

2: 101-130 kph, 65-80 mph

3: 91-100 kph, 55-64 mph

4: 71-90 kph, 41-54 mph

5: 51-70 kph, 31-40 mph

6: 31-50 kph, 21-30 mph

7: 11-30 kph, 6-20 mph

8: < 11 kph, < 6 mph

Routing

SPEED_LIM

The legal speed limit of the road.

Numeric

5

Blank

1-999

Routing

STALENAME

Stale name: indicates a name that is still used colloquially, but is no longer officially recognized. May be used for destination selection but should never be used for route guidance or map display.

Boolean

1

Y: stale name

N: not a stale name

TOLLWAY

Tollway: indicates a link that requires a fee to be paid when traveled upon.

Boolean

1

Y: tollway

N: not a tollway

Navigation

Routing

TUNNEL

Tunnel.

Boolean

1

Y: tunnel

N: not a tunnel

TYP_AFT

The suffix link type (such as 'Street' in 'Murray Street'). Not to be confused with NM_SUFF.

Text

Variable width

GeoCoding

TYP_BEF

The prefix link type (such as 'Rue' in 'Rue Chalet'). Not to be confused with NM_PREF.

Text

Variable width

GeoCoding

UNDEFTRAFF

Undefined traffic area: indicates links in paved areas without legally defined traffic paths that may still be navigable to cars.

Boolean

1

Y: undefined traffic area link

N: not undefined traffic area link

URBAN

Urban: indicates links in built-up areas.

Boolean

1

Y: urban

N: not urban

ZLEVEL

Z-level: the relative vertical position of a shape point or link.

Array

Variable width - one element for every point along the link, that is: numpoints(%geom)

Each element must be a positive or negative integer, indicating the relative vertical position of the corresponding point on the link.

Routing