Helo,

I think that It would be useful to have "Spatial index" for rectangle coordinates in IB. Here is what I mean: I use IB for storing GIS objects. That objects can be represented as poly-lines, points, polygons etc. Every object (called feature) has minx, maxx, miny, maxy "envelope" information and related set of points in another (point) table. When user loads rectangle of features on screen (x1,y1,x2,y2) program executes

select * from table_features where (minx < x2) and (maxx > x1) and (miny < y2) and (maxy > y1)
e.g. load all features overlaping screen rect (or whatever rect for that metter)
and then load points of loaded features from another table.

Table of features has format:
FeatureID(Int), DataString(XML Blob)

Table of points has format:
FeatureID(Int), PointNr(Int), PointID(Int), X(Int), Y(Int)

so different features can have common points. Feature has points ordered, so PointNr has to be incrementaly set by user from 0 to n.

Anyway, loading time is not quite good even when I have all coordinates indexed. The reason is that IB search for feature by index, say, minx and then find a lot of features that fits (minx < x2). Then IB looks at index maxx to reduce query to records that has (maxx > x1) and so on.

I would prefer to have kind of "Spatial Index" of feature "envelope" coordinates (minx, maxx, miny, maxy) that I can chack against another rectangle (x1, y1, x2, y2). Previous example would look like this:

select * from table_features objects_of SpatialIndex1 inside (x1, y1, x2, y2).
select * from table_points points_of SpatialIndex1 inside (x1, y1, x2, y2).

where SpatialIndex1 is created for table of features and points before.

This could work by recursively dividing squares of data following 2 rules:

1. Every feature is stored in smallest square of data that contains feature envelope
2. Square of data contains pointer to 4 "sub squares".

During SELECT it would be enough to recursively search rect-tree and return features that fits given rect.

Or someone has better idea?

Oracle 8i Spatial can do such thing, but even more sophisticated. It check relation of polygons, lines in database (inside, outside, overlaping, touching) not only rects, but I think that hendling rect in DB level will be enough, rest should be done in SW.

This thing would be VERY useful for GIS and CAD as more companies moves from file storage to databases, unfortunatelly I have no idea how IB works, so maybe some insider will consider this to be interesting :-)


Mirza Hadzic







[Non-text portions of this message have been removed]