SQL View for top left and top right cell

Question

Referring to the output shown as best solution there, how can I get the boundary cells? That is min(StartX), min(StartY) and max(EndX) and max(EndY) OR in certain cases max(EndX+1) or max(EndY+1) if the column or row be missed out as in the case of 3,10 in the image below (green bordered are my bounding cells)

[BoundingBox](http://tinypic.com/r/2moyvjo/6)

X     Y           PieceCells Boundary
1     1            (1,1)(2,1)(2,2)(3,2) (1,1)(3,2)
8     1            (10,1)(8,1)(8,2)(9,1)(9,2)(9,3) (8,1)(10,1)

Well I want like this:
BoundaryStartX, BoundaryStartY, BoundaryEndX, BoundaryEndY
1 1 3 2
8 1 10 3

Is there a reason you're doing this in SQL? It seems to me a task much better suited for other languages — jdotjdot, Dec 25 '12 at 22:59
yes the reason to check how it would perform if not done in other programming language — Ziad, Dec 27 '12 at 18:29

Ben Thul · Answer 1 · 2012-12-28T15:18:21.893

0

I was able to do this pretty simply with the geometry data type.

declare @g geometry;
set @g = geometry::STGeomFromText(
   'POLYGON( (1 -1, 1 -2, 2 -2, 2 -3, 4 -3, 4 -2, 3 -2, 3 -1, 1 -1) )'
   , 0);

select @g, @g.STEnvelope();

Geometry is available starting in SQL2008. Also note that I converted your coordinate system to standard Cartesian (positive x axis to the right of the origin, negative y axis below); you'd do well to consider doing the same.

edited Dec 28 '12 at 15:18

answered Dec 26 '12 at 03:07

Ben Thul

31,080
4
45
68

Why do I get this error? "Msg 243, Level 16, State 4, Line 3 Type gemoetry is not a defined system type." I just copied and pasted, any other changes needed? Thanks – Ziad Dec 27 '12 at 13:16
As I said, the geometry data type is available starting in SQL2008. If it's not available on your server, that means that you're running SQL2005 at most. – Ben Thul Dec 27 '12 at 18:47
I am using SQLServer2012 version.. so it should work, but I still get the error. – Ziad Dec 28 '12 at 14:44
Wait... I see that I mis-spelled "geometry" in the second line. That's what I get for doing dev on one machine and the post on another! Regardless, the code has been edited (and tested) now. – Ben Thul Dec 28 '12 at 15:18
Ok, I havenot also noticed that, anyway I get some sort of hexanumbers in two columns, how can I make sense out of it? Is it possible to replace the values of the Polygon using X, Y coordinate values from a SQL Table? Thanks – Ziad Dec 28 '12 at 19:56
You've just done what is called a spatial query. When you ran the query, there should be an extra results tab called "spatial results". It will give you a graphical representation of the geometry type in that column. Now, if you're hellbent on getting coordinates, you can call the STPointN method on the envelope to get the corners of it and STX and STY on *that* to get the X & Y coordinates of the point. – Ben Thul Dec 28 '12 at 21:47
Thanks, but can you suggest something with just using SQL View may be with CTE using a select statement instead? – Ziad Jan 04 '13 at 02:07
because in database I have a table like with columns X,Y, see this thread http://stackoverflow.com/questions/12546369/view-to-identify-grouped-values-or-object – Ziad Jan 04 '13 at 02:08

score 0 · Answer 2 · answered Jan 04 '13 at 04:52

use tempdb;
if exists (select 1 from sys.tables where name = 'grid')
    drop table grid;

if not exists (select 1 from sys.tables where name = 'tally')
begin
    create table tally (i int not null);
    with 
        a as (select 1 as [i] union select 0),
        b as (select 1 as [i] from a as [a1] cross join a as [a2]),
        c as (select 1 as [i] from b as [a1] cross join b as [a2]),
        d as (select 1 as [i] from c as [a1] cross join c as [a2]),
        e as (select 1 as [i] from d as [a1] cross join d as [a2])
    insert into tally
    select row_number() over (order by i) from e
    create unique clustered index [CI_Tally] on tally (i)
end

create table grid (
    x tinyint, 
    y tinyint, 
    cell as geometry::STGeomFromText(
    'POLYGON( (' + 
        cast(x as varchar) + ' ' + cast(-1*y as varchar) + ', ' +
        cast(x+1 as varchar) + ' ' + cast(-1*y as varchar) + ', ' +
        cast(x+1 as varchar) + ' ' + cast(-1*(y+1) as varchar) + ', ' +
        cast(x as varchar) + ' ' + cast(-1*(y+1) as varchar) + ', ' +
        cast(x as varchar) + ' ' + cast(-1*y as varchar) +
        ') )'
        , 0)
);

insert into grid (x, y) 
    values 
    (1,1),
    (2,1),
    (2,2),
    (3,2),

    (8,1),
    (9,1),
    (8,2),
    (9,2),
    (9,3),
    (10,1);

with cte as (
    select cell, row_number() over (order by x, y) as [rn]
    from grid
),
cte2 as (
    select cell, [rn]
    from cte
    where [rn] = 1

    union all

    select a.cell.STUnion(b.cell) as [cell], b.rn
    from cte2 as a
    inner join cte as b
        on a.rn + 1 = b.[rn]
), cte3 as (
    select cell
    from cte2
    where [rn] = (select count(*) from grid)
), clusters as (
    select i, cell.STGeometryN(t.i) as c
    from cte3 as [a]
    cross join tally as [t]
    where t.i <= cell.STNumGeometries()
)
select *, c.STEnvelope() from clusters

This solution solves both your original problem and this one. I like this because you can still use whatever weird coordinate system you want and it'll do what you want. All you'd have to do is modify the computed column on the grid table accordingly. I'm going to leave the computation of the corners of the envelope as an exercise to the reader. :)

By way of explanation, the computed column makes a 1x1 geometry instance out of the given x and y coordinates. From there, I essentially union all of those together which will yield a multipolygon. From there, I iterate through the individual polygons in the multipolygon to get the individual clusters. The envelope comes along for free. From here, you should be able to wrap that final select (or something very like it) in a view if you so choose.

SQL View for top left and top right cell

2 Answers2

Linked