Thinking in GIS

In this section we will adapt the ASP .Net Tutorial to work both with shapefiles or with PostGIS layers.

The only c# code that needs to be modified is only the code that updates the point layer.

The tutorial is composed of basically 2 methods that update a point layer:

• the first method adds a point and its attributes to the point layer (AddPoint method)
• the second method delete all the points from the point layer (butClear_Click)

After you will terminate this step the tutorial will work both with shapefiles and PostGIS layers.

Adding a reference to PostgreSQL .NET dll to the Visual Studio solution

You will connect to PostgreSQL from your ASP .NET Project. To do so I have chosen to use the npgsql .NET Data Provider. You should find it with your PostgreSQL distribution.

Basically you need the Npgsql.dll and the Mono.Securirty.dll to be added as .NET references for your Visual Studio Solution.

Modifying web.conf application key to use a MapFile with PostGIS connection

Update the sections of the web.config file.

First you need to replace the value for the key "mapFilePath" with the correct value: the full path to the MapFile that is connecting layers with PostGIS.

Then you have to add a second key, named "postgreSQLConnectionString" with the value setted to the connection string for PostgreSQL. There parameters for the connection string are similiar to the ones that you used for connecting to PostgreSQL via the MapFile.

  <appSettings>
    <!--  Path to MapFile -->
    <add key="mapFilePath" 
         value = "C:\training\mapServerTutorial\data\csharptutorial.map"
    />
    <add key="postgreSQLConnectionString"
         value = "Server=127.0.0.1;Port=5432;User Id=psqluser;password=psqluser;Database=TUTORIAL;"
    />
  </appSettings>

Modifying the c# method for adding points to the layer

Delete from the default.cs class the previously typed AddPoint method with these new code:

                /// 

        /// Add a point feature to point shapefile with an array of values for dbf, or add a point and attributes to a point PostGIS feature class
        /// 
        /// x image coordinate
        /// y image coordinate
        /// name of the active layer
        /// array with field names and values
        private void AddPoint(Double x, Double y, String activeLayer, String[,] fieldValues)
        {
      //check if the active layer is a point layer and if the point layer is from a shapefile or from PostGIS
      layerObj layer = map.getLayerByName(activeLayer);
      if(layer.type!=MS_LAYER_TYPE.MS_LAYER_POINT)
      {
        //notify action
        lblInfo.Text = "This action can be performed only on point layers.";
        return;
      }
      //convert the image point in map point
      pointObj point = pixel2point(new pointObj(x,y,0,0)); 
      //generate the sql INSERT statment
      //get field list and value list to use in the query on dbf
      String fieldList = "";
      String valueList = "";
      for(int i=0; i<(fieldValues.Length/2); i++)
      {
        fieldList = fieldList + fieldValues[i,0];
        valueList = valueList + "'" + fieldValues[i,1] + "'";
        if(i<((fieldValues.Length/2)-1))
        {
          fieldList = fieldList + ", ";
          valueList = valueList + ", ";
        }
      }
      //add the point to a shapefile
      if(layer.connectiontype==MS_CONNECTION_TYPE.MS_SHAPEFILE)
      {
        String shapeFullPath = map.shapepath + "\\" + activeLayer + ".shp";
        shapefileObj shapefile = new shapefileObj(shapeFullPath,-2);
        /*Alternative way to insert a point in the shapefile using shapeObj:
        //create line to store the point
        lineObj line = new lineObj();
        line.add(point);
        //create shape
        shapeObj shape = new shapeObj((int)MS_SHAPE_TYPE.MS_SHAPE_POINT);
        shape.add(line);
        //add shape to shapefile
        shapefile.add(shape);
        */
        shapefile.addPoint(point);
        //add record for dbf table
        String sqlInsert = "INSERT INTO " + activeLayer + " (" + fieldList + ") VALUES(" + valueList + ")";
        OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + map.shapepath + ";Extended Properties=dBASE IV;User ID=Admin;Password=");
        cn.Open();
        OleDbCommand com = cn.CreateCommand();
        com.CommandText = sqlInsert;
        com.CommandType = CommandType.Text;
        com.ExecuteNonQuery();
        cn.Close();
        shapefile.Dispose();
      }
      //add the point to a PostGIS table
      if(layer.connectiontype==MS_CONNECTION_TYPE.MS_POSTGIS)
      {
        //set CurrentCulture according to PostgreSQL server
        CultureInfo newCultureInfo = new CultureInfo("en-US");
        newCultureInfo.NumberFormat.NaNSymbol = "";
        Thread.CurrentThread.CurrentCulture = newCultureInfo;
        //connect with PostgreSQL
        //the sqlInsert includes also the geometry (with shapefile we need to make 2 different steps)
        String sqlInsert = "INSERT INTO " + activeLayer + " (" + fieldList + ",the_geom) VALUES(" + valueList + ", GeomFromText('POINT(" + point.x.ToString() + " " + point.y.ToString() + ")',-1))";
        //reads connection string for PostgreSQL
        String connectionString = System.Configuration.ConfigurationSettings.AppSettings["postgreSQLConnectionString"].ToString();
        Npgsql.NpgsqlConnection cn = new Npgsql.NpgsqlConnection(connectionString);
        cn.Open();
        Npgsql.NpgsqlCommand com = cn.CreateCommand();
        com.CommandText = sqlInsert;
        com.ExecuteNonQuery();
        cn.Close();
      }
      //notify action
      lblInfo.Text = "Point added to " + activeLayer + " point layer.";
        }

This method is accepting 4 parameters: the x,y image coordinates of the point to be inserted in the layer, the active layer name, and an array with the field names and values.

The first thing to be performed is to check if the active layer is a point layer. If not we have to stop the execution.

If the active layer is a point layer, then we create the real point from the image coordinates.

Then we create a string for the INSERT INTO (field1,field2,...) VALUES (value1,value2,...) statment to be executed in the dbf or PostgreSQL database.

We have two differents way to make this INSERT, depending if the layer is a point Shapefile or a point PostGIS layer.

Updating the shapefile

With a shapefile we need to do 2 consecutive steps (would be better to have a transaction just to be sure that both are performed):

• First we need to generate a new geometry in the shp file using the shapefileObj MapScript class. To do so we can use the shapefileObj .add(shape) or the .addPoint(point) method
• Then we need to insert a new record in the dbf table. To do so I use an OleDBCommand

Updating the PostGIS layer

With a PostGIS layer is much easier and powerfull than with a shapefile.

We can just use SQL to perform the INSERT of both geometric and textual information. And to do so we just need to perform an unique SQL statment.

To do so I use a NpgsqlCommand object.

Modifying the c# method for clearing all teh points from the layer

Delete from the default.cs class the previously typed butClear_Click method with these new code:

        /// 

        /// Restore the original point shapefile or delete all the records from the PostGIS layer
        /// 
        /// 
        /// 
        private void butClear_Click(object sender, System.EventArgs e)
        {
            String shapeFullPath = map.shapepath + "\\" + ddlLayers.SelectedItem.Text + ".shp";
            layerObj layer = map.getLayerByName(ddlLayers.SelectedItem.Text);
      //action allowed only for point layer
      if(layer.type!=MS_LAYER_TYPE.MS_LAYER_POINT)
      {
        //notify action
        lblInfo.Text = "This action can be performed only on point layers.";
        return;
      }
      //different delete action if layer is shapefile or PostGIS
      //shapefile layer
            if(layer.connectiontype==MS_CONNECTION_TYPE.MS_SHAPEFILE)
            {
                //Clear the point shapefile by restoring its copy
                //Create a DirectoryInfo object representing the specified directory.
                DirectoryInfo dir = new DirectoryInfo(map.shapepath);
                //Get the FileInfo objects for every file that belongs to shapefile in the directory.
                FileInfo[] files = dir.GetFiles(ddlLayers.SelectedItem.Text + "Copy.*");
                for(int i=0; i

The first thing to be performed is to check if the active layer is a point layer. If not we have to stop the execution. If the active layer is a point layer, then we are going to clear all the point features in this layer.

We have two differents way to make this deletion, depending if the layer is a point shapefile or a point PostGIS layer.

Deleting features from the shapefile

With a shapefile we will copy the original shapefile with 0 features, replacing the actual shapefile, served by MapServer, with n features.

Deleting features from a PostGIS layer

With a PostGIS layer is much easier and powerfull than with a shapefile.

We can just use SQL to perform the DELETE of every feature in the PostGIS layer.