2

Although I will illustrate my problem using a database, I think this is a quite general GIS question. I hope I came to the right place to ask.

I am working with a system that runs on a normal relational database (without any SQL/MM functionality) and there is sadly no hope I can upgrade it anytime soon. But I need to maintain some basic geographic relationships regarding intersections of lines (Set A) and polygons (Set B).

Users may change individual lines and polygons and expect to get a list afterwards, regarding those that will somehow intersect. Now the exact intersection test isn't a problem and is dealt with in a Java backend. My current problem is that I have to check the cross product of A and B anytime someone edits anything because the change could introduce a new intersection. No matter how small the change is, I can't tell in advance which exact relations could be affected.

So I would like to introduce some bounding boxes in the database, which hopefully will speed up the whole application because many obviously not related entities can be filtered out early.

I came up with the following structure and query to demonstrate the general idea:

CREATE TABLE bound_rect
(
  id serial NOT NULL,
  x1 double precision,
  y1 double precision,
  x2 double precision,
  y2 double precision,
  CONSTRAINT bound_rect_pkey PRIMARY KEY (id)
)

SELECT *
FROM bound_rect lhs, bound_rect rhs
WHERE lhs.x1 < rhs.x2
AND   lhs.x2 < rhs.x1
AND   lhs.y1 < rhs.y2
AND   lhs.y2 < rhs.y1

So this query is able to do some very general bounding box intersection tests (I am aware that things like the dateline are not taken into account, I can live with that). For me this seems like a similar calculation as it would be done in PostGIS' geometry mode. Performance is much improved, I am happy so far.

Now my actual question: Would I need to possibly "inflate" my bounding box if I want to make sure I don't get any "false negatives" when dealing with lat/lng coordinates on a sphere? I can live with "false positives", e.g. cases where the bounding boxes overlap but not the "real" entities. But if my query would filter out entities that do overlap because of too small bounding boxes, that would be a real problem.

Marcus Riemer
  • 121
  • 4
  • Can you handle formulas similar to those found in similar threads at http://gis.stackexchange.com/questions/18584/how-to-find-a-point-half-way-between-two-other-points/18740#18740 and http://gis.stackexchange.com/questions/52949/great-circle-lines-in-equirectangular-projection? – whuber Aug 14 '13 at 15:46
  • Depends on what you understand as "handling". I was somewhat hoping my problem isn't something exactly uncommon and somebody could point me out to some paper or book discussing the issue. I had my share of calculus and linear algebra at university. – Marcus Riemer Aug 14 '13 at 18:18
  • I should have said "your software" instead of "you." It sounds like it will be no problem. – whuber Aug 14 '13 at 18:42

0 Answers0