-----------------------------------

Acquista i software ArcGIS tramite Studio A&T srl, rivenditore autorizzato dei prodotti Esri.

I migliori software GIS, il miglior supporto tecnico!

I migliori software GIS, il miglior supporto tecnico!
Azienda operante nel settore GIS dal 2001, specializzata nell’utilizzo della tecnologia ArcGIS e aderente ai programmi Esri Italia Business Network ed Esri Partner Network

-----------------------------------



giovedì 30 luglio 2009

SQL Server 2008 Layer

This is an example of how to visualize spatial data stored in SQL Server 2008 (and Express) using Silverlight ESRI API. In a nutshell, we create a WCF service acting as a connection to database SQL Server which reads spatial data in a table with a geography field and converts SQL geography format into geometry ESRI.
In WFC service, in the reference we use the WPF Silverlight ESRI API library containing the definition of geometry class which can be used with the same contract as far as the silverlight client part is concerned.
Calling the WFC Service from client silverlight allows you to catch objects (including geometry ESRI) to be visualized on the map.
With this code you can load points, lines and polygons but geography not valid data have not still been checked.

From sqlgeography to geometry ESRI:


public static Geometry CreateGeography(SqlGeography geometry)

        {

 

            string geometryType = geometry.STGeometryType().Value;

 

            if (string.Compare(geometryType, "Point") == 0)

                return geometry.GetPointN(1);

            else if (string.Compare(geometryType, "MultiPoint") == 0)

            {

                MultiPoint multipoint = new MultiPoint();

                geometry.PopulatePointCollection(multipoint.Points);

                return multipoint;

            }

            else if ((string.Compare(geometryType, "Polygon") == 0) || (string.Compare(geometryType, "MultiPolygon") == 0))

            {

                return geometry.BuildPoly<Polygon>((Polygon p, PointCollection t1) => p.Rings.Add(t1));

            }

            else if ((string.Compare(geometryType, "LineString") == 0) || (string.Compare(geometryType, "MultiLineString") == 0))

            {

                return geometry.BuildPoly<Polyline>((Polyline p, PointCollection t1) => p.Paths.Add(t1));

            }           

            return null;

        }



In order to visualize your own data, in the WFC Service insert your connection string to database.


public System.Collections.Generic.List<Feature> GetSpatialData(Query query)

        {

            try

            {

 

 

                ///////////////////////////////////////////////////////////////////////////////////////////////////////////

                string connString = @"Data Source=PC-HOME\SQLEXPRESS;Initial Catalog=SpatialData;Integrated Security=True";

                //For instance @"Data Source=MyServerName\sqlexpress;Initial Catalog=SpatialData;Integrated Security=True"

                ///////////////////////////////////////////////////////////////////////////////////////////////////////////

 

                string where = null;

                if (query.Geometry != null)

                {

                    SqlGeography spatialFilter = Utility.CreateGeography(query.Geometry);

                    where = string.Format("({0}.STIntersects(geography::STGeomFromText('{1}',{2})) = 1)", query.GeometryColumnName, spatialFilter.ToString(), spatialFilter.STSrid.Value.ToString());

                }

 

                if ((where != null) && (!string.IsNullOrEmpty(query.Where)))

                   where += " AND ";





In the XAML page you have to set the name of the field containing geography (GeometryColumnName), the name of the table (Table) and the name of the service WFC (Service) for GeoDataLayer.


<samples:GeoDataLayer x:Name="GeoDataLayer" GeometryColumnName="GEOM"

                 Table="[SpatialData].[dbo].[LimitiComunali]" MaxRecordCount="10"

                                  Service="http://localhost:3088/ISpatialData.svc">




An option is adding a filter (Where), a max record count (MaxRecordCount) and a spatial filter (Geometry).
Since a map tip is created for every visualized feature, it is possible to point out the fields you want to visualize. If you do not point them out, all will be visualized.


<samples:GeoDataLayer.OutFields>

    <!--Empty for all fields-->

    <!--<sys:String>IdCameretta</sys:String>

                    <sys:String>NomeCameretta</sys:String>-->

  </samples:GeoDataLayer.OutFields>







Download here

12 commenti:

Erick T ha detto...

Thanks for this excellent example! Do you see any issues with using SqlGeometry as opposed to SqlGeography?

I've tried, and quickly run into ring direction issues, and I'm not sure what else is waiting for me.

Ing. Domenico Ciavarella ha detto...

SqlGeometry SRID is only used as metadata, while if you have to do buffer operations etc. and you need to compare different SRIDs, you have to use SqlGeography. To help you validating SqlGeography you can use SpatialTool. For any further specific question, post it!

Anonimo ha detto...

Thank you for the sample. Have you tried bringing in Oracle sde data? I really appreciate any help displaying and editing using Silverlight application with Oracle sde data.

Alexander ha detto...

Domenico,
Thanks a lot for this sample! Very useful.

Ing. Domenico Ciavarella ha detto...

I suggest you to create a WFC service restful for editing using Silverlight application with Oracle sde data or to wait for 9.4 version ...

Artist ha detto...

I've been follow this Example and i used database from SQL Server 2008 - Virtual Earth but I've got some problem :

System.Exception was unhandled by user code
Message="No data!"
StackTrace:
at ESRI.ArcGIS.Client.Layer.OnInitializationFailed(EventArgs e)
at ESRI.ArcGIS.Client.Layer.Initialize()
at ESRI.ArcGIS.Samples.GeoDataLayer.loader_LoadFailed(Object sender, GeoDataLoadFailedEventArgs e)
at ESRI.ArcGIS.Samples.GeoData.GeoDataLoader.client_GetSpatialDataCompleted(Object sender, GetSpatialDataCompletedEventArgs e)
at GeoData.SpatialData.SpatialDataClient.OnGetSpatialDataCompleted(Object state)


Thanks in advanced

Ing. Domenico Ciavarella ha detto...

did you check the connection to db?

Kevin ha detto...

I wonder if you can Help , I converted SHP files using SpatialTool into GEOMETRY fields and not Geaography type, how can I use your method to load the polygons on an ESRI/SILVERLIGHT map

Ing. Domenico Ciavarella ha detto...

you must change code.

SureshRajamani ha detto...

How to do this in ArcGIS Javascript Api?

Ing. Domenico Ciavarella ha detto...

Now you can use Query Layer in ArcGIS 10 so you can load table or view with geometry in arcmap and then publish the service.
http://www.youtube.com/watch?v=JSDt0Mi5vHE

Ing. Domenico Ciavarella ha detto...

you can see also this samples:
http://www.arcgis.com/home/item.html?id=6d28a606369c43fd9a6f929541ae7c93