Geocoding Points Against a Grid of Cells/Tiles With/out Spatial Operators

Point In Polygon Using SQL Server Spatial

Now let’s test the three points against the sample grid using different SQL Server Spatial operators, to chose how

/* Inside a Single Cell */ 
select 'Inside' as test
  from dbo.sttileXY(0,0,4,4,1,1,0,0,0,0) as t
  where t.geom.STContains(geometry::Point(2.5,2.5,0)) = 1
Go

test
------
Inside

(1 row affected)

/* On Corner Of 4 Cells -- Contains test will fail as is on corner of four cells ie end of fours boundary lines */ 
select 'Inside' as test
  from dbo.sttileXY(0,0,4,4,1,1,0,0,0,0) as t
 where t.geom.STContains(geometry::Point(1,1,0)) = 1
Go

test
------

(0 rows affected)

/* On Corner Of 4 Cells -- Only Intersects will return the cells */
select 'Corner: ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as varchar(1)) as test
  from dbo.sttileXY(0,0,4,4,1,1,0,0,0,0) as t
 where t.geom.STIntersects(geometry::Point(1,1,0)) = 1
Go

test
---------
Corner: 1
Corner: 2
Corner: 3
Corner: 4

(4 rows affected)

-- Note that the number of cells is a problem because we expect only a single cell reference (see later).

/* On Boundary of 2 Adjoining Cells - Boundary Contains will fail. Just execute STIntersects */
select 'Boundary: ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as varchar(1)) as test
  from dbo.sttileXY(0,0,4,4,1,1,0,0,0,0) as t
 where t.geom.STIntersects(geometry::Point(2,1.5,0)) = 1;
GO

test
-----------
Boundary: 1
Boundary: 2

(2 rows affected)

As we can see, while ever the point lies wholly within a single cell a single reference can be computed. But where the point lies on a boundary, two queries would be needed; first to see if STContains passes; if not STIntersects (with TOP 1) is needed.

Alternate Method for Point in Polygon

The normal method for discovering what grid cell (polygon) a point falls in, is, as shown, the STContains or STIntersects operator.

Is there another way that avoids calling the two spatial functions?

Yes, there is.

We can use the same metadata that defined the grid, to determine the col/row references of the cell the point falls in.

The function, STTileColRowRef4GeomPoint, given a geometric point and the target grid’s metadata, returns its grid column and row reference in that grid. Here’s its definition.

/*  NAME
*    STTileColRowRef4GeomPoint - Returns required grid column and row references in which the geometric point falls.
*  SYNOPSIS
*    Function STTileColRowRef4GeomPoint (
*      @p_point     geometry,
*      @p_origin    geometry,
*      @p_tile_size float,
*      @p_rAngle    float = 0.0
*     )
*     Returns @results table ( col integer, row integer )
*  DESCRIPTION
*    This is a function that computes the col/row reference of a cell in an existing set of tiles.
*    @p_origin is the origin of the grid as created by the tiling TSQL functions.
*    The input tile sizes must be in the units of the SRID.
*    Since the function is a scalar function it only returns a single value ie the 
*    col reference (@p_side = 'C') or the row (@p_side = 'R') reference.
*    Normally the grid metadata refers to an grid that is perfectly organised to the N/E axes.
*    If not, the calculation needs to take into account the rotation of the grid.
*  INPUTS
*    @p_point  geometry -- Point which lies in a grid of size @p_tileX
*    @p_origin geometry -- Point of LL point of grid
*    @p_tile_x    float -- Grid cell size for X direction.
*    @p_tile_y    float -- Grid cell size for Y direction
*    @p_rAngle    float -- Optional rotation angle 
*  RESULT
*    Col,Row (integer,integer) - The grid cell reference from right to left; or bottom to top as table 
*/

We’ll see how this works by generating the row/column values of the three points above.

with points as (
  select 'Inside a Cell'    as point_type, geometry::Point(2.5,2.5,0) as geom
  union all
  select 'Corner 4 Cells'   as test, geometry::Point(1,1,0) as geom
  union all
  select 'Boundary 2 Cells' as test, geometry::Point(2,1.5,0) as geom
)
select point_type, ref.col, ref.row
  from points as p
       cross apply
	   [dbo].[STTileColRowRef4GeomPoint] (
             /* @p_point  */ p.geom,
             /* @p_origin */ geometry::Point(0,0,0),
             /* @p_TileX  */ 1.0,
             /* @p_TileY  */ 1.0,
             /* @p_rangle */ 0.0
       ) as ref
Go  

See how in the boundary cases the function has selected the Left- and Down-most cell.

Better still, every point is encoded: nothing is missing!

It will be shown soon that if the grid is rotated, the functions still compute the correct grid reference.