Using vrt files to import odbc csv
Download
1 / 19

Using VRT Files to Import ODBC, CSV - PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on

Using VRT Files to Import ODBC, CSV. Problem : Many data sources come in comma delimited or in Excel or database formats Solution : VRT files describe the data source so they can be easily imported into Bentley Map Notes: Works best with point features

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Using VRT Files to Import ODBC, CSV' - francis-burnett


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Using vrt files to import odbc csv
Using VRT Files to Import ODBC, CSV

  • Problem: Many data sources come in comma delimited or in Excel or database formats

  • Solution: VRT files describe the data source so they can be easily imported into Bentley Map

  • Notes:

    • Works best with point features

    • Capitalization is important in the various parameters


What is vrt virtual datasource
What is VRT (Virtual Datasource)

  • An XML format file that describes the format of the source data

    • Data source

    • Feature name

    • Spatial Reference System

    • Field encoding

Sample VRT

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>SurveyMon.csv</SrcDataSource>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>


Common parts of vrt file
Common Parts of VRT file

Name of feature

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>SurveyMon.csv</SrcDataSource>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>


Common parts of vrt file1
Common Parts of VRT file

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>SurveyMon.csv</SrcDataSource>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>

Source for feature


Common parts of vrt file2
Common Parts of VRT file

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>SurveyMon.csv</SrcDataSource>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>

Feature Type


Common parts of vrt file3
Common Parts of VRT file

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>SurveyMon.csv</SrcDataSource>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>

Spatial Reference


Common parts of vrt file4
Common Parts of VRT file

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>SurveyMon.csv</SrcDataSource>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>

Encoding (Column names containing XY(Z) values)


Csv example
CSV Example

Source Data: SurveyMon.csv

Easting,Northing,ID,Elevation

602639.5381,4049623.398,1,282.9576447

669949.1987,4061243.25,2,282.3723275

617433.4852,4004901.709,3,265.455676

640922.0652,4096213.903,4,228.7933358

680351.1314,4008944.322,5,211.2217422

689200.8667,4010622.815,6,278.4353313

600382.0976,4086158.497,7,203.1683236

630393.1237,4095834.27,8,262.3779305

644160.8595,4016882.563,9,261.6596222

660532.9288,4092362.074,10,243.4360725

VRT File

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>SurveyMon.csv</SrcDataSource>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>


The map interoperability tool
The Map Interoperability Tool

  • File -> Map Interoperability

  • Import -> New Import

  • Add file…

  • Set All File Types

  • Show Properties -> Ungrouped


Import the data
Import the Data

  • All headings from CSV are present, including system generated FID

  • Set any WHERE clauses or spatial constraints

  • Set any required symbology

  • Import the data


Review the imported features
Review the Imported Features

  • Feature named correctly

    • <OGRVRTLayer name="SurveyMon">

  • Located at correct location

    • <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

  • All other properties imported


A note about coordinate systems
A Note About Coordinate Systems

  • <LayerSRS></LayerSRS> specifies the geographic coordinate system (GCS) of the input data

  • Examples are:

    • <LayerSRS>WGS84</LayerSRS>

    • <LayerSRS>GEOGCS [ "GDA94", DATUM ["Geocentric Datum of Australia 1994 (EPSG ID 6283)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree",0.0174532925199433]]</LayerSRS>


A note about coordinate systems1
A Note About Coordinate Systems

  • Because the GCS can be difficult to define, a simple workaround is to omit the GCS definition and import to a DGN to which the GCS of the data has been assigned.

  • Reference to other DGN files or reassign GCS

  • Sample data is MTM27-10

  • Import to DGN file with GCS defined as:

  • Reassign GCS as required for transformation


Database and spreadsheet sources
Database and Spreadsheet Sources

  • Require an ODBC connection to be setup which is specified in the data source parameter

    • SrcDataSource>ODBC:SurveyMonXLS</SrcDataSource>

  • Because there can be multiple tables or sheets in each source, an additional parameter is required

    • <SrcLayer>MonumentData</SrcLayer>

  • This defines the table or range name in the source

  • All other parameters remain the same


Odbc example excel spreadsheet
ODBC Example – Excel Spreadsheet

  • Setup ODBC link to XLS file

  • File must be in Excel 97 – 2003 format

  • Data is as shown

  • Range Name (MonumentData) defines the extent of the cells to import including the column headers


Odbc example excel spreadsheet1
ODBC Example – Excel Spreadsheet

VRT File

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>ODBC:SurveyMonXLS</SrcDataSource>

<SrcLayer>MonumentData</SrcLayer>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>


Odbc example access database
ODBC Example – Access Database

  • Setup ODBC link to MDB or ACCDB file

  • Data table is as shown

  • Source layer is the name of the table


Odbc example access database1
ODBC Example – Access Database

VRT File

<OGRVRTDataSource>

    <OGRVRTLayer name="SurveyMon">

        <SrcDataSource>ODBC:SurveyMonMDB</SrcDataSource>

<SrcLayer>SurveyMon</SrcLayer>

        <GeometryType>wkbPoint</GeometryType>

        <LayerSRS></LayerSRS>

        <GeometryField encoding="PointFromColumns" x="Easting" y="Northing"/>

    </OGRVRTLayer>

</OGRVRTDataSource>


Linear and polygon sources
Linear and Polygon Sources

  • Require a custom format for the data

  • Geometry type is

    • <GeometryType>wkbLineString</GeometryType>

    • <GeometryType>wkbPolygon</GeometryType>

  • For more information and examples:

http://communities.bentley.com/products/geospatial/desktop/f/5924/t/65028.aspx


ad