D 2018-08-22T08:55:52.276 L Virtual\sTables\s(misc) P c14fe1d90e3238e66d8c5e1b1690a50c316e8d30 U sandro W 13210 back to main page

VirtualShape

The VirtualShape Interface allows to directly access an external ShapeFile by using standard Spatial SQL queries.
Syntax for creating a VirtualShape Table: SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape ( shp_path , charset_encoding , srid ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape ( shp_path , charset_encoding , srid , text_dates ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape ( shp_path , charset_encoding , srid , text_dates , colname_case ); Supported arguments and their interpretation:

shp_path (mandatory): the relative or absolute pathname leading to the external Shapefile.
- Note: any Shapefile requires at least three individual members sharing the same base name but with different suffixes (*.shp, *.shx and *.dbf).
  All suffixes will be automatically handled by VirtualShape, and consequently the shp_path argument must not specify any prefix, just the bare base path must be passed.
charset_encoding (mandatory): canonical name of the charset encoding adopted by the DBF member.
- Examples of valid names are: ASCII, UTF-8, CP850, ISO-8849-1
- You can check the full list of supported charset encodings from here.
srid (mandatory): the SRID value of all Geometries within the Shapefile.
- Examples of valid SRID values are: 4326, 3003, 32632
text_dates (optional): this is a BOOLEAN flag:
- if set to 0 (FALSE) all fields of the DATE type will be represented as julian dates
- if set to any other integer value (TRUE) all fields of the DATE type will be represented as Text Strings as e.g. 2018-08-21
- if not explicitly specified, TRUE will always be assumed (default setting).
colname_case (optional): this argument specifies if all column names should be considered uppercase or lowercase.
Valid settings are:
- uppercase or upper: all column names will be fully capitalized.
- lowercase or lower: all column names will be full lowercase.
- samecase or same: all column names will be exactly preserved as they are defined into the DBF memeber.
- if not specified, or if any other definition definition is found, lowercase will always be assumed (default setting).

Note: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect. SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape( /home/sandro/myshp , CP1252 , 3003 , 1 , lower ); or SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape( '/home/sandro/myshp' , 'CP1252' , 3003 , 1 , 'lower' ); Both notations will produce exactly the same identical result.

Best practices and known limitations

Virtual Tables are not really genuine Database Tables, they are just pretending to be.
Virtual Tables have very poor speed performances when compared to genuine Database Tables.
Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, if one or more of the JOINed Tables are of the Virtual type.
The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.

VirtualDBF

The VirtualDBF Interface allows to directly access an external DBF table by using standard Spatial SQL queries.
Syntax for creating a VirtualDbf Table: SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf ( dbf_path , charset_encoding ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf ( dbf_path , charset_encoding , text_dates ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf ( dbf_path , charset_encoding , text_dates , colname_case ); Supported arguments and their interpretation:

dbf_path (mandatory): the relative or absolute pathname leading to the external DBF file.
- Note: the full pathname must be passed, including the final *.dbf suffix.
charset_encoding (mandatory): canonical name of the charset encoding adopted by the DBF member.
- Examples of valid names are: ASCII, UTF-8, CP850, ISO-8849-1
- You can check the full list of supported charset encodings from here.
text_dates (optional): this is a BOOLEAN flag:
- if set to 0 (FALSE) all fields of the DATE type will be represented as julian dates
- if set to any other integer value (TRUE) all fields of the DATE type will be represented as Text Strings as e.g. 2018-08-21
- if not explicitly specified, TRUE will always be assumed (default setting).
colname_case (optional): this argument specifies if all column names should be considered uppercase or lowercase.
Valid settings are:
- uppercase or upper: all column names will be fully capitalized.
- lowercase or lower: all column names will be full lowercase.
- samecase or same: all column names will be exactly preserved as they are defined into the DBF memeber.
- if not specified, or if any other definition definition is found, lowercase will always be assumed (default setting).

Note: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect. SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf( /home/sandro/myshp , CP1252 , 1 , lower ); or SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf( '/home/sandro/myshp' , 'CP1252' , 1 , 'lower' ); Both notations will produce exactly the same identical result.

Best practices and known limitations

Virtual Tables are not really genuine Database Tables, they are just pretending to be.
Virtual Tables have very poor speed performances when compared to genuine Database Tables.
Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, if one or more of the JOINed Tables are of the Virtual type.
The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.

VirtualText

The VirtualText Interface allows to directly access an external Text file by using standard Spatial SQL queries.
The Text File is expected to contain a properly formatted Table accordingly to some Delimiter-Separated-Values format (comma separated values *.csv, tab separated values *.txt and alike).
Syntax for creating a VirtualText Table: SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles , decimal_separator ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles , decimal_separator , text_separator ); SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles , decimal_separator , text_separator , field_separator ); Supported arguments and their interpretation:

textfile_path (mandatory): the relative or absolute pathname leading to the external Text file.
- Note: the full pathname must be passed, including an eventual final suffix (*.txt, *.csv and alike).
charset_encoding (mandatory): canonical name of the charset encoding adopted by the Text file.
- Examples of valid names are: ASCII, UTF-8, CP850, ISO-8849-1
- You can check the full list of supported charset encodings from here.
first_row_as_titles (optional): this is a BOOLEAN flag:
- if set to 0 (FALSE) all column names will be automatically set by using a progressive sequence.
- if set to any other integer value (TRUE) all column names will by expected to be defined in the very first row of the Text file.
- if not explicitly specified, TRUE will always be assumed (default setting).

decimal_separator (optional): the character acting as a separator between the integer and decimal parts of a number.
Valid values are any printable character or the special mnemonic notations POINT or COMMA.
If not explicitly specified POINT will always be assumed (default setting)
text_separator (optional): the character acting as a string delimiter.
Valid values are any printable character or the special mnemonic notations SINGLEQUOTE, DOUBLEQUOTE or NONE.
If not explicitly specified DOUBLEQUOTE will always be assumed (default setting)
field_separator (optional): the character acting as a delimiter between consecutive fields.
Valid values are any printable character or the special mnemonic notations TAB.
If not explicitly specified TAB will always be assumed (default setting)

Note: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect. SELECT CREATE VIRTUAL TABLE my_table USING VirtualText( /home/sandro/myfile.txt , CP1252 , 1 , POINT , NONE , TAB ); or SELECT CREATE VIRTUAL TABLE my_table USING VirtualText( '/home/sandro/myfile.txt' , 'CP1252' , 1 , 'POINT' , 'NONE' , 'TAB' ); Both notations will produce exactly the same identical result.

Best practices and known limitations

Be aware: opening big or huge text files could easily require massive RAM allocations.
Warning: attempting to access an external Text file by blindly specifying random choosen delimiters and separators may easily have catastrophic effects.
Always carefully check the actual payload of the Text file before attempting to access it.
Virtual Tables are not really genuine Database Tables, they are just pretending to be.
Virtual Tables have very poor speed performances when compared to genuine Database Tables.
Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, if one or more of the JOINed Tables are of the Virtual type.
The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.

back to main page Z 4c001e89bc107ddb0dade1c1ce9fa2cb