Hello,
I have a quite challenging request to fulfill.
I need to show on a map points of different kind.
| TypeA |
| TypeA.ID |
| TypeA.GeoPoint |
| TypeA.Dim1 |
| TypeA.Dim2 |
| .... |
| TypeB |
| TypeB.ID |
| TypeA.GeoPoint |
| TypeB.Dim1 |
| TypeB.Dim2 |
| |
One of the requirements is that users must be able to select by area on the map, including both point types. This means that it is not possible to show the points on two different map layers.
So, my first approach was to create a single GeoPoint Table
| GeoPoints |
| GeoPoint.ID |
| GeoPoint.Type |
| GeoPoint.Point |
| TypeA.ID |
| TypeB.ID |
| TypeA |
| TypeA.ID |
| TypeA.Dim1 |
| TypeA.Dim2 |
| .... |
| TypeB |
| TypeB.ID |
| TypeB.Dim1 |
| TypeB.Dim2 |
| |
But the other requirement is that if you select, for example, a value in "TypeA.Dim1", you should still see all "Type B" points, and vice versa.
I tried different approaches:
1) Create a single table with all dimensions. Where the point type is "B", fill the "TypeA" dimensions with '*' and vice versa, and use STAR IS * statement.
| GeoPoint.ID |
GeoPoint.Type |
GeoPoint.Point |
TypeA.ID |
TypeA.Dim1 |
TypeA.Dim2 |
TypeB.ID |
TypeB.Dim1 |
TypeB.Dim2 |
| 1 |
A |
lat,lon |
A1 |
ADIMVAL1 |
... |
- |
* |
* |
| 2 |
A |
lat,lon |
A2 |
ADIMVAL2 |
... |
- |
* |
* |
| 3 |
B |
lat,lon |
- |
* |
* |
B1 |
BDIMVAL1 |
... |
This is working as expected, but causes the engine to explode all dimensions values for each row, resulting in a crash after the first selection (the real model is more complex than the example)....
2) Fill the non relevant dimension with 'ALL' instead of '*'
| GeoPoint.ID |
GeoPoint.Type |
GeoPoint.Point |
TypeA.ID |
TypeA.Dim1 |
TypeA.Dim2 |
TypeB.ID |
TypeB.Dim1 |
TypeB.Dim2 |
| 1 |
A |
lat,lon |
A1 |
ADIMVAL1 |
... |
- |
ALL |
ALL |
| 2 |
A |
lat,lon |
A2 |
ADIMVAL2 |
... |
- |
ALL |
ALL |
| 3 |
B |
lat,lon |
- |
ALL |
ALL |
B1 |
BDIMVAL1 |
...
|
Then, for each dimension, create a table like this:
| TypeA.Dim1 |
Dim1 |
| ADIMVAL1 |
ADIMVAL1 |
| ALL |
ADIMVAL1 |
| ADIMVAL2 |
ADIMVAL2 |
| ALL |
ADIMVAL2 |
But the model is complex and the dimensions are hundreds....
3) Leave the dimensions in the dimensions tables, fill with 'ALL' the non relevant IDs
| GeoPoint.ID |
GeoPoint.Type |
GeoPoint.Point |
TypeA.ID |
TypeB.ID |
| 1 |
A |
lat,lon |
A1 |
ALL |
| 2 |
A |
lat,lon |
A2 |
ALL |
| 3 |
B |
lat,lon |
ALL |
B1 |
Then concatenate each dimension table row with a row pointing to 'ALL' ID
| TypeA.ID |
TypeA.Dim1 |
TypeA.Dim2 |
| A1 |
ADIMVAL1 |
... |
| ALL |
ADIMVAL1 |
... |
| A2 |
ADIMVAL2 |
... |
| ALL |
ADIMVAL2 |
... |
Of course, this is doubling all the dimension table rows and it is exploding the dimension values for the non relevant point types. So the app is very slow....
4) I tried combining different alternate states, but it is hard to maintain the whole app consistancy with this approach.
Therefore, I am looking for a brighter solution, if one exists....
Thank You
Paolo
