Changes between Initial Version and Version 1 of VirtualSpatialData

Timestamp:

Jan 27, 2009, 3:51:44 PM (15 years ago)

Author:

jmckenna

Comment:

--

VirtualSpatialData

@@ +1 @@
+= Virtual Spatial Data =
+ 
+This page is intended to describe how to set up spatial data from non-spatially aware datasources such as RDMSs without spatial engines. The basic assumptions are that the datasource is available from an ODBC connection, that the datasource contains two columns that can be mapped to a point (x and y), and that you are not afraid to figure things out.
+
+Note that this currently only works for POINT data.
+
+The basic components that you will need are! MapServer 4.0 or 4.1 compiled with a recent (as of 2004-01-06) CVS version of GDAL/OGR, a datasource that you have an ODBC driver for, and an ODBC driver manager.
+
+The quick steps are (more detailed steps including pitfalls are below):
+
+   1. create a DSN (ODBC datasource) for your data source and test it. How you do this is system and driver dependent, you have to either know how or figure it out yourself.
+   2. test using ogrinfo
+   3. create a virtual data file using the instructions provided at http://www.remotesensing.org/gdal/ogr/drv_vrt.html
+   4. test using ogrinfo
+   5. create a layer of TYPE OGR with a CONNECTION that points to the virtual data file and DATA that is one of the OGRVRTLayer layer names, with appropriate classes for drawing
+   6. test using mapserver/mapscript 
+
+== Detailed steps ==
+
+=== Create a DSN ===
+
+Creating a DSN is system dependent. Some notes follow:
+
+==== Windows ====
+
+Windows is fairly easy to setup and test DSNs since the Data Source Manager does most of the work for you. However, one gotcha is that OGR seems to need a System DSN. User DSNs do not seem to work. If anyone can figure out why (permissions?) please update this.
+
+(ChipHankley - System DSNs are DEFINITELY the way to go on Windows boxes using IIS for web-based apps. This is because all web transactions use a "web-user" that is typically different from the person who is logged in. System DSNs are tied to the system, while User DSNs are tied to a user. For example if person A sets up a User DSN, person B will not have access to it when they're logged in. Consequently, the default web-user will never have access to a User DSN, unless the default web-user - set up in IIS - is the same as the person setting up the User DSN. Bottom line, avoid all of this by using System DSNs.)
+
+(JenuAbraham - Setting up the ODBC system DSN for Windows 2003 with MS SQL Server.
+
+ Administrative tools -> DataSources(ODBC)-> System DSN -> Add -> SQL Server
+ -> (Give any datasource name eg:"localserver") -> Select the SQL Server eg:(local)
+ -> CHECK RadioButton (With SQL Server Authentication) -> CHECK(Connect to SQL Server to obtain default settings) 
+ -> fill out the username and password for SQL Server -> Select the default database where your table exists
+ -> Finish -> Test Data Source.
+
+NOTE: ogrinfo might work with both windowsNT authentication and with SQL server authentication without obtaining the default settings from SQL Server. But mapserver.exe will only work with SQL Server authentication along with the default setting from SQL Server.)
+
+==== Linux ====
+
+When working on unix platforms, you need to supply your own ODBC driver manager as well as an ODBC driver for the RDBMS being accessed.
+
+I found two driver managers: http://www.unixodbc.org and http://www.iodbc.org. GDAL/OGR compiles much more easily against unixodbc, so it is probably a better bet. The driver manager can be built with a GUI tool to allow creation of DSNs, or you can add them through configuration files identical in structure to those used by Windows (odbc.ini and odbcinst.ini).
+
+Once the driver manager is installed, you need to reconfigure and make a recent cvs version of GDAL/OGR adding --with-odbc to the configure command. When built, ogrinfo --formats will tell you if odbc is an available driver.
+
+ODBC drivers can often be obtained from the RDBMS supplier. MyODBC? is freely available from the MySQL site and worked well in my testing. There are commercially supported drivers for Oracle available from www.easysoft.com (there is supposed to be a free trial) but there is also an open source odbc driver project for oracle that seems to be based on the easysoft one: http://fndapl.fnal.gov/~dbox/oracle/odbc/
+
+Once your odbc driver is built, you need the DSN to reference the shared library:
+
+e.g.
+{{{
+[unixodbctest]
+Driver = /usr/local/lib/libmyodbc3.so
+Port = 3306
+Database = test
+UID = username
+PWD = password
+}}}
+One tip: compiling an odbc driver usually requires you to have the client libraries for the RDBMS available on your system.
+
+If you have experience with alternatives to these or drivers for other common databases, pleas add them.
+
+Test using ogrinfo
+
+ogrinfo is a binary utility that can be built from the gdal source tree. Once you figure out how to build it, you can use it to test various aspects of an OGR datasource. Details on using ogrinfo can be found at:
+
+http://www.remotesensing.org/gdal/ogr/ogr_utilities.html
+
+The options you will likely want to use are:
+
+ $ ogrinfo ODBC:user/pass@DSN table
+
+This tells ogrinfo to use an ODBC datasource named DSN with user name "user" and password "pass", and display all the features in the table called "table". Some notes on this. The username and password should be optional if the DSN is configured to include a user name and password, in which case you would write 'ogrinfo ODBC:DSN table' but I never got this to work correctly. The password can be empty, in which case you would use 'ogrinfo ODBC:user/@DSN table'.
+
+You can also use SQL statements with ogrinfo with the -sql option. For instance, "ogrinfo ODBC:user/pass@DSN -sql 'select table.* from table where table.myval > 10'"
+
+The latest version of the OGR ODBC driver as of 2004-01-05 passes the SQL directly to the ODBC driver, which is a slight change. Before this version, OGR interpreted the SQL itself and was somewhat limited in its SQL abilities (see http://www.remotesensing.org/gdal/ogr/ogr_sql.html).
+
+A note about ogrinfo and debugging. To get more information out of ogrinfo, you can set an environment variable called CPL_DEBUG to ON (set CPL_DEBUG=ON in windows)
+
+If you have trouble connecting to the ODBC datasource with OGR, you might want to use a direct ODBC client to verify that the ODBC datasource is working properly. For windows the WinSQL? product (http://www.indus-soft.com/SynametricsWebApp/WinSQL.jsp) is good, and the Lite version is free. On Unix the isql commandline client that comes with unixodbc should be OK.
+
+Create a Virtual Data File
+
+A virtual data file can be used by OGR to create geometries for non-spatial data sources. For the purpose of this howto, we are working with ODBC data sources but it should work for any OGR data source (but it is only useful if it is a non-spatial source with point data).
+
+The format of the virtual data file is described at
+
+ http://www.remotesensing.org/gdal/ogr/drv_vrt.html
+
+A minimal virtual file could be:
+
+<OGRVRTDataSource>
+    <OGRVRTLayer name="mylayer">
+        <SrcDataSource>ODBC:user/pass@DSN</SrcDataSource> 
+        <SrcLayer>table</SrcLayer> 
+        <GeometryType>wkbPoint</GeometryType> 
+        <LayerSRS>WGS84</LayerSRS>
+        <GeometryField encoding="PointFromColumns" x="x" y="y"/> 
+    </OGRVRTLayer>
+</OGRVRTDataSource>
+
+This example assumes that there are columns in the database table called 'x' and 'y' and that these contain spatial locations in latlong projection WGS84 (epsg:4326)
+
+The virtual file can be called anything you want and be placed anywhere. I'll assume its called virtual.ovf (OGR Virtual File = .ovf).
+
+The <SrcLayer?> parameter is a table name. If you wish to join tables or select only certain columns, you can use an SQL statement by omitting <SrcLayer?> and including <SrcSQL?>select table.name, table.x, table.y from table</SrcSQL?> in its place. Note that in the GeometryField?, you may need to refer to the attributes x and y as table.x and table.y in this case.
+
+Testing using ogrinfo
+
+To test an ovf file using ogrinfo, simply replace the driver with the path to the file, for instance:
+
+ $ ogrinfo virtual.ovf
+
+ ERROR 4: Update access not supported for VRT datasources.
+ Had to open data source read-only.
+ INFO: Open of `virtual.ovf'
+ using driver `VRT' successful.
+ 1: virtual (Point) 
+
+( The last line indicates the table inside the database you are accessing )
+
+To list the contents of a layer in the file, use:
+
+ $ ogrinfo virtual.ovf mylayer.
+
+ ERROR 4: Update access not supported for VRT datasources.
+ Had to open data source read-only.
+ INFO: Open of `virtual.ovf'
+ using driver `VRT' successful.
+
+ Layer name: mylayer
+ Geometry: Point
+ Feature Count: 15
+ Extent: (-74.014800, 40.445500) - (-74.014800, 40.445500)
+ Layer SRS WKT:
+ GEOGCS["WGS 84",
+    DATUM["WGS_1984",
+        SPHEROID["WGS 84",6378137,298.257223563,
+            AUTHORITY["EPSG","7030"]],
+        TOWGS84[0,0,0,0,0,0,0],
+        AUTHORITY["EPSG","6326"]],
+    PRIMEM["Greenwich",0,
+        AUTHORITY["EPSG","8901"]],
+    UNIT["degree",0.0174532925199433,
+        AUTHORITY["EPSG","9108"]],
+    AXIS["Lat",NORTH],
+    AXIS["Long",EAST],
+    AUTHORITY["EPSG","4326"]]
+ virtual.name: String (50.0)
+ virtual.x: Real (0.0)
+ virtual.y: Real (0.0)
+ OGRFeature(mylayer):0
+  virtual.name (String) =  MyPoint1
+  virtual.x(Real) = -74.01479999999999
+  virtual.y(Real) = 40.4455
+  POINT (-74.01480000 40.44550000 0.000)
+
+The difference from using ogrinfo directly on the ODBC datasource is that there is now a POINT geometry associated with the feature.
+
+Windows 2003-MS SQL Server:
+If you see "ERROR 1: SetAttributeFilter?() not supported on ExecuteSQL?() results" within the above output while running the above command, then its possible that your mapserver wont run properly too. Check out OgrinfoError1
+
+Creating a MapServer layer
+
+The final step is to create a layer object in mapserver and have it use this virtual file as its datasource. A (very minimal) layer follows:
+
+  LAYER
+    NAME "MyVirtualLayer"
+    TYPE POINT
+    CONNECTIONTYPE OGR
+    CONNECTION "virtual.ovf" # the name and path to the virtual.ovf file, relative to shapepath may work?
+    DATA "mylayer" # the name of the OGRVrtLayer
+    STATUS ON
+    LABELITEM table.name #may need to use table.name or just name depending on your OVF file.
+    PROJECTION
+      "init=epsg:4326"
+    END
+    CLASS
+      SYMBOL 1
+      SIZE 6
+      COLOR 255 0 0
+      LABEL
+        SIZE MEDIUM
+        TYPE BITMAP
+        COLOR 0 0 0
+      END
+    END
+END
+
+Testing with MapServer
+
+This is up to you ... hopefully you can do this if you've got this far ;)
+
+One tip:
+
+Some ODBC drivers may need environment variables to be set to function correctly. A symptom of related problems is that ogrinfo can successfully connect to your database from a command shell, but there is an OGR error connecting to the layer's .ovf file through mapserver. With Apache, you can add the necessary environment variables to your httpd.conf file:
+
+   1. Enable mod_env
+   2. add lines like: setenv ORACLE_HOME /path/
+   3. restart apache 
+
+Second tip:
+
+If can see other layers but not your virtual spatial layer.
+
+   1. Check if you have a symbol defination in your map file.
+   2. If NOT create one like below. (You dont have to use this, just keep it as a dummy defination). 
+
+   SYMBOL
+        NAME cir
+        TYPE ellipse
+        FILLED true
+        POINTS
+                1 1
+        END
+   END
+
+Third tip:
+
+Make sure you have the right signs for the latitudes and longitudes inside the table. x=longitude, y=latitude
+
+   1. Western Hemisphere eg: USA - longitude (-)
+   2. Eastern Hemisphere eg: India - longitude (+)
+   3. Northern Hemisphere eg: USA - latitude (+)
+   4. Southern Hemisphere eg: Australia - latitude (-) 
+
+eg: x= -87.5456 y = 35.4565 (for some place in USA)
+
+Dynamic Control of Virtual Sources
+
+While the virtual configuration XML is normally maintained as a separate file referenced in the CONNECTION string of the layer, OGR also makes it possible to include the file's contents instead, inline in the mapfile.
+
+This can be combined with the mapserver facility for URL subsitution (text in the mapfile surrounded by % delimiters is replaced with matching text in the GET or POST) to create a configuration where the connection details are passed through the incoming URL. An example shows how a time parameter in the url can be used to create an SQL argument at the time of the request:
+
+if we have a wms url: http://localhost/cgi-bin/mapserv?map=mymap.map&version=1.1.1&request=getmap&time=2003-12-24%2009:00:00
+
+and this string is the CONNECTION parameter in the mapfile:
+
+'<OGRVRTDataSource>
+    <OGRVRTLayer name="mylayer">
+        <SrcDataSource>ODBC:user/pass@DSN</SrcDataSource> 
+        <SrcSQL>SELECT * FROM table WHERE time LIKE SUBSTR("%time%", 0, 4)</SrcSQL> 
+        <GeometryType>wkbPoint</GeometryType> 
+        <LayerSRS>WGS84</LayerSRS>
+        <GeometryField encoding="PointFromColumns" x="x" y="y"/> 
+    </OGRVRTLayer>
+</OGRVRTDataSource>'
+
+the result is that the SQL returns all records from table with a date in 2003.
+
+TIP: experiment with single and double quotes in situations where mapserver or your ODBC source are having trouble reading the complete string.
+
+Using some creative SQL it's possible to do quite a bit!