{"id":307,"date":"2009-01-07T17:01:05","date_gmt":"2009-01-07T16:01:05","guid":{"rendered":"http:\/\/sickel.net\/blogg\/?p=307"},"modified":"2009-01-13T13:50:30","modified_gmt":"2009-01-13T12:50:30","slug":"postgresql-as-a-mapserver-virtual-data-source","status":"publish","type":"post","link":"https:\/\/sickel.net\/blogg\/?p=307","title":{"rendered":"Postgresql as a mapserver virtual data source"},"content":{"rendered":"

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

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.<\/p>\n

ovf-file: (test_pg.ovf)<\/p>\n

\r\n<OGRVRTDataSource>\r\n        <OGRVRTLayer name=\"test\">\r\n                <SrcDataSource> PG:dbname=mapdata user=www port=5434<\/SrcDataSource>\r\n                <GeometryField encoding=\"PointFromColumns\" x=\"x\" y=\"y\"\/>\r\n                <GeometryType>wkbPoint<\/GeometryType>\r\n                <SrcSQL>Select x,y,b,a,b+a as total from test<\/SrcSQL>\r\n        <\/OGRVRTLayer>\r\n<\/OGRVRTDataSource>\r\n<\/pre>\n
To test that this is working, use
\nogrinfo -ro test_pg.ovf
\npreferably do a su to the user running the web server. eg (on debian where apache runs as the user www-data)
\nsu www-data -c ‘ogrinfo -ro test_pg.ovf’
\nIf this is working, try to add it into a .map file, eg as<\/p>\n
\n  LAYER
\n        PROJECTION
\n           “proj=latlon”
\n        END
\n        CONNECTION   \/home\/www\/htdocs\/tutorial\/data\/test_pg.ovf
\n        CONNECTIONTYPE OGR
\n        NAME         test-pie
\n        DATA            test
\n        STATUS       OFF
\n        TYPE         CHART
\n        PROCESSING “CHART_SIZE_RANGE=total 5 100 10 200”
\n        CLASS
\n                NAME “Value”
\n                STYLE
\n                        OUTLINECOLOR 0 0 0
\n                        COLOR 255 186 145
\n                        SIZE [a]
\n                END
\n        END<\/p>\n
        CLASS
\n                NAME “Value2”
\n                STYLE
\n                        OUTLINECOLOR 0 0 0
\n                        COLOR 255 217 191
\n                        SIZE [b]
\n                END
\n        END
\nEND\n<\/p><\/blockquote>\n
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.<\/p>\n
the map file can be tested using shp2img, e.g.
\nshp2img test.map -o test.png
\nif it doesnt work, set -all_debug to a sufficient high level, e.g.
\nshp2img -all_debug 20 test.map -o test.png<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[17],"class_list":["post-307","post","type-post","status-publish","format-standard","hentry","category-div","tag-englishposts"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pnVtD-4X","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/307","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=307"}],"version-history":[{"count":4,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/307\/revisions"}],"predecessor-version":[{"id":337,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/307\/revisions\/337"}],"wp:attachment":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}