topic Re: SqlServer 2008 R2 geometry polygon to ArcGis Explorer in ArcGIS Explorer Desktop Questions

SqlServer 2008 R2 geometry polygon to ArcGis Explorer

GeorgeKeradinides — Sun, 01 May 2011 22:07:30 GMT

I have a table in SqlServer 2008 R2 with a geometry polygon field.

CREATE TABLE FLDS(
FLD_ID int IDENTITY(1,1) NOT NULL
,FLD_LABEL VARCHAR(80) NULL
,FLD_POLYGON GEOMETRY NULL
)
GO
ALTER TABLE FLDS ADD CONSTRAINT PK_FLD_ID PRIMARY KEY CLUSTERED (FLD_ID)
GO

Can please someone help me with a piece of code to display these polygon in a map at ArcGis Explorer 1500 Desktop?

Also I would like the polygons to have the FLD_LABEL column as a label.

Thanks

Re: SqlServer 2008 R2 geometry polygon to ArcGis Explorer

GeorgeKeradinides — Thu, 12 May 2011 18:09:36 GMT

I have created the following methods, but still I can't see the polygons on map.
Can somebody tell me the missing piece?

Thanks

private void AddExistingShapes()
{
   string connectionString = "Data Source=.;Initial Catalog=agrosyn;Persist Security Info=True;User ID=sa;Password=sa";
   System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
   con.ConnectionString = connectionString;
   System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT FLD_POLYGON.ToString() FROM FLDS", con);

            List<Graphic> _allFldsGrph = new List<Graphic>();
            Polygon plgnTmp = new Polygon();

            {
    con.Open();
      using (System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader())
      {
       while(rdr.Read())
       {
      string strPlgn = rdr.GetString(0);
                        plgnTmp = StringToPolygon(strPlgn);
                        _allFldsGrph.Add(GeometryToGraphic(plgnTmp));
       }
       rdr.Close();
      }
      con.Close();
               _md.Graphics.Add(_allFldsGrph);
    }
}

        private Polygon StringToPolygon(string strGeom)
        {
            strGeom = strGeom.Replace("POLYGON ((", "");
            strGeom = strGeom.Replace("(", "");
            strGeom = strGeom.Replace(")", "");
            strGeom = strGeom.Replace(", ", "#");
            strGeom = strGeom.Replace(".", ",");
            string[] strArr = strGeom.Split(new char[] { '#' });

            Polygon poly = new Polygon();
            double dblX;
            double dblY;
            string strBuffer = "";

            for (int i = 0; i < strArr.Count(); i++)
                {
                    int ialpha = strArr.ToString().IndexOf(' ');
                    strBuffer = strArr.Substring(0, ialpha);
                    dblX=Convert.ToDouble(strBuffer);
                    strBuffer = strArr.Substring(ialpha + 1).Trim();
                    dblY=Convert.ToDouble(strBuffer);
                    poly.AddPoint(new ESRI.ArcGISExplorer.Geometry.Point(dblX, dblY));
                }
            poly.Close();
            return poly;
        }

Where _md is my ESRI.ArcGISExplorer.Application.Application.ActiveMapDisplay

Re: SqlServer 2008 R2 geometry polygon to ArcGis Explorer

BrianLeroux — Fri, 12 Aug 2011 12:48:47 GMT

George,
I am lookign into connecting to a SQL server as well. I wanted to see if you made any progress with this before I start my attempts. I appreciate any insight you can give.

Thanks..

Re: SqlServer 2008 R2 geometry polygon to ArcGis Explorer

GeorgeKeradinides — Fri, 26 Aug 2011 13:10:09 GMT

Hello Brian

I didn't have any progress with this project, because of another emerging one.
I will check again next month and let you know.
If you have any progress or want any help, keep in touch.

Regards George

Re: SqlServer 2008 R2 geometry polygon to ArcGis Explorer

BrianLeroux — Sat, 11 Dec 2021 14:39:33 GMT

I have made progress with this. Although there is still more work to do like making my query dynamic, the concept is done. Here is the code I used. It will fill a DataGridView first when display data is clicked. Then you can choose to add a selected item or all items to the maps as notes. Here is the code. Hope it helps.

Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System.Windows.Forms
Imports ESRI.ArcGISExplorer
Imports ESRI.ArcGISExplorer.Application
Imports ESRI.ArcGISExplorer.Geometry
Imports ESRI.ArcGISExplorer.Mapping
Imports ESRI.ArcGISExplorer.Data
Imports ESRI.ArcGISExplorer.Threading

Public Class Form1

    ' Initialize constants for connecting to the database
    ' and displaying a connection error to the user.
    Protected Const SqlConnectionString As String = _
        "Server=Your Server Name;" & _
        "DataBase=YourDBName;" & _
        "Integrated Security=SSPI"

    Protected Const ConnectionErrorMessage As String = _
        "To run this sample, you must have SQL " & _
        "installed.  For " & _
        "instructions on installing SQL, view the documentation file."

    Protected didPreviouslyConnect As Boolean = False
    Protected didCreateTable As Boolean = False
    Protected connectionString As String = SqlConnectionString

#Region "Display data"
    ' Handles the click event for the Display button. This handler gets the product
    ' information from the Contact table puts it into a DataSet which is used to
    ' bind to a DataGrid for display. Custom style objects are used to give the 
    ' DataGrid a nice appearance.
    Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click

        If IsNothing(DataGridView1.DataSource) Then

            Dim strSQL As String = _
                "USE YourDBName" & vbCrLf & _
                "SELECT * " & _
                "FROM dbo.tblGIS"

            Try
                ' The SqlConnection class allows you to communicate with SQL Server.
                ' The constructor accepts a connection string as an argument.  This
                ' connection string uses Integrated Security, which means that you 
                ' must have a login in SQL Server, or be part of the Administrators
                ' group for this to work.
                Dim dbConnection As New SqlConnection(connectionString)

                ' A SqlCommand object is used to execute the SQL commands.
                Dim cmd As New SqlCommand(strSQL, dbConnection)

                ' The SqlDataAdapter is responsible for using a SqlCommand object to 
                ' fill a DataSet.
                Dim da As New SqlDataAdapter(cmd)
                Dim sqlData As New DataSet()
                da.Fill(sqlData, "Data")

                With Me.DataGridView1
                    .Visible = True
                    .AutoGenerateColumns = False
                    .AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
                    .BackColor = Color.WhiteSmoke
                    .ForeColor = Color.MidnightBlue
                    .CellBorderStyle = DataGridViewCellBorderStyle.None
                    .ColumnHeadersDefaultCellStyle.Font = New Font("Tahoma", 8.0!, FontStyle.Bold)
                    .ColumnHeadersDefaultCellStyle.BackColor = Color.MidnightBlue
                    .ColumnHeadersDefaultCellStyle.ForeColor = Color.WhiteSmoke
                    .DefaultCellStyle.ForeColor = Color.MidnightBlue
                    .DefaultCellStyle.BackColor = Color.WhiteSmoke
                End With


                Me.DataGridView1.DataSource = sqlData.Tables(0)
                Dim newColumn As Integer = Me.DataGridView1.Columns.Add("ID", "ID")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "ID"

                newColumn = Me.DataGridView1.Columns.Add("Name", "Name")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Name"

                newColumn = Me.DataGridView1.Columns.Add("Address", "Address")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Address"

                newColumn = Me.DataGridView1.Columns.Add("City", "City")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "City"

                newColumn = Me.DataGridView1.Columns.Add("State", "State")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "State"

                newColumn = Me.DataGridView1.Columns.Add("Zip", "Zip")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Zip"

                newColumn = Me.DataGridView1.Columns.Add("X", "X")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "X"

                newColumn = Me.DataGridView1.Columns.Add("Y", "Y")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Y"

                newColumn = Me.DataGridView1.Columns.Add("Geog", "Geog")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Geog"

                newColumn = Me.DataGridView1.Columns.Add("Data1", "Data1")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Data1"

                newColumn = Me.DataGridView1.Columns.Add("Data2", "Data2")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Data2"

            Catch sqlExc As SqlException
                MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
                    MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If

    End Sub
#End Region


#Region "Create Layer"
    'Add all the elements of the table to the map as notes in a notes folder.
    Sub btnAddNotes_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNotes.Click

        Dim mapDisp As MapDisplay = ESRI.ArcGISExplorer.Application.Application.ActiveMapDisplay
        'create a folder


        Dim fold As New Folder("SQL Notes")

        mapDisp.Map.ChildItems.Add(fold)

        'get coordinates of 1st selected record
        Dim strX As String = Me.DataGridView1.SelectedRows(0).Cells("X").Value
        Dim strY As String = Me.DataGridView1.SelectedRows(0).Cells("Y").Value
        Dim strName As String = Me.DataGridView1.SelectedRows(0).Cells("Name").Value
        Dim point As ESRI.ArcGISExplorer.Geometry.Point = ESRI.ArcGISExplorer.Geometry.Point.CreateFromLatitudeLongitude(strX, strY)

        Dim note As New ESRI.ArcGISExplorer.Mapping.Note(strName, point, ESRI.ArcGISExplorer.Mapping.Symbol.Marker.Pushpin.Blue)

        fold.ChildItems.Add(note)

    End Sub

    Sub btnAddAllNotes_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddAllNotes.Click

        Dim mapDisp As MapDisplay = ESRI.ArcGISExplorer.Application.Application.ActiveMapDisplay
        'create a folder


        Dim fold As New Folder("SQL Notes")

        mapDisp.Map.ChildItems.Add(fold)

        For Each dgvr As DataGridViewRow In DataGridView1.Rows

            Dim strX As String = dgvr.Cells("X").Value
            Dim strY As String = dgvr.Cells("Y").Value
            Dim strName As String = dgvr.Cells("Name").Value
            Dim point As ESRI.ArcGISExplorer.Geometry.Point = ESRI.ArcGISExplorer.Geometry.Point.CreateFromLatitudeLongitude(strX, strY)

            Dim note As New ESRI.ArcGISExplorer.Mapping.Note(strName, point, ESRI.ArcGISExplorer.Mapping.Symbol.Marker.Pushpin.Blue)

            fold.ChildItems.Add(note)
        Next
    End Sub

#End Region

    Private Sub exitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitToolStripMenuItem.Click
        Me.Close()
    End Sub
End Class