TUTORIAL 2: BUILDING A POSTGIS LAYER VIEWER

postgis is the open source spatial database extension to PostgreSQL and enables you to store shapefiles and make spatial queries.  
To view layers stored in postgis database you need to first build a shapefile viewing application like the one described above but additional functionality to connect to a postgis database has to be added, this is outlined in this tutorial.first of all ensure that you have PostgreSQL and postgis installed on your computer, they are both open source softwares and are available for download on the internet . then make sure you have loaded a shapefile into the postgis database using the shapefile loader that comes with your postgis.
before you start you have to download npgsql which is the .net data provider for PostgreSQL the zip file can be downloaded here. after downloading the zip file extract the two files in it( i.e. npgsql and monosecurity ) copy these two files into the bin file in the project folder for the vb.net project you are working on then go to your project in visual studio and right click on references in your project explorer window and select add reference , browse to the bin folder of your project and add references to both npgsql and monosecurity. once that is done add a button to shapefile viewer application we created in tutorial one on this blog. this button show have the the "show dialog" code for the form below .


to connect to the postgis database a new form has to be created the form should have a design similar to the one shown below

code for the form is as follows:

'the following declarations have to be added to the code don't put them in any sub

    Dim userName As String
    Dim password As String
    Dim port As String
    Dim hostName As String
    Dim dbName As String




FOR THE CONNECT BUTTON THE CODE IS AS FOLLOWS

 Private Sub txtConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtConnect.Click
'note  txtUserName, txtPassword, txtPort,  txtHost and txtDatabase are names of the textboxes on the form

        userName = txtUserName.Text

        password = txtPassword.Text

        port = txtPort.Text

        hostName = txtHost.Text

        dbName = txtDatabase.Text

        Dim npgConnection As Npgsql.NpgsqlConnection = Nothing

        npgConnection = New NpgsqlConnection("Server=" & hostName & ";UID=" & userName & ";PWD=" & password & ";Database=" & txtDatabase.Text & ";Port=5432;")

        Try

            npgConnection.Open()

            MessageBox.Show("Connecting success")

           

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub

FOR THE LOAD TO VIEWER BUTTON ADD THE FOLLOWING CODE



Private Sub btnPgsql2Shp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPgsql2Shp.Click
        If lstAvailbeLayers.SelectedIndex = -1 Then
            MessageBox.Show("Please select layer from available layers ")
        Else

            Dim layerName As String = lstAvailbeLayers.SelectedItem.ToString()
            ToolStripStatusLabel1.Text = "Transfering PostGis layer to shape   ....."
        
            Dim Cmd As String
            Cmd = " -h " + hostName + " -p " + "5432" + " -u " + userName + " -P " + password + " -f " +_ "C:\shp" + "\" + layerName + ".shp" + " " + dbName + " " + layerName

            executeCommand("pgsql2shp", Cmd)

            If My.Computer.FileSystem.FileExists("C:\shp" + "\" + layerName + ".shp") = True Then
                Dim sfiles As New MapWinGIS.Shapefile
                sfiles.Open("C:\shp" + "\" + layerName + ".shp")
                'note frmMain is the name of the form for the shapefile viewer we developed in tutorial one
                frmMain. mapMain.AddLayer(sfiles, True)
            Else
                MsgBox("THE FILE COULD NOT BE LOADED TO VIEWER")
            End If

        End If

    End Sub



FOR THE SHOW AVAILABLE LAYERS BUTTON THE CODE IS AS FOLLOWS


Private Sub BtnAvailbleLayers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles_ Button2.Click

        Dim npgConnection As Npgsql.NpgsqlConnection = Nothing
        npgConnection = New NpgsqlConnection("Server=" & hostName & ";UID=" & userName &_ ";PWD=" & password & ";Database=" & txtDatabase.Text & ";Port=5432;")

        npgConnection.Open()
    

        Dim strSql As String = "select f_table_name from geometry_columns"
        Dim daPgsql As IDbDataAdapter = New Npgsql.NpgsqlDataAdapter(strSql, npgConnection)
        Dim dsPg As New DataSet()

        daPgsql.Fill(dsPg)
      
        Dim dtPg As DataTable = dsPg.Tables(0)
        lstAvailbeLayers.Items.Clear()
        For i As Integer = 0 To dtPg.Rows.Count - 1
            lstAvailbeLayers.Items.Add(dtPg.Rows(i)(0))
        Next i

    
    End Sub

INCLUDE THIS SUB IN YOUR CODE



Private Sub executeCommand(ByVal commandType As String, ByVal commandSentence As String)
        Try
            Dim info As New System.Diagnostics.ProcessStartInfo()
            info.FileName = "C:\Program Files\PostgreSQL\8.3\bin\" & commandType & ".exe "
            info.Arguments = commandSentence
            info.CreateNoWindow = True
            info.UseShellExecute = False
            Dim proc As New System.Diagnostics.Process()
            proc.StartInfo = info
            proc.Start()
            proc.WaitForExit()

        Catch ex As Exception
            ToolStripStatusLabel1.Text = ex.ToString()
        End Try
    End Sub