using vrt files to import odbc csv n.
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


Download Now 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