Here’s a tip for mapserver users which allows you read and display vector data stored within a regular database in ResponseMX.
You will need ODBC access to your database. I’ve used mysql on windows for this example but other databases should work just the same.
The tip takes advantage of OGR ability to understand geometry in Well Known Text (WKT) format, via its Virtual Format mechanism.
So to be of use, you’ll need to be able to create WKT feature descriptions and store them in a text column in your non-spatial database.
To do this you need to do the following:
1. Create a database containing your vector data
2. Create a system DSN to give ODBC access to a data source
3. Create an .ovf file
4. Test the connection through ogrinfo to prove access to the data
5. Modify map file
6. Modify the ResponseMX geoset
Here’s the detailed steps:
1. Create a database containing your vector data
Create a database table
DROP TABLE IF EXISTS `test`.`polygon_test`;
CREATE TABLE `test`.`polygon_test` (
`Record_Id` int(10) unsigned NOT NULL auto_increment,
`Polygon` text NOT NULL,
`Info` varchar(45) NOT NULL,
PRIMARY KEY (`Record_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now add some data to it. The Polygon column is going to hold the geometry which can be a very long string, so it needs to be a text type column.
insert into polygon_test values (DEFAULT, 'POLYGON (( 350600 420200, 350600 440200, 330100 440200, 330100 420200, 350600 420200 ))', 'A square');
insert into polygon_test values (DEFAULT, 'POLYGON (( 436909.520549625 346979.908498395, 428048.086151056 341231.951050675, 431401.061328893 329975.534382221, 443495.721791806 330694.029063187, 446249.951402172 340752.954596698, 446249.951402172 340752.954596698, 436909.520549625 346979.908498395 ))', 'A Pentagon');
2 Create a system DSN to give ODBC access to the data source
On Windows, Go to Control panel -> Administrative tools -> Data Sources (ODBC) then select the System DSN tab.
Click Add and select the correct driver for whatever database is being used, e.g. SQL Server, MySQL
From this point the set up will be specific to the database server, but typically you will have to configure the database server connection details, such as server name, database name, username and password, default database to connect to.
Test the connection once you have entered the necessary details.
Once you have a working ODBC connection to your database you’re ready for step 2.
3. Create an .ovf file
The ovf file provides mapserver with the connection details it needs to retrieve geometry and feature information from the database.
Example .ovf file:
<OGRVRTDataSource>
<OGRVRTLayer name=”boundaries”>
<SrcDataSource>ODBC:TestDbUser/secret@testdb</SrcDataSource>
<SrcLayer>geometryTable</SrcLayer>
<FID>Record_Id</FID>
<GeometryType>wkbPolygon</GeometryType>
<GeometryField encoding=”WKT” field=”POLYGON”/>
</OGRVRTLayer>
</OGRVRTDataSource>>
Things you will have to configure:
<OGRVRTLayer name=”boundaries”> – this needs to be the name of your data source, in this case ‘boundaries’
<SrcDataSource>ODBC:TestDbUser/secret@testdb</SrcDataSource>
This is the odbc connection string which is in the format
ODBC:username/password@database
or
ODBC:username/password@database,tableName
<SrcLayer>geometryTable</SrcLayer> – this is where you specify the table that holds your vector data.
<FID>Record_Id</FID> – this may or may not be required. It is used to specify the column that contains the unique key for the row (the Feature Identifier, hence FID). Mapserver will have a go at guessing but can be confused by some data structures, hence this tag.
For point data you would leave <GeometryType></GeometryType> set to wkbPoint and leave the encoding attribute of the <GeometryField/> tag set to “PointFromColumns”.
Then you will need to specify the columns where the x and y coordinates are drawn from in the database table as the x and y attributes of the <GeometryField/> tag.
However, in this example we’re storing polygons in our database, so you would set
<GeometryType></GeometryType> to wkbPolygon and set the GeometryField tag thus:
<GeometryField encoding=”WKT” field=”POLYGON”/>
This tells OGR to expect the geometry of our polygons to be stored in a column called POLYGON and encoded in WKT format.
You’ll need to store your .ovf file in the same folder as your mapserver’s .map file. To avoid confusion, give the file a name that matches the layer name you’re going to give the layer in your .MAP file. e.g. boundaries.ovf
Be aware that although the .ovf file format looks like xml, it isn’t actually fully-compliant xml, so don’t put an xml declaration at the top of the file and if you have problems when you test the connection, make sure you don’t have blank lines or hidden characters in the file.
4. Test the connection through ogrinfo to prove access to the data
Having configured your .ovf file, test it using the following ogrinfo command:
ogrinfo -al boundaries.ovf
ogrinfo will warn you that the layer is read only with the following message:
ERROR 4: Update access not supported for VRT datasources.
This is ok - unfortunately virtual format layers can’t be updated (yet). If your configuration is correct you will be rewarded with a list of the features in your database.
5. Modify map file
Now you’ll need to modify your mapserver’s .map file to include your new layer. Note that you’ll need to provide styling for the layer as there won’t be any styling information in your database.
LAYER
NAME "boundaries"
STATUS ON
CONNECTIONTYPE OGR
CONNECTION "boundaries.ovf"
DATA "polygon_test"
TYPE POLYGON
6. Modify the ResponseMX geoset
Finally add the layer to the Response MX geoset xml file that corresponds to your mapserver .map file. Depending on your requirements this may be as simple as adding
Finally restart ResponseMX to pick up your changes
With luck your new layer is displaying your polygons.
If not here are a few things to check:
If you are using x and y values to retrieve point data, there seems to be a limit to the numeric length that OGR can handle for the FID column approx 12 digits long.
Check that your data is really in Well Known Text format. When setting up this test I managed to miss a couple of spaces which meant two coordinates were stuck together. The tell tale for this is ogrinfo -al shows ‘POLYGON (string)’ before the feature data but doesn’t then repeat all the feature data without the ‘(string)’. In other words ogrinfo can see your data, but can’t turn it into WKT.
There are several ways in which you can get WKT format wrong - see this post for another example: http://www.mail-archive.com/mapserver-users@lists.umn.edu/msg03679.html
Be careful when using the tag. I had this in my first few attempts and it stopped mapserver in its tracks. You shouldn’t need to specify a SRS in the .ovf file if the data is in the same spatial referencing system as your other layers.
Further info
Reference on mapserver site:
http://mapserver.gis.umn.edu/docs/reference/vector_data/VirtualSpatialData
Actual ovf file reference documentation:
http://www.gdal.org/ogr/drv_vrt.html
