Connection MapFile layer to PostGIS (c# mapscript tutorial, part 7)

Posted by Paolo Corti on August 1, 2006

First I am going to show the difference between connecting the MapFile to a shapefile layer and to a PostGIS layer, then I will show how to adapt your MapFile to work with PostGIS.

Shapefile Connection

Here is a sample connection from MapFile to a shapefile called “compfun”.

First you need to declare the path to the shape data (SHAPEPATH).

Then for each shapefile layer you have to set the shapefile’s data source (DATA) that is the name of the shapefile.
Here the shapefile is named compfun.shp so DATA is set to “compfun”.

Note that NAME can be anything, it means how in your MapFile want to call the shapefile layer.

MAP
...
SHAPEPATH "C:\training\mapServerTutorial\data"
...
LAYER
  NAME "compfun"
  TYPE POLYGON
  STATUS ON
  DATA "compfun"
  CLASS
    STYLE
      COLOR 255 235 190
      OUTLINECOLOR 0 0 0
      SYMBOL 0
    END
  END
END
...

PostGIS Connection

All the connection properties can be defined in the LAYER section of MapFile.

Obviously there is no need to set a SHAPEPATH attribute, unless you want to serve some shapefile layer togheter with PostGIS layers.

Basically in the LAYER section you need to set 3 attributes.

CONNECTIONTYPE has to be set to “postgis”
CONNECTION has to be set to the connection string needed for PostGIS
DATA has to be set with a SQL select to the geometry column from the layer table

The connection string needs some attribute to be set:

HOST hostname of the PostgreSQL server
DBNAME name of the database where your PostGIS data are stored
USER name of the user
PASSWORD
PORT you can omit this attribute if you installed PostgreSQL at the default port (5432)

Also for PostGIS layers, as for shapefile layers, NAME can be anything. It is how you want to call the layer in the MapServer context.

LAYER
  CONNECTIONTYPE postgis
  CONNECTION "host=localhost dbname=TUTORIAL user=psqluser password=psqluser port=5432"
  DATA "the_geom FROM compfun"
  NAME "compfun"
  TYPE POLYGON
    STATUS ON
    CLASS
    STYLE
      COLOR 255 235 190
      OUTLINECOLOR 0 0 0
      SYMBOL 0
    END
  END
END

Modify the MapFile tutorial’s file

You can use the csharptutorial_postgis.map MapFile included with the tutorial data.

Or if you prefer you can copy the following text in a new text file named csharptutorial_postgis.map.

MAP
NAME "Zone Samples"
SHAPEPATH "C:\training\mapServerTutorial\data"
SIZE 400 400
STATUS ON
EXTENT 1143759 4417539 1146436 4420390
UNITS METERS 
FONTSET "fonts\fonts.list"  
WEB
  IMAGEPATH "C:\Inetpub\wwwroot\temp"
  IMAGEURL "C:\Inetpub\wwwroot\temp"
END
 
SYMBOL
  NAME "circle"
  TYPE ellipse
  FILLED true
  POINTS
    1 1
  END
END
 
LAYER
		CONNECTIONTYPE postgis
		CONNECTION "host=localhost dbname=TUTORIAL user=psqluser password=psqluser port=5432"
		DATA "the_geom FROM compfun"
    NAME "compfun"
    TYPE POLYGON
    STATUS ON
    CLASS
      STYLE
        COLOR 255 235 190
        OUTLINECOLOR 0 0 0
        SYMBOL 0
      END
    END
END
 
LAYER
    NAME "zone"
    CONNECTIONTYPE postgis
		CONNECTION "host=localhost dbname=TUTORIAL user=psqluser password=psqluser port=5432"
		DATA "the_geom FROM zone"
    TYPE POLYGON
    STATUS ON
    CLASSITEM "COD"
    LABELITEM "NUMLOTT"
    CLASS
				EXPRESSION ([COD]=103)
        STYLE
            COLOR 230 50 0
            OUTLINECOLOR 0 0 0
            SYMBOL 0
        END
    END
    CLASS
				EXPRESSION ([COD]=105 OR [COD]=106 OR [COD]=107 OR [COD]=108 OR [COD]=102)
        STYLE
            COLOR 255 235 230
            OUTLINECOLOR 0 0 0
            SYMBOL 0
        END
        LABEL
					COLOR 0 0 0
					FONT verdana
					TYPE TRUETYPE
					SIZE 7
					POSITION CC
				END
    END
    CLASS
				EXPRESSION ([COD]=109 OR [COD]=101)
        STYLE
            COLOR 255 196 171
            OUTLINECOLOR 0 0 0
            SYMBOL 0
        END
    END
    CLASS
        STYLE
            COLOR 225 225 225
            OUTLINECOLOR 0 0 0
            SYMBOL 0
        END
    END
END
 
LAYER
		CONNECTIONTYPE postgis
		CONNECTION "host=localhost dbname=TUTORIAL user=psqluser password=psqluser port=5432"
		DATA "the_geom FROM vestizioni"
    NAME "vestizioni"
    TYPE LINE
    STATUS ON
    CLASS
        STYLE
            COLOR 0 0 0
            SYMBOL 0
        END
    END
END
 
LAYER
		CONNECTIONTYPE postgis
		CONNECTION "host=localhost dbname=TUTORIAL user=psqluser password=psqluser port=5432"
		DATA "the_geom FROM poi"
    NAME "POI"
    TYPE POINT
    STATUS ON
    LABELITEM "POI_TIME"
    CLASS
				SIZE 10
        STYLE
            COLOR 255 0 0
            OUTLINECOLOR 0 0 0
            SYMBOL "circle"
				END
				TEXT ([POI_USER], [POI_TIME])
				LABEL
					COLOR 255 0 0
					FONT verdana
					TYPE TRUETYPE
					SIZE 7
					POSITION LC
					WRAP " "
				END
		END
END
 
END

In the next step you will adapt the c# to work indifferently with shapefile or PostGIS data.

Share and Enjoy:

Thanh Long says:

November 21, 2006 at 6:44 am

Great! this help me very much, thanks so much!

Bayu Kurniawan R says:

December 21, 2006 at 8:30 am

Thanks, its works

MeoMeo says:

December 25, 2006 at 4:15 am

Can u send me Example Mapserver(api) connect SDE database. And Add Point, Polyline, polygon to SDE thanks

Amgad A. Ali says:

January 27, 2007 at 3:29 pm

I need to edit SDE Line Layer, now i can connect to view SDE layers but I can’t EDIT with C# , Please help

January 27, 2007 at 3:32 pm

I need to edit SDE Line Layer, now i can connect to view SDE layers but I can’t EDIT with C# , Please help
amgad_gis@yahoo.com
amgad.gis@gmail.com

Paolo Corti says:

January 29, 2007 at 4:31 pm

Amgad,
you cannot edit sde data with c# mapscript. To do that you would need an Esri library appropriate for doing that, like ArcGis server (Server ArcObjects) or the sde API, but the last there is only for C++ and Java at the moment, I don’t know if they will release a version for .NET.

Dhruv Sazawal says:

March 4, 2007 at 12:37 am

Hi paolo,

I am trying to develop a Gis application using c# mapscipt and lot has been done . Now I am facing problem in developing route finder in it . can u suggest some sources from where i may get help .
please help !!!

thanhtung says:

October 17, 2007 at 5:28 am

help me!
I want update record in .DBF from query SQL VS C#
database ABC.dbf field:
ID,Name,street
I want rename of name but no known doing.
I known only sqlserver codding .net
help me!

sergio che says:

November 14, 2007 at 10:12 pm

I need help on what are the requirements filter SQL, as it does not work for me just to put

DATA “the_geom FROM name_table”

I mark the following error

msDrawMap(): Image handling error. Failed to draw layer named ‘vegetacion_potenc
ial’.
prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual que
ry) statement: ‘DECLARE mycursor BINARY CURSOR FOR SELECT “lib”::text,asbinary(f
orce_collection(force_2d(the_geom)),’NDR’),gid::text from vegetacion_potencial W
HERE the_geom && setSRID(’BOX3D(-118.64174423 11.4890309023497,-85.21563187 35.7
823256776503)’::BOX3D, 4326 )’

Postgresql reports the error as ‘ERROR: column “lib” does not exist
LINE 1: DECLARE mycursor BINARY CURSOR FOR SELECT “lib”::text,asbina…
^
‘

More Help:

Error with POSTGIS data variable. You specified ‘check your .map file’.
Standard ways of specifiying are :
(1) ‘geometry_column from geometry_table’
(2) ‘geometry_column from (sub query) as foo using unique column name using SRID
=srid#’

Make sure you put in the ‘using unique column name’ and ‘using SRID=#’ clauses
in.

Thinking in GIS

a blog about GIS, CMS, agile development and design patterns