December 19, 2008 at 6:14 pm
I got one table of polygons, that I want to lay over top of the results from another layer of polygons....
table example
Table 1
id int
topPolygon geometry
table 2
id int
bottomPolygon geometry
December 19, 2008 at 6:41 pm
Couple of questions:
1. Have you created spatial indexes for the two tables yet?
2. What do you mean by "lay over"? SQL Server can tell you if two shapes touch or not, but if you want to display them overlayed, that is a matter for the user interface, not the database.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 19, 2008 at 7:57 pm
Haven't created any spatial indexes yet.
Maybe I don't need to overlay them now th I think about it.
For example, say I have 100 X 100 grid, with 10X10 polygons so we have 100 polygons in Table 1
In table 2 I store 5 other polygons of varying sizes that would plot to a 100X100 area also.
Say Polygon A, B, C, D, and E
Then I want to plot a line from Polygon A to Polygon E
I want to make sure I don't intersect Polygon B, C, Or D
And I also want to know all the polygons in Table 1 my line would intersect.
Would I have to do two queries?
DECLARE @Myline geometry
@Myline = geometry::STGeomFromText('LINE(( "Buncha line points")),0)
SELECT PolygonGeom
FROM Table 2
WHERE PolygonGeom.STIntersects(@MyLine) = 1
"ResultSet 1"
SELECT PolygonGridGeom
FROM Table 1
WHERE PolygonGridGeom.STIntersects(@MyLine) = 1
"ResultSet 2"
Or is there a way I could join both tables, and get both results at same time?
Hope this make sense, my syntax may be off, I typed those queries freehand
December 19, 2008 at 9:26 pm
I don't think you need two tables. You can store all the polygons in a single table. If we're just talking 105 rows here, that's a trivial amount. If you want to, add a type column with G for grid and P for other polygons. Something like this (forgive me Seth for I am about to sin).
ID Name Type Coord
1 Grid1 G (whatever)
2 Grid2 G ...
... ..etc G ...
99 Grid99 G ...
100 Grid100 G ...
101 A P ...
102 B P ...
103 C P ...
104 D P ...
105 E P ...
Okay... you're going to plot a line from one polygon to another. I assume you already have a scheme to determine where in the polygons your line will start and end (centers? nearest corner?) but we can safely assume that your line will touch at least those two polygons, plus whatever grid squares the line crosses.
If you store all polygons in a single table, your single intersects query using @myline will return the set of all grid squares and other polygons the line intersects. If it only returns two 'P' types (A and E) then you have a winner. If not, you will get the other 'P' polygons as well. If your table has an identifier column [ID] you can always add "and [ID] not in (101, 105)" to your WHERE clause, so that you get no 'P' rows returned unless the line intersects one of the other three polygons B-D. (You may WANT to be sure that A and E are returned, or else your line isn't starting and ending corrctly. The point is, you have a traditional result set upon which you can take counts to base your logic.)
Please share with me what your purpose is in defining the grid? Remember that four grid squares can share one common point: the corner where they meet. If your line happens to pass through a corner, I believe it will return all four grids in the result set. Just something to plan for 😉
Finally, when you build your spatial index, you probably want to ensure that at least one level is the equivalent of your grid squares. I believe it will speed up performance, since the bulk of your polygons are grid squares.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 22, 2008 at 12:25 pm
thanks, I think your right about sticking them in one table...I'm just so use to normalizing stuff out that I always try to put different things in different tables.
The point of the grid is I'm building a game, and part of the mechanics of the game will be area based, so there will be set areas that people can perform certain actions in. This 10X10 grid will be reused 100 times over, with different polygons placed on top of it. But we'll always want the grid behind it for the purpose of querying players, objects, and other things that are in a given area.
And so far it seems like it's going to work perfectly, since we'll be able to track when two players interecept each other, when and where. How far there traveling...even a waypoint system will be very easy.
We'll be able to organize all players that are in a certain area...things like that.
the features of spatial data in sql server 2008 is basically saving us from having to write all this stuff ourselves. At least, that's the goal, we haven't of course tested it's efficency...time will tell.
December 22, 2008 at 1:29 pm
If you REALLY want the grid in a separate table you could always do two queries with a UNION ALL, but why bother?
The problem is that SQL doesn't currently allow joins between tables based on spatial indexes, at least not for the moment. Since this is a game, I assume you are working with geometry data types and not geography. I did some reading this weekend, and geometry apparently performs slightly better than geography because there is no curvature of the earth involved.
Once you get around to building a spatial index, you're going to have to keep an eye on the execution plans to make sure that it is being used. (You may need to apply a hint.) Otherwise you may be doing a table scan every time you do a spatial query.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 22, 2008 at 1:38 pm
ok, that's definately stuff I want to keep an eye on.
at first I'd say most intense queries would be intercept checks, and that would return on average between 5 and 50 rows.
But there is always the "who knows what might happen", and this game could actually take off and we could potentiall need to return "many rows".
We've built into our code the ability to split databases easily if load gets too bad, but implementing good efficency can surely save on hardware costs.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply