Dynamic Content as WFS

There could be a number of reasons why you'd like to use a database view as a backend for a WFS. Perhaps your data is split across multiple tables and updated regularly. Perhaps the data is in a table without an explicit ESRI-style SHAPE field. That's the case in this example, where the geometry information is stored in a table as UTM coordinates.

Starting point: A table with columns representing XY coordinates of points in ArcSDE on SQL Server 2008 (you need GEOMETRY support which didn't exist in 2005).

The goal is to build a view that contains:
- a unique integer field that ArcGIS can use like an ObjectID
- a GEOMETRY field that ArcGIS can read
- all the other attributes you want associated with each point
... and then register that view as a featureclass with ArcSDE.

So first, the SQL behind the view. My example uses a table called StructureObservation, with UTME and UTMN columns:

                  geometry::STPointFromText('POINT(' + STR(UTME) + '  ' + STR(UTMN) + ')', 29612) AS Shape,

  • The first column being selected is my surrogate objectid. I used the row_number() function to generate these on the fly, but it made a bigint datatype that I had to convert to int.
  • The second column is my geometry. Essentially I'm doing two things.
    • First, I'm writing a WKT string representation of the geometry of a point 'POINT(' + STR(UTME) + '  ' + STR(UTMN) + ')'. When the query runs, I end up with strings like "POINT(315887 3748889)"
    • Then I'm using the geometry::STPointFromText function to create a point. The first argument in that function is the WKT string, the second is the EPSG code for the spatial reference system being used by the coordinates. In this case the code is for NAD83 UTM zone 12.
  • After that, its just attributes, conditions. joins, whatever else you need to define your query.
Once the view is constructed and saved, I log into the server running SDE and open a command prompt. My command looked like this:
sdelayer -o register -l mock_event_theme,Shape -e p -C OID -t GEOMETRY -R 16 -i sde:sqlserver:malachite\azgsgeodatabases -s malachite\azgsgeodatabases -D AZGeology

So, the breakdown:
  • sdelayer is an ArcSDE function used to administer featureclasses.
  • -o register defines the operation we wish to run. We want to register a layer/featurclass.
  • -l mock_event_theme,Shape defines the name of the view that we want to turn into a featureclass, and the name of the column in that view that contains the feature geometries.
  • -e p defines an "entity mask" which (as I understand it) is kind of like specifying the type of feature (point, line, polygon, etc.) p is for point.
  • -C OID defines the field to be used as a surrogate ObjectID. If you don't have this, you won't be able to select features in the resulting featureclass.
  • -t GEOMETRY defines the geometry storage type in SQL Server.
  • -R 16 defines the spatial reference system to use. I found the right number in the database's SDE_spatial_references table.
  • -i sde:sqlserver:malachite\azgsgeodatabases defines the service used to connect to the ArcSDE database. We don't use SDE services, and instead opt for direct connections.
  • -s malachite\azgsgeodatabases defines the name of the SQL Server instance to connect to.
  • -D AZGeology defines the database name.

If it succeeds you'll get a message: "Successfully Created Layer." After that you should be able to connect to the database in ArcCatalog and see the new featureclass - called MOCK_EVENT_THEME in this case.

The last thing you need to do is right-click on the featureclass and select "Register with Geodatabase". After that you're free to use the "fake" featureclass as a layer in a WFS.

Note that you will not be able to edit the featureclass. Data changes that adjust the view's content will be reflected immediately in the featureclass. I would be cautious of changing the query in any way that would add, remove or rename columns. I bet that would cause problems.