How To Create an ArcSDE Spatial View With an Outer Join

by James Richards July 16, 2009

Overview

ArcSDE Spatial Views are a useful tool for organizing information from multiple feature classes and geodatabase tables into a single “virtual feature class” or table at the database level. Similar to a database view, they allow administrators to join commonly used information so that the users do not need to perform the same actions repeatedly in a client (in this case, ArcMap).

Spatial Views are created with the “sdetable –o create_view …” command. When creating a Spatial View in this manner, the default join type is INNER JOIN. This fact cannot be altered via the command line syntax. INNER JOIN is the most restrictive join type and only records that match on both sides of the join will be in the resulting view. But once a default INNER JOIN Spatial View has been created, the join type can be changed after the fact either with the ALTER VIEW SQL statement, or through a database administration tool.

This article demonstrates how to create a Spatial View and then change the join type using SQL Server Management Studio. The same principles can be applied to other databases using whatever management tool you have available. [more]

Discussion

Step 1: Create the Spatial View

To create the Spatial View, use the sdetable –o create_view command. The syntax for this command is:

sdetable -o create_view -T <view_name> -t <table1,table2...tablen>
-c <table_col1,table_col2...table_coln>
[-a <view_col1,view_col2...view_coln>] [-w <"where_clause">]
[-i <service>] [-s <server_name>] [-D <database>]
-u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

The important parameters that we will be working with in this example are:

  • -T : The name of the view to create
  • -t : The list of tables to include in the view
  • -c : The list of columns to include in the view
  • -a : The list of field alias as they will appear in the view
  • -w : A where clause to define the view
  • -u : The name of a user with ArcSDE administration privileges
  • -p : The password for the aforementioned user

Here’s an example that joins a geodatabase table to a feature class on a common ID field called Join_ID:

sdetable –o create_view –T MyView –t “MyLayer, MyTable”
-c “MyLayer.OBJECTID, MyLayer.Shape, MyLayer.Join_ID, MyTable.Join_ID, MyTable.Field1”
-a “OBJECTID, Shape, Layer_Join_ID, Table_Join_ID, Table_Field1”
-w “MyLayer.Join_ID = MyTable.Join_ID”
-u ****** –p ******

One thing that’s a little odd about this syntax is that the join information is given in the WHERE clause. In a typical SQL CREATE VIEW statement, the join information would be found in the FROM clause.

Also note that if you want the resulting view to be a virtual feature class that can be used with ArcGIS Desktop, you will need to include the OBJECTID and Shape fields from the original feature class in the list of columns to include.

For reference, the sample data in MyLayer and MyTable looks like so:

image

Notice that there is no record with a Join_ID of 5 in MyTable, and no record with a Join_ID of 7 in MyLayer.

Step 2: Modify the View Definition in SQL Server Management Studio

As was mentioned in the overview, the default behavior of “sdetable –o create_view” creates a view with an INNER JOIN. If we add the newly created view to ArcMap, only the 5 matching records are shown in the view:

image

To modify the view definition so that it uses an outer join, open SQL Server Management Studio and drill down to the Views folder under the sde database. Next, right click on the view you just created and choose Modify.

 image

Change the phrase “INNER JOIN” in the FROM clause to “LEFT OUTER JOIN” and save the changes.

image

Now if we look at the View in ArcMap again, we will see all records from MyLayer, with null values for any records with a Join_ID in MyLayer that was unmatched in MyTable:

image

You can use other join styles such as FULL OUTER JOIN, but keep in mind that if the primary OBJECTID (or Row ID) column contains multiple null values, client behavior will become unpredictable.

Wrap Up

When creating ArcSDE Spatial Views using the “sdetable –o create_view” command, the default join type is the most restrictive INNER JOIN. But you can change the view definition to use other join types using the ALTER VIEW SQL command or your database’s management tool. The article showed an example of how to make such a change with SQL Server Management Studio.

I hope you find this helpful!

Resources

 

Tags: ,

ArcSDE | How To | Planet GS

How To: Display an ArcGIS Server Cached Tile Layer as a Custom Map Type with the Google Maps API for Flash

by James Richards July 06, 2009

Overview

This is the third post in a series where I discuss techniques for interacting with the ArcGIS Server REST API from within a Flex 3 application built with the Google Maps API for Flash. The first and second posts presented and refined an example that demonstrated how to stream features from ArcGIS Server and overlay them on top of Google Maps data. This post demonstrates how to display an ArcGIS Server cached tile layer as a custom map type with the Google Maps API for Flash.

Sample Application Concepts

The sample application works as follows:

  • The Google Maps API for Flash is embedded in a Flex 3 application.
  • A cached tile layer representing Land Base features for Portland, Oregon is served via an ArcGIS Server Map Service, accessible via ArcGIS Server’s REST API.
  • The cached tile layer appears as a custom map type in the Google Maps API for Flash User Interface.
  • The cached tile layer is displayed at zoom levels 0 - 19.

Here are a couple of screen shots of the sample application. The first shows the application with the custom “Land Base” map type selected. The second shows the application with the normal map type selected.

image

image

Live example is here, and source code is here. [more]

Discussion

MXML File

Starting with a basic Flex Application, we have a single map with a specified “map ready” event:

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
    xmlns:gm="com.google.maps.*"
    backgroundGradientColors="[0xFFFFFF, 0xAAAAAA]"
    height="100%" width="100%" viewSourceURL="srcview/index.html">
    <gm:Map id="map"
        width="100%"
        height="100%"
        key="(Your API Key)"
        mapevent_mapready="map_mapReady(event)" />
    <mx:Script>
        // Omitted for clarity
    </mx:Script>
</mx:Application>

In the map_mapReady function we create an instance of the custom tile layer, specifying the URL endpoint of the cached tiles, the minimum and maximum zoom levels, and the copyright text. Then we create a new map type using the custom tile layer instance. Next we add the usual map controls, and finally we initialize the map using the new map type.

private function map_mapReady(event:Event):void {
    // Create custom tile layer
    var baseUrl:String = http://sampleserver1.arcgisonline.com/ +
        "ArcGIS/rest/services/Portland/ESRI_LandBase_WebMercator/MapServer";
    var copyright:String = "Taxlots, Zoning © Oregon METRO RLIS; " +
        "Buildings © City of Portland Planning"; 
    var tileLayer:TileLayer = new TileLayer(baseUrl, 0, 19, copyright);

    // Create custom map type and add to map
    var mapType:IMapType = new MapType([tileLayer], map.MERCATOR_PROJECTION, "Land Base");
    map.addMapType(mapType);

    // Add map controls
    var mtc:MapTypeControl = new MapTypeControl();
    map.addControl(mtc);
    var pc:PositionControl = new PositionControl();
    map.addControl(pc);
    var zc:ZoomControl = new ZoomControl();
    map.addControl(zc);
    var sc:ScaleControl = new ScaleControl();
    map.addControl(sc);
    var omc:OverviewMapControl = new OverviewMapControl();
    map.addControl(omc);

    // Initialize map. Use custom tile layer for the initial map type           
    map.setCenter(new LatLng(45.520211, -122.65553), 17, mapType);
}

The custom tile layer is implemented with the reusable TileLayer class and its companion Tile utility class. The basic design for these classes comes from Pamela Fox’s custom tile layer sample in the Google Maps API for Flash Demo Gallery.

The TileLayer constructor takes four arguments: the URL endpoint of the cached tiles, the minimum zoom level, the maximum zoom level, and the copyright text. Our example uses an ESRI sample server that has a Land Base cached tile service for Portland, Oregon. 

We determine the appropriate values for the constructor’s arguments by exploring the MapServer REST API using the interactive SDK . The MapServer is hosted at: http://sampleserver1.arcgisonline.com/arcgis/rest/services

image

By drilling down into the Portland Folder and the associated MapServices, we learn about the functionality and data layers that are available for consumption. If we drill down to the Portland/ESRI_LandBase_WebMercator MapServer, we can gather the information we need to fill in the above mentioned TileLayer constructor.

image

First, we need to check to make sure that the MapServer is cached. Look at the Single Fused Map Cache property and make sure it is set to true. Next, verify that the MapServer uses the Google Maps (and Virtual Earth) tiling scheme by making sure the Spatial Reference is set to 102113 (Web Mercator), the Width and Height are 256 pixels, and the DPI is 96.

If you are designing your own map cache to overlay on top of Google Maps, be sure to read the following information in the ESRI online documentation (depending on your platform):

To determine the base URL constructor argument, look at the URL in the browser’s address bar. The URL should end with “MapServer”.

image

To determine the minimum and maximum zoom levels, scroll down and look at the Tile Info - Levels of Detail section. The first Level ID in the list is the minimum zoom level (in this case 0) and the last Level ID is the maximum zoom level (in this case 19).

image

Finally, the copyright parameter can be anything you deem appropriate. In this case, I abbreviated the information found in the Copyright Text property of the MapServer.

TileLayer Class Implementation

Moving on to the TileLayer class implementation, here is the code:

// Custom tile layer for displaying ArcGIS Server cached tiles
// in a Google Maps API for Flash application.
public class TileLayer extends TileLayerBase
{
    private var baseUrl:String;
    private var minResolution:Number;
    private var maxResolution:Number;

    // Constructor
    public function TileLayer(baseUrl:String, minResolution:Number,
                          maxResolution:Number, copyright:String) {
        // Save the options
        this.baseUrl = baseUrl;
        this.minResolution = minResolution;
        this.maxResolution = maxResolution;

        // Add copyright information.
        var copyrightCollection:CopyrightCollection = new CopyrightCollection();
        copyrightCollection.addCopyright(new Copyright("TileLayer",
            new LatLngBounds(new LatLng(-180, 90), new LatLng(180, -90)),
            0, copyright));
        super(copyrightCollection);
    }

    // Return the coarsest zoom level. Provides information necessary
    // to adjust the boundaries of the zoom level control when this 
    // layer is included in a custom map type.
    public override function getMinResolution():Number {
        return minResolution;
    }

    // Return the finest zoom level. Provides information necessary to
    // adjust the boundaries of the zoom level control when this layer
    // is included in a custom map type.
    public override function getMaxResolution():Number {
        return maxResolution;
    }

    // Creates and loads a tile (x, y) at the given zoom level.
    public override function loadTile(tilePos:Point, zoom:Number):DisplayObject {
        return new Tile(baseUrl, tilePos, zoom);
    }       
}

As you can see, the class is pretty simple. It extends the Google Maps API’s TileLayerBase abstract base class, which does most of the work. According to the Google Maps API for Flash Documentation, sub-classes must override the loadTile() method; however, overriding the other methods is optional. The loadTile() method must return a DisplayObject that represents the tile.

Tile Class Implementation

In the loadTile() method implementation, we defer to the Tile utility class, which is defined as follows:

// Custom tile for displaying ArcGIS Server cached tiles
// in a Google Maps API for Flash application.
public class Tile extends Sprite
{
    // Constructor
    public function Tile(baseUrl:String, tilePos: Point, zoom:Number)
    {
        // Construct the url to the ArcGIS Server tile:
        // http://<path to map service>/tile/<zoom level>/<y>/<x>
        var tileUrl:String = baseUrl + "/tile/" + zoom.toString() + "/" +
            tilePos.y.toString() + "/" + tilePos.x.toString();
        trace(tileUrl);

        // Load the tile and add it to the sprite as a child
        var loader:Loader = new Loader();
        loader.contentLoaderInfo.addEventListener(IOErrorEvent.IO_ERROR, ioErrorHandler);
        loader.load(new URLRequest(tileUrl));
        addChild(loader);
    }

    // Error handler when a tile cannot be loaded by the loader.
    public function ioErrorHandler(event:IOErrorEvent):void {
        // If the tile was not found, just load an empty sprite
        // so nothing draws.
        addChild(new Sprite());
    }
}

The Tile class extends Sprite, which in turn inherits from DisplayObject. Therefore, we can return an instance of the Tile class from the loadTile() method in the TileLayer class. The Tile class’s constructor builds a URL to the tile as served by the ArcGIS Server REST API. The URL pattern for accessing tiles through the REST API is: http://<path to map service>/tile/<zoom level>/<y>/<x>

Once the URL string is created, a flash Loader is used in concert with a URLRequest object to load the tile image and add it as a child to the Sprite. If the tile is not found and an error is thrown, we simply add an empty Sprite instead so nothing draws. Alternatively, we could load some kind of "tile not found" graphic, as shown by Pamela Fox’s custom tile layer sample in the Google Maps API for Flash Demo Gallery.

Wrap Up

This post has demonstrated how to display an ArcGIS Server cached tile layer as a custom map type with the Google Maps API for Flash. The TileLayer and Tile classes are reusable. To add an ArcGIS Server cached tile layer to your project, simply copy the classes as-is and modify the code shown in the map_mapReady event to use your cached MapServer.

Live example is here, and source code is here.

Resources

Downloads
Documentation and Sample Servers
Blogs, Forums and Code Galleries

Tags: , , ,

ArcGIS Server | Flex 3 | Google Maps | How To | Planet GS | REST

ArcGIS Server WebADF: Adjusting the Zoom Scale for Find Address Task Results

by James Richards July 02, 2009

Overview

When working with the Find Address Task in the ArcGIS Server WebADF, the default zoom scale that is displayed when the user zooms to a found address might not be what you want. This article discusses how to change it using the ZoomToPointFactor property. [more]

Background

Let’s assume that you have a WebADF application with some Map Layers and a Find Address Task that is configured to use a Geocoding Service. This could be an application that you created from scratch in Visual Studio, or one that you created with ArcGIS Server Manager.

After entering an address:

image

The results are shown in the Task Results panel. From there you can right click on an address and choose Zoom To:

image

This will zoom the map to an area centered on the address point. The problem is that scale to which the map is zoomed might not be what you want. Often it’s zoomed out too far.

Solution

To adjust the zoom scale to your liking, add the ZoomToPointFactor property to the esri:TaskResults element on the ASP.NET Page hosting the application.

If you created the application with ArcGIS Server Manager using the default options, this would be found in the Default.aspx file in the C:\inetpub\wwwroot\[YourApplicationName] directory.

Open this file and search for the esri:TaskResults tag, then add the ZoomToPointFactor property and give it a numeric value. In the example below, I’ve set the property to “1000”.

image ArcGIS Server will divide the width and height of the map by this value when zooming to a point. For this example, the zoom area will be 1/1000th of the full extent. The larger the ZoomToPointFactor, the further in that the map will zoom. You will need to experiment to find the ratio that works best, as the full extent can vary greatly depending on the data in your application.

Also note that if you have any cached map services in your application, ArcGIS Server will snap to the cache scale that is closest to the calculated map extent. Consequently, you might not see the map extent change if you don’t modify the ZoomToPointFactor property by a large enough value to jump to another scale.

Resources

Tags: , , ,

.NET | ArcGIS Server | How To | Planet GS | WebADF

Powered by BlogEngine.NET 1.6.0.0
Theme by Mads Kristensen | Modified by Mooglegiant
Creative Commons License This work is licensed under a Creative Commons Attribution 3.0 United States License.

Welcome

James Richards

Hi, I'm James Richards the CTO and co-founder of Artisan Global LLC. We make location-aware mobile apps with maps. I'm the author of QuakeFeed and I helped launch Zaarly at LASW Feb 2011. I also enjoy surfing, snowboarding, golfing, yoga, and music. I love my family: Linda, Sequoya and our cats Remy and Twiggy. Thanks for stopping by, I hope you find something helpful here.

Subscribe by RSS   Follow me on Twitter   Connect on Facebook   View my profile on LinkedIn


Amazon Associates

Some of my posts may contain Amazon Associates links. I only include these links when it makes sense within the context of the article. If you are in the market for one of these items, please consider clicking on an affiliate link to make your purchase. You still get the same great deal from Amazon and it will help me pay for hosting and bandwidth. Thanks!