Top 5 Recent Articles
- 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
|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.