using vrt files to import odbc csv
Download
Skip this Video
Download Presentation
Using VRT Files to Import ODBC, CSV

Loading in 2 Seconds...

play fullscreen
1 / 19

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


  • 85 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