ResponseMX Blog

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