Example: Tracing Virtual Earth into SQL Server 2008

This topic continues the series of SQL Server 2008 examples set forth in the SQL Server Spatial DBMS Facilities topic. We use data from Microsoft Virtual Earth to create a drawing within SQL Server 2008 showing buildings in the Microsoft Redmond campus and then we edit that drawing to show how drawings linked from SQL Server 2008 into Manifold maintain their dynamic links back to SQL Server spatial storage. This example assumes data sources have already been configured as set forth in the Example: Configuring SQL Server 2008 topic.

 

We begin this example by opening the US Base Map.map project from the Exporting KML to Google Earth tutorial topic. That project has two handy linked images, one from Microsoft Virtual Earth satellite imagery and the other from Microsoft Virtual Earth road map imagery, and also has a useful map of the US as boundaries. We are mainly interested in the Virtual Earth layers. This example assumes, of course, that we are connected to the web so that Manifold facilities such as image server module interfaces, will operate correctly.

 

We delete all the components from that project we don't need and we save it under a different name. This will be our new project for creating a drawing that shows buildings in the Microsoft Redmond campus.

 

images\eg_redmond_01.gif

 

We begin with a project that has two linked images and a drawing. We won't use the USA drawing but it is there in the project should we need it. We create a map from the Virtual Earth Satellite Image and we open the map.

 

images\eg_redmond_02.gif

 

Wow! As always, the sheer breadth and quality of Virtual Earth imagery never fails to impress. This particular image was linked using the extents of the entire US plus parts of Mexico and Canada. The amazing thing about the image before us is that we can zoom into any part of it and Manifold will arrange to stream in more detailed image tiles to the limit of resolution supported by the host image server. Considering that the host image server is Microsoft Virtual Earth that ultimate detail is impressive indeed. We can zoom into this image to see virtually any part of the US in astonishing detail.

 

Our objective is to zoom into that part of Redmond hosting the main Microsoft campus, but it is not so easy to see where Redmond is from a satellite view.

 

images\eg_redmond_03.gif

 

To provide better orientation, we will drag the Microsoft Virtual Earth street map image into the map and then use the Zoom Box tool to zoom into the Seattle area.

 

images\eg_redmond_04.gif


Note that the magic of Manifold image server technology connected to Virtual Earth automatically fetches more detailed views. Knowing that Redmond is somewhere near Bellevue we will zoom again into the Seattle area near Bellevue.

 

images\eg_redmond_05.gif

 

There's Redmond... let's zoom closer.

 

images\eg_redmond_06.gif

 

At this point we know we are looking at Redmond, but we are darned if we know where exactly Microsoft is in this display. To get a fix on the Microsoft campus, we will use Manifold's built-in geocoding capability (available if we have the Geocoding Tools extension installed or any edition from Universal edition on up).

 

images\eg_redmond_07.gif

 

We choose Edit - Go To, choose Address in the Go To box and give the published address of Microsoft Corporation. Press OK.

 

images\eg_redmond_08.gif

 

That's better. Let's zoom further into the Microsoft campus.

 

images\eg_redmond_09.gif

 

At this point we have zoomed far enough into the Virtual Earth street image that we can see individual Microsoft buildings. Time to switch to the satellite image.

 

images\eg_redmond_10.gif

 

We do this by double-clicking on the Virtual Earth Street Map Image layer to turn it off, and clicking on the Virtual Earth Satellite Image layer to make it the active layer. We zoom further into the image.

 

images\eg_redmond_11.gif

 

That's a good view of much of the main Microsoft campus in Redmond. Let's zoom even further into the image, to the point that an individual Microsoft building fills the screen.

 

images\eg_redmond_12.gif

 

Very good. Now we will switch gears a bit and create a drawing into which we can draw areas that trace over the shapes of individual buildings.

 

To do so, we right click onto the Virtual Earth Satellite Image tab and choose Add - New Drawing from the context menu.

 

images\eg_redmond_13.gif

 

The Create Drawing dialog allows us to name the new drawing and to provide any descriptive notes we would like. We enter the name Buildings and press OK.

 

images\eg_redmond_14.gif

 

A new tab appears in the map for the new drawing.

 

images\eg_redmond_15.gif

 

In the project pane, we see that a new drawing, called Buildings, has been created, together with the drawing's table.

 

images\eg_redmond_16.gif

 

If we open the drawing's table we see that it is empty and consists of just one column, the built-in ID column that stores the object ID for any objects added to the drawing. We will add another column. To do so, we right click on the column head.

 

images\eg_redmond_17.gif

 

From the context menu we choose Add - Column.

 

images\eg_redmond_18.gif

 

In the Add Column dialog we provide a name for the new column (we will call it Buildings) and for the type of the column we choose Text (ANSI, variable-length) from the seemingly endless list of different database types offered by Manifold in the Type box pull-down menu. Press OK.

 

images\eg_redmond_19.gif

 

Very good. We now have a new column in the drawing's table. So far there is nothing in the table because we have not added any objects to the drawing.

 

One more thing: because this is an example and we want to show off a bit, we will change the new drawing's projection. When a new drawing is created in a map it inherits the projection of the map, in this case the same Mercator projection used by the Virtual Earth layers from which the map was created.

 

images\eg_redmond_20.gif

 

Changing the projection of the Buildings drawing is easy. First, we right click on the Buildings drawing in the project pane and choose Assign Projection to verify that the starting projection is what we intend it to be. This is a "belt and suspenders" safety check by Manifold applied to all components the first time they are used in a projections context.

 

Next, we right click on the Buildings drawing in the project pane and choose Change Projection to get the dialog shown above. In that dialog we choose Latitude / Longitude projection and press OK. That changes the projection of the drawing to Latitude / Longitude projection.

 

images\eg_redmond_21.gif

 

When the projection of a layer in a map is changed the map will re-sync itself as it re-projects the component on the fly to display it in the projection used by the map. We click the Back button to get back to the same zoom and pan used earlier.

 

There is one more housekeeping thing we will do before launching into a wonderful and productive editing session. We will turn on Instant Data mode.

 

images\eg_redmond_22.gif

 

To do this, we choose Edit - Instant Data. This sets a highly useful editing mode so that whenever we draw a new object into a drawing Manifold will automatically pop open an editing dialog to enable us to add field values for that object. This is one of those deals that sounds like a totally nerd-alert, dry feature but in actual practice is one of those things you cannot live without once you taste it.

 

images\eg_redmond_23.gif

 

With the focus on the Buildings drawing layer in our map, we click the Insert Area command to use that tool.

 

images\eg_redmond_24.gif

 

To use this tool we click on the outlines of the area desired. In this case we click along the edges of the building over which we want to create an area. This is a good example of tracing, whereby we create new map objects by tracing in a drawing layer above an image layer.

 

When we are finished drawing the area we right click to indicate we are done.

 

images\eg_redmond_25.gif

 

A new area appears in the drawing and because we have Instant Data mode on a dialog pops open to enable us to enter attribute data for that object. We enter 21 into the Buildings field because we know this is building 21 in the Microsoft campus. Press OK.

 

images\eg_redmond_26.gif

 

A new area object is created in the drawing.

 

images\eg_redmond_27.gif

 

If we open the drawing's table we see that a new record has been created for that drawing object, with the value of 21 for the Buildings field.

 

images\eg_redmond_28.gif

 

Let's pan over to a nearby building and trace over it to create another area object.

 

images\eg_redmond_29.gif

 

When we are done the Instant Data mode automatically pops open the dialog with the cursor pre-positioned in the last field edited with the last-used data entered, ready for us to modify it or enter new data.

 

Instant Data makes it extremely efficient to enter large numbers of new objects together with data attribute information for each new object. It is the first choice for data entry when recording assets or other repetitive information. For example, a crew can use it together with Manifold's GPS Console capability to record the location of thousands of assets, such as fire hydrants or electrical transformers or even 911 addressing in a day.

 

images\eg_redmond_30.gif

 

We enter 35 for this building and press OK.

 

images\eg_redmond_31.gif

 

In the drawing's table we see that we now have two records for the two objects in the drawing.

 

images\eg_redmond_32.gif

 

Zooming out a bit in the map we see there are now two area objects drawn into the Buildings drawing.

 

Let's switch gears and export the Buildings drawing into SQL Server 2008 spatial storage. We can't export a drawing into SQL Server unless it contains some objects. We have created two objects in this drawing so we can now export it into SQL Server. After export, we will link the drawing back into our Manifold project and continue editing it "in place" within SQL Server.

 

To export the drawing, we right click onto the Buildings drawing in the project pane and choose Export.

 

images\eg_redmond_33.gif

 

In the resulting dialog we choose Data Sources () as the type to which this drawing will be exported.

 

images\eg_redmond_34.gif

 

That launches Manifold's Data Source dialog, which helpfully remembers data sources that have been previously configured. We have two data sources, configured in the Example: Configuring SQL Server 2008 topic. We choose the data source using an explicit user login and password since that connects to the database used in previous examples.

 

images\eg_redmond_35.gif

 

In the resulting Export Drawing dialog we verify that the Type is SQL Server and otherwise accept defaults. Press OK.

 

The drawing is now exported into SQL Server 2008 spatial storage. For the rest of this example we will use the drawing resident within SQL Server.

 

To do that, we will first right click on the Buildings drawing in the project pane and delete it from our project. This also deletes it as a layer from the map. Next, we will link the drawing back in from the database. We will do so by launching Tools - Database Console.

 

images\eg_redmond_36.gif

 

The database console helpfully remembers the last-used data source. We press the Refresh button to refresh the connection.

 

images\eg_redmond_37.gif

 

After refresh, the database console lights up with a listing of all the components in this database. As we can see, there are plenty. For now, we have not used Administrator Console to assign friendly names so they are displayed in their native SQL Server object names. Manifold will helpfully put a drawing icon next to those database objects that it knows contain drawing geometry.

 

We click on the database object that is obviously our Buildings drawing uploaded in the prior step and we press the Link button to link it into our project.

 

images\eg_redmond_38.gif

 

The link options dialog allows us to choose to use all objects or just some area of interest subset. We use all objects since there is hardly anything to this drawing. If we wanted to grab just a manageable subset of some terabyte-sized drawing stored in SQL Server we would no doubt use some area of interest subset.

 

Manifold creates a version column by default to support multiuser editing of linked drawings. We make it a habit of using that column just in case someone else connected to the DBMS also wants to simultaneously edit the same drawing. Press OK.

 

images\eg_redmond_39.gif

 

A new drawing appears in the project pane. The drawing's icon incorporates a database cylinder graphic to indicate this is a drawing linked into the project from an external data source.

 

images\eg_redmond_40.gif

 

If we open the drawing we see that it contains two area objects. They look a bit strange because the drawing uses Latitude / Longitude as its native projection, and that projection shows considerable distortion in higher latitudes like those where Redmond is located.

 

images\eg_redmond_41.gif

 

If we open the drawing's table we see that it does indeed contain all the data attributes uploaded into SQL Server.

 

images\eg_redmond_42.gif

 

However, our task is not to look at the drawing on its own but to work with it in a layer above the Virtual Earth imagery we were using earlier. We therefore drag and drop the linked drawing from the project pane into the map. It appears as a layer, re-projected on the fly into the Mercator projection used by Virtual Earth and the map. Note that the objects overlay perfectly, as well we would expect them to do.

 

images\eg_redmond_43.gif

 

The gray default formatting of new objects is getting boring, so we will format the area background color for the Buildings layer to be bright yellow.

 

images\eg_redmond_44.gif

 

The interesting thing about the Buildings drawing layer is that it is a "real time" connection to the drawing stored within SQL Server. If we edit the drawing, we are dynamically changing the data in SQL Server. Although the layer seems local, it is a visual means of editing the contents of our database with none of the hassles of manually specifying coordinates or command-line interaction.

 

For example, we can zoom into a different building and create another area traced over it.

 

images\eg_redmond_45.gif

 

When we finish the object, because Instant Data mode is still one a dialog pops open to enable us to enter data for this object as well. We enter building 34.

 

images\eg_redmond_46.gif

 

Zooming out, we see that we now have three buildings in this drawing.

 

images\eg_redmond_47.gif

 

If we click open the drawing's table (also linked in automatically from SQL Server storage), we see that we have data for three objects in the table.

 

What is also interesting about the drawing's table is that it too is an interface that we can use to manage data within SQL Server. For example, suppose we want to add a new column to the table?

 

That is easy to do by right clicking on the column head and choosing Add - Column from the context menu

 

images\eg_redmond_48.gif

 

In the Add Column dialog we provide a name for the new column (we will call it Cafeteria) and for the type of the column we choose Text (Unicode, variable-length) just to show that Manifold can handle Unicode text as well as ANSI text. Press OK.

 

images\eg_redmond_49.gif

 

A new column appears in the table. We have actually added a new column within the table structure in the SQL Server 2008 database as well. Manifold has extraordinary capabilities for managing tables within databases, better than most DBMS products do.

 

images\eg_redmond_50.gif

 

Let's trace one more building. Back in the map window we use the Insert Area tool to create an area for another building.

 

images\eg_redmond_51.gif

 

When we finish the new area the instant data dialog automatically pops open as before, but this time it shows the new Cafeteria field as well. The cursor is helpfully positioned within the last field used, ready to go.

 

images\eg_redmond_52.gif

 

We enter 36 for the building and Y for the cafeteria field, intending to indicate this building has a cafeteria. Press OK.

 

images\eg_redmond_53.gif

 

The new area appears in the drawing. It uses the formatting we have specified for that drawing.

 

images\eg_redmond_54.gif

 

If we open the drawing's table we see that a record for the new object, complete with attributes as we've entered them, has appeared.

 

images\eg_redmond_55.gif

 

Zooming out, we can see we now have four new area objects in this drawing. The last two have been added using the linked drawing from SQL Server. The edits we've made in this interactive Manifold session have applied in real time to the data set stored in SQL Server.

 

images\eg_redmond_56.gif

 

All Manifold capabilities (thousands of them) apply to the use of such linked drawings from SQL Server 2008. For example, in the illustration above we've created a labels layer that prints the Microsoft building number above each building.

 

We have also used thematic formatting to color the buildings automatically by whether they have a cafeteria or not. Green indicates a cafeteria. Thematic formatting and labels took, literally, less than a minute to do.

 

Many, many other Manifold facilities are open to us: we can create a web site in seconds that provides dynamic access to the SQL Server data, or we could publish the contents as a PDF. Although this topic uses many illustrations, it can be done by a semi-experienced Manifold user much faster than the time required to read through it. An experienced person could create this project in just a few minutes.

 

What is remarkable is that bringing together technologies like Virtual Earth and SQL Server and Manifold together makes it extremely easy to do what used to be very difficult and error-prone.

 

images\eg_redmond_57.gif

 

Consider, for example, the illustration above from the Microsoft web site which shows inaccurately the position and orientation of Building 36. In just a few short minutes we have created a more accurate rendition. Although we did not take great care to draw areas accurately, Manifold has many facilities (such as scrolling in dynamically while creating a new object) that make it fast and easy to draw accurately.

 

See Also

 

SQL Server Spatial DBMS Facilities

Example: Configuring SQL Server 2008

Example: Linking a Drawing from SQL Server 2008

Example: Storing a Drawing in Manifold Spatial DBMS

Example: Storing a Drawing in SQL Server 2008

Example: Tracing Virtual Earth into SQL Server 2008

Example: Storing an Image in SQL Server 2008

 

Data Source Dialog

Example: Storing a Drawing in Manifold Spatial DBMS

Example: Storing a Drawing in Oracle

Manifold Spatial DBMS Facilities

Multi-User Editing of Linked Drawings

Spatial DBMS Facilities

Tools - Database Console