spatial or n dimensional search in a relational world l.
Skip this Video
Loading SlideShow in 5 Seconds..
Spatial (or N-Dimensional) Search in a Relational World PowerPoint Presentation
Download Presentation
Spatial (or N-Dimensional) Search in a Relational World

Loading in 2 Seconds...

play fullscreen
1 / 10

Spatial (or N-Dimensional) Search in a Relational World - PowerPoint PPT Presentation

  • Uploaded on

Spatial (or N-Dimensional) Search in a Relational World. Jim Gray. y. x=c/a. x. ax + by = c. y=c/b. y. x. Equations Define Subspaces. For (x,y) above the line ax+by > c Reverse the space by -ax + -by > -c

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Spatial (or N-Dimensional) Search in a Relational World' - tavi

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
equations define subspaces




ax + by = c




Equations Define Subspaces
  • For (x,y) above the lineax+by > c
  • Reverse the space by-ax + -by > -c
  • Intersect a 3 volumes: a1x + b1y > c1a2x + b2y > c2a3x + b3y > c3
domain is union of convex hulls
Domain is Union of Convex Hulls

Not a convex hull

  • Simple volumes are unions of convex hulls.
  • Higher order curves also work
  • Complex volumes have holes and their holes have holes. (that is harder).


now in relational terms
Now in Relational Terms

create table HalfSpace (

domainID int not null -- domain name

foreign key references Domain(domainID),

convexID int not null, -- grouping a set of ½ spaces

halfSpaceID int identity(), -- a particular ½ space

x float not null, -- the (a,b,..) parameters

y float not null, -- defining the ½ space

z float not null,

c float not null, -- the constant (“c” above)

primary key (domainID, convexID, halfSpaceID)

(x,y,z) inside a convex if it is inside all lines of the convex

(x,y,z) inside a convex if it is NOT OUTSIDE ANY line of the convex

select convexID -- return the convex hulls

from HalfSpace -- from the constraints

where @x * x + @y * y + @x * z < l -- point outside the line?

group by all convexID -- consider all the lines of a convexID

having count(*) = 0 -- count outside == 0

the algebra is simple boolean
The Algebra is Simple (Boolean)

@domainID = spDomainNew (@type varchar(16), @comment varchar(8000))

@convexID = spDomainNewConvex (@domainID int)

@halfSpaceID = spDomainNewConvexConstraint (@domainID int, @convexID int,

@x float, @y float, @z float, @l float)

@returnCode = spDomainDrop(@domainID)

select * from fDomainsContainPoint(@x float, @y float, @z float)

Once constructed they can be manipulated with the Boolean operations.

@domainID = spDomainOr (@domainID1 int, @domainID2 int,

@type varchar(16), @comment varchar(8000))

@domainID = spDomainAnd (@domainID1 int, @domainID2 int,

@type varchar(16), @comment varchar(8000))

@domainID = spDomainNot (@domainID1 int,

@type varchar(16), @comment varchar(8000))

what no bounding box
What! No Bounding Box?
  • Bounding box limits search.A subset of the convex hulls.
  • If query runs at 3M halfspace/sec then no need for bounding box, unless you have more than 10,000 lines.
  • But, if you have a lot of half-spaces then bounding box is good.
a different problem
A Different Problem
  • Table-valued function find points near a point
    • Select * from fGetNearbyEq(ra,dec,r)
  • Use Hierarchical Triangular Mesh
    • Space filling curve, bounding triangles…
    • Standard approach
  • 13 ms/call… So 70 objects/second.
  • Too slow, so precompute neighbors: Materialized view.
  • At 70 objects/sec it takes 6 months to compute a billion objects.
zone based spatial join
Zone Based Spatial Join
  • Divide space into zones
  • Key points by Zone, offset(on the sphere this need wrap-around margin.)
  • Point search look in a few zones at a limited offset: ra ± r a bounding box that has 1-π/4 false positives
  • All inside the relational engine
  • Avoids “impedance mismatch”
  • Can “batch” all-all comparisons
  • 33x faster and parallel6 days, not 6 months!







Ra ± x

in sql

select o1.objID -- find objects

from zone o1 -- in the zoned table

where o1.zoneID between -- where zone # floor((@dec-@r)/@zoneHeight) and -- overlaps the circle


and o1.ra between @ra - @r and @ra + @r -- quick filter on ra

and o1.dec between @dec-@r and @dec+@r -- quick filter on dec

and ( (sqrt( power(,2)+power(,2)+power(,2))))

< @r -- careful filter on distance

Bounding box

Eliminates the ~ 21% = 1-π/4

False positives

  • SQL is a set oriented language
  • You can express constraints as rows
  • Then You
    • Can evaluate LOTS of predicates per second
    • Can do set algebra on the predicates.
  • Benefits from SQL parallelism
  • SQL == Prolog? 