With SQL 2008 R2, and the associated report control, we can create reports using spatial data. However, CRM 4.0 doesn’t support the spatial data type as it stands. It can be done with a bit of additional work though. I’ll go through my ideas in 2D space below, for 3D, just add the z parameter.
There are 2 distinct ways that this can be done. The first is to create a parallel database and use inner join SQL queries to get the relevant spatial data. However, this is usually only best for spatial data that is either static, or updatable from another application or interface. For my purposes, I usually use this when I want to use shape files of countries, or regions. This has the added benefit of already being captured in coordinate for (i.e. latitude and longitude).
To get these via SQL as a point, one of the options would be to run the following snippet in the query where latitude and longitude are the attribute/field names:
1: geography::Point(isnull(latitude,0), isnull(longitude,0), 4326) as LatLon
For points, lines, or polygons within CRM itself, it can be a little more tricky. For points, you can store the latitude and longitude in separate fields and then convert them into the “geometry” datatype in the SQL query. For lines and polygons, one of the ways of maintaining and managing these in CRM is to create a custom entity (for argument sake called Point) which has two attributes, one for latitude, and one for longitude (or x and y). Then attach this entity as a child entity to whatever you want to keep spatial data for (a route for example). The one gotcha of this is that the Point entity has a primary entity. An easy way around this would be to run some client script to default the value to “Point” or something. However, you may want to put meaningful names in for reference on your report (e.g. Start, End, etc.).
When running the report, you would just need to collate all of the different points for a route. You could use a similar syntax as for a point, but rather for a line or polygon.
Tech P.O.V. 