## Top 5 Recent Articles

##### ARTICLES CATEGORIES

- Algorithms (13)
- All (407)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (5)
- Image Processing (2)
- Import Export (5)
- Licensing (2)
- Linear Referencing (3)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (3)
- Open Source (16)
- Oracle Spatial and Locator (178)
- PostGIS (33)
- Published Articles (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (101)
- Spatial DB comparison (1)
- Spatial XML Processing (10)
- SQL Server Spatial (General) (83)
- SQL Server Spatial (LRS) (38)
- Standards (1)
- Stored Procedure (15)
- Tessellation or Gridding (9)
- Tools (2)
- Training (2)

# 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

point_type | col | row |
---|---|---|

Inside a Cell | 2 | 2 |

Corner 4 Cells | 1 | 1 |

Boundary 2 Cells | 2 | 2 |

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.

## Documentation

- MySQL Spatial General Function Documentation
- Oracle Spatial Exporter Package Documentation
- Oracle Spatial Object Function Documentation
- Oracle Spatial Object Function Documentation (Multi Page Version)
- PostGIS pl/pgSQL Function Documentation
- SC4O Oracle Java Topology Suite (Stored Procedures) Package Documentation
- SQL Server Spatial General TSQL Function Documentation
- SQL Server Spatial LRS TSQL Function Documentation