Define a current layer extent's WKT string for use in Data statement.

[panzel]

Some queries, such as for annotating only the "top N" instances of a feature by displayed layer, require the layer's current extent in order to properly winnow the results of a layer's Data statement.

I propose that a string token, such as "{extentWKT}", be populated with the relevant layer's map extent (for example, the literal string "POLYGON((-71 42,-69 42,-69.99 43,-71 43,-71 42))") when the Data statement is expanded and passed to the underlying spatial database.

Consider this Data statement:

Data "shapeGeometry from

(select * from GetRankedStateLabels?('{extentWKT}', 1)) as foo

using unique objectId using SRID=4326"

In this instance (I'm using MS Sql Server 2008 as an example), the "User Defined Function" (shown below) accepts the layer's extent window as a polygon WKT string and a number of unique labels to be shown for each state, and returns the geometry column, the "labelitem" column, and other columns as needed.

Here's the sample stored procedure:

create function GetRankedStateLabels
  (
      @extentWKT as varchar(max),
      @labelCountLimit int
  )
returns @rankedResults TABLE 
(
   ShapeGeometry geometry,
   OBJECTID   int,
   STATE_ABBR char(2),
   G_Area     float,
   Area_Rank  int
)
as begin
  declare @extent geometry;
  set @extent = geometry::STGeomFromText(@extentWKT,4326);
  insert @rankedResults
  select ShapeGeometry, Areas.* 
  from 
    (Select 
       OBJECTID, STATE_ABBR, 
       ShapeGeometry.STIntersection(@extent).STArea() as G_Area, 
       rank() over 
          (partition by STATE_ABBR 
           order by sum(ShapeGeometry.STIntersection(@extent).STArea()) desc) 
          as Area_Rank 
       from state_table
       where 
        ShapeGeometry.STIntersects(@extent) = 1 
       group by 
        OBJECTID, STATE_ABBR, 
        ShapeGeometry.STIntersection(@extent).STArea()
      ) Areas,
      state_table as T
   where Areas.Area_Rank <= @labelCountLimit
   and   T.ObjectID = Areas.ObjectID
   
   return;
end;

Note that the function performs its ranking over the size of each displayed polygon, where the size is determined after clipping to the specified layer extent.

Is there a better way to do this?

[sdlime]

Marking as "won't fix" for the moment... Not that it's a terrible idea.

Steve