Postgresql as a mapserver virtual data source

I wanted to use postgresql as a server for Virtual Spatial Data in mapserver. After some googling around, I ended up with the following:

The postgresql server is set up to trust local connections, or I would have had to use the parameter password=psswrd after PG:. There is a database mapdata with a table test having the fields id,x,y,a,b. x and y being the geographical coordinates, a and b just two floating point numbers to play around with.

ovf-file: (test_pg.ovf)

<OGRVRTDataSource>
        <OGRVRTLayer name="test">
                <SrcDataSource> PG:dbname=mapdata user=www port=5434</SrcDataSource>
                <GeometryField encoding="PointFromColumns" x="x" y="y"/>
                <GeometryType>wkbPoint</GeometryType>
                <SrcSQL>Select x,y,b,a,b+a as total from test</SrcSQL>
        </OGRVRTLayer>
</OGRVRTDataSource>

To test that this is working, use
ogrinfo -ro test_pg.ovf
preferably do a su to the user running the web server. eg (on debian where apache runs as the user www-data)
su www-data -c ‘ogrinfo -ro test_pg.ovf’
If this is working, try to add it into a .map file, eg as

LAYER
PROJECTION
“proj=latlon”
END
CONNECTION /home/www/htdocs/tutorial/data/test_pg.ovf
CONNECTIONTYPE OGR
NAME test-pie
DATA test
STATUS OFF
TYPE CHART
PROCESSING “CHART_SIZE_RANGE=total 5 100 10 200”
CLASS
NAME “Value”
STYLE
OUTLINECOLOR 0 0 0
COLOR 255 186 145
SIZE [a]
END
END

CLASS
NAME “Value2”
STYLE
OUTLINECOLOR 0 0 0
COLOR 255 217 191
SIZE [b]
END
END
END

If you are using mapserver version < 5.2, use “CHART_SIZE=20” rather than “CHART_SIZE_RANGE=total 5 100 10 200” the latter gives a dynamic pie size set with the ‘total’ calculated field.

the map file can be tested using shp2img, e.g.
shp2img test.map -o test.png
if it doesnt work, set -all_debug to a sufficient high level, e.g.
shp2img -all_debug 20 test.map -o test.png

Hope this may be helping another postgresql user, remember, this is how to use a normal table as a virtual spatial table, if you are using postgis, things are different.