20.
Virtual Spatial Data (ODBC/OVF)
Document Actions
This is an OGR extension to MapServer. It allows you to connect to databases that do not explicitly hold spatial data, as well as flat text files. Your data must have an X and a Y column, and the data may be accessed through an ODBC connection or a direct pointer to a text file.
Types of Databases
The VirtualSpatialData OGR extension has been tested with the following databases and should, in theory, support all ODBC data sources.
- Oracle
- MySQL
- SQL Server
- Access
- PostgreSQL
Other References
The old MapServer Wiki had a good page on this.
Types of Flat Files
Comma, tab or custom delimited text/flat files work with VirtualSpatialData.
Steps for Display
1. Create the Datasource Name (DSN)
- Specific notes about creating a DSN on Windows and Linux can be found by searching the MapServer reference documents site: http://mapserver.gis.umn.edu/doc
- On some Windows systems you must create a SYSTEM DSN.
2. Test your Connection
Test your connection with ogrinfo. The syntax for this command is:
> ogrinfo ODBC:user/pass@DSN table
Windows users may not be required to specify a user/password, so the syntax would be:
> ogrinfo ODBC:@DSN table
Example: Accessing a comma separated text file through ODBC using ogrinfo
The following is a snippet of the flat text file coal_dep.txt containing lat/long points:
unknown,na,id,id2,mark,coalkey,coalkey2,long,lat 0.000,0.000,1,1,7,87,87,76.90238,51.07161 0.000,0.000,2,2,7,110,110,78.53851,50.69403 0.000,0.000,3,3,3,112,112,83.22586,71.24420 0.000,0.000,4,4,6,114,114,80.79896,73.41175
If the DSN name is Data_txt, the ogrinfo command to see a list of applicable files in the directory is:
> ogrinfo ODBC:jeff/test@Data_txt INFO: Open of `ODBC:jeff/test@Data_txt' using driver `ODBC' successful. 1: coal_dep.csv 2: coal_dep.txt 3: coal_dep_nf.txt 4: coal_dep_trim.txt 5: Copy of coal_dep.txt 6: deposit.csv 7: maruia.asc 8: oahuGISbathy.csv 9: oahuGISbathy.txt 10: on_pts.txt 11: on_pts_utm.txt 12: test.txt 13: utm_test.txt
Username and password may be optional, so the following may also be valid:
> ogrinfo ODBC:@Data_txt
Therefore, the command to see more information about one of the specific layers is:
> ogrinfo ODBC:@Data_txt coal_dep.txt INFO: Open of `ODBC:@Data_txt' using driver `ODBC' successful. Layer name: coal_dep.txt Geometry: Unknown (any) Feature Count: 266 Layer SRS WKT: (unknown) UNKNOWN: String (255.0) NA: String (255.0) ID: String (255.0) ID2: String (255.0) MARK: String (255.0) COALKEY: String (255.0) COALKEY2: String (255.0) LONG: String (255.0) LAT: String (255.0) OGRFeature(coal_dep.txt):0 UNKNOWN (String) = 0.000 ....
3. Create a Virtual Data File
This is a file with an ovf extension and looks like the following:
<OGRVRTDataSource>
<OGRVRTLayer name="mylayer">
<SrcDataSource>ODBC:user/pass@DSN</SrcDataSource>
<SrcLayer>tablename</SrcLayer>
<GeometryType>wkbPoint</GeometryType>
<LayerSRS>WGS84</LayerSRS>
<GeometryField encoding="PointFromColumns" x="x" y="y"/>
</OGRVRTLayer>
</OGRVRTDataSource>
More information on ovf files can be found at: http://www.gdal.org/ogr/drv_vrt.html
Example ovf file for coal_dep.txt:
<OGRVRTDataSource>
<OGRVRTLayer name="coal-test">
<SrcDataSource>ODBC:Data_txt</SrcDataSource>
<SrcLayer>coal_dep.txt</SrcLayer>
<GeometryField encoding="PointFromColumns" x="Long" y="Lat"/>
<GeometryType>wkbPoint</GeometryType>
</OGRVRTLayer>
</OGRVRTDataSource>
4. Test Virtual Data File with ogrinfo
Use ogrinfo to test your new ovf file, such as:
> ogrinfo coal.ovf coal-test ERROR 4: Update access not supported for VRT datasources. Had to open data source read-only. INFO: Open of `myfile.ovf' using driver `VRT' successful. Layer name: coal_dep.txt Geometry: Unknown (any) Feature Count: 266 Layer SRS WKT: (unknown) UNKNOWN: String (255.0) NA: String (255.0) ID: String (255.0) ID2: String (255.0) MARK: String (255.0) ...
5. Mapfile Layer
Using an ovf file your layer may look like:
LAYER
CONNECTION "coal.ovf"
CONNECTIONTYPE OGR
DATA "coal-test"
METADATA
"wms_srs" "4326"
"wms_title" "coal-test"
END
NAME "coal-test"
SIZEUNITS PIXELS
STATUS ON
TOLERANCE 0
TOLERANCEUNITS PIXELS
TYPE POINT
UNITS METERS
CLASS
STYLE
COLOR 255 0 0
MAXSIZE 100
MINSIZE 1
SIZE 6
SYMBOL "star"
END
END
END
Or you may specify the ovf contents inline such as:
LAYER
CONNECTION "<OGRVRTDataSource>
<OGRVRTLayer name='coal-test'>
<SrcDataSource>ODBC:@Data_txt</SrcDataSource>
<SrcLayer>coal_dep.txt</SrcLayer>
<GeometryField encoding='PointFromColumns' x='Long' y='Lat'/>
<GeometryType>wkbPoint</GeometryType>
</OGRVRTLayer>
</OGRVRTDataSource>"
CONNECTIONTYPE OGR
DATA "coal-test"
METADATA
"wms_srs" "4326"
"wms_title" "coal-test"
END
NAME "coal-test"
SIZEUNITS PIXELS
STATUS ON
TOLERANCE 0
TOLERANCEUNITS PIXELS
TYPE POINT
UNITS METERS
CLASS
STYLE
COLOR 255 0 0
MAXSIZE 100
MINSIZE 1
SIZE 6
SYMBOL "star"
END
END
END
6. Test your Mapfile
The first thing you should try is to use the shp2img utility:
shp2img -m mymapfile.map -o test.png
Once you sucessfully created a map image, then try your application. Note Windows users may come across a problem where shp2img works but their application throws an error similar to this:
Warning: [MapServer Error]: msOGRFileOpen(): Open failed for OGR connection `coal.ovf'. Unable to initialize ODBC connection to DSN for jeff/test@Data_txt, [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified in D:\ms4w\Apache\htdocs\quickmap.php on line 40
If that happens you should make sure you have created a System DSN.
Access to flat files through OGR VRT
Access to flat files through OGR VRT
While there's plenty of documentation regarding access to tabular data through ODBC, I found very little about setting up OGR VRT (OVF) files to access flat files (text or dbf). Here is an example using GDAL/OGR 1.3.1 to access non-spatial, plain dbf files through OVF; the procedure should be very similar for text files like CSV.
First, if the dbf is part of a shape file, rename it to a different base file name that doesn't match that of any other shape file in the folder. In this example, there are two dbf files, hotspot48h.dbf and hotspot7d.dbf, in the file path /home/dbf_folder. Both files have LONGITUDE and LATITUDE fields that can be used to create point shape files. The OVF file ("points.ovf") below creates a virtual data source with two layers (rrs_fire_ca_48h_pt and rrs_fire_ca_7d_pt) with point geometries, corresponding to the two dbf files.
Load layers from points.ovf into a map file as described above in this document. To use ogr2ogr to create point shape files, use these commands: cd /home/dbf_folder ogr2ogr -f "ESRI Shapefile" . points.ovf rrs_fire_ca_48h_pt ogr2ogr -f "ESRI Shapefile" . points.ovf rrs_fire_ca_7d_pt
Another how-to convert lon/lat to Shapefile format
The only way I was able to get ogr2ogr to respond was by --
- Naming my input file foo.csv (extension .csv)
- Storing the .csv as well as the corresponding .ovf in a path with no spaces
- Quoting the source definition path in the command C:\>ogr2ogr -f "ESRI Shapefile" foo "C:\foo.ovf"
- Not quoting the source path in the .ovf file (see SrcDataSource below)
C:\foo.csv spottings_geocoded_good wkbPoint
Not very consistent, but it actually worked. So that was encouraging. Next task was to add the srs_def. Since I was running this command from within a Perl script via a system call, my srs_def was stored as a variable. If I stored it as a Heredoc variable to make it legible, ogr2ogr would fail. In other words, the following caused it to complain about bad srs_def.
$srs = <GCS_North_American_1983,
...
END
So, I changed the above to a single line like so
$srs = "PROJCS['USA_Contiguous_Lambert_Co..."
and then I got the following error
ERROR 6: Failed to initialize PROJ.4 with `+ellps=GRS80 +units=m +no_defs '. projection not named Failed to create coordinate transformation between the following coordinate systems. This may be because they are not transformable, or because projection services (PROJ.4 DLL/.so) could not be loaded.
So, to summarize, mixed luck, but hopefully this will help someone somewhere especially if no srs_def is involved.
Feature_info & Oracle performance
Sometimes ODBC performance to Oracle databases is very bad (up to a minute for each request). OGR is known to scan all tables in your scheme. Tell OGR to scan only the tables you need by specifying the tablename behind the odbc connection seperated by ','.
ODBC:me/secret@myDB,myTable
Oracle Instant Client and ODBC Quick Steps
I had problems getting Virtual Spatial Data to work with Oracle (not Oracle Spatial).
Here's what I got to work:
1) Download and Install Oracle Instant Client (from OTN) 2) Install unixodbc (I used apt-get to do this on debian) 3) Download Oracle Instant Client ODBC
##I had to make a link from libsqora.so.10.1 to libsqora.so.10.2 in the odbc directory I had downloaded from OTN... This may not be necessary depending on which version of the 10g Instant Client you have downloaded##
4) Locate (or find) odbc.ini. In my debian unixodbc pacakge it is in /etc 5) chmod +x odbc_install.sh (give your install shell script exectuable rights) 6) run shell script and pass the argument (the directory where your obdc.ini file is located)
./odbc_install.sh /etc
7) edit your odbc.ini file. this is what I used:
[ORACLE] DSN = oracle Driver = OracleODBC-10g <-- This was the name of the driver from the obdcinst.ini file in the same directory as my odbc.ini file ServerName = server.ilpcs.com <-- This was the name of the oracle server I was using, I'm using the full name so that I'm not dependent on any TNSNAMES.ORA file UserID = scott Password = tiger
8) Use isql -v oracle to test, sqlplus to test, or ogrinfo ODBC:@DSN table to test