How to calculate the count of overlap between 3 regions.

[M-A Gagnon]

I've been trying to solve this one for a few days now....
I have many radio wireless coverage regions, refered to as area1,
area2, area3... I want to see the section of each region that
overlaps with each other and how many areas are overlaping in that
region. Here is what I have so far:

Select Columns: AreaOverlap(Area1.obj, Area2.obj),
AreaOverlap(Area1.obj,
Area3.obj), AreaOverlap(Area2.obj, Area3.obj)

From Tables: Area1, Area2, Area3

Where: ((Area1.obj Intersects Area2.obj) OR (Area1.obj
Intersects Area3.obj) OR (Area2.obj Intersects Area3.obj))

I read a few posts here and there and I believe you guys are my best
chance of solving this, given the solution proposed by the Mapinfo
office.

[Lars I. Nielsen (GisPro)]

Hi M-A (?)

MapInfo Pro SQL only support inner joins.

But you can trick it into making an outer join, enabling you to do all
sorts of ALL-ALL comparisons. Just add a smallint field named "dummy" to
each table, and leave it uninitialized (= 0).

Then you run a query along these lines :

Select
Area ( Overlap ( Overlap ( area1.obj , areal2.obj ) , area3.obj ) ,
"sq mi" )
From
area1 , area2 , area3
Where
area1.dummy = area2.dummy and area2.dummy = area3.dummy and
AreaOverlap ( area1.obj , area2.obj ) > 0 and AreaOverlap (
area2.obj , area3.obj ) > 0 and AreaOverlap ( area1.obj , area3.obj ) > 0

It works in theory, but I haven't tried it out.

The first line of the Where clause is the join clause, that makes an
inner join of the three tables. But as all values are identical, you get
an outer join.

In normal SQL this would be specified with an "On" clause, but in Pro it
must be given as first conditions in the Where clause, and they must use
"and".

The second row selects all records where all three objects overlap each
other.

The selection is the common area (in square miles) of all the three regions.

Best regards / Med venlig hilsen
Lars I. Nielsen
GIS & DB Integrator
GisPro

M-A Gagnon skrev:

[Gentreau]

Hi M-A G

I would assume that you have a lot more than just three areas to compare,
would that be true?
In which case I would expect MapInfo SQL queries to be painfully slow.
What's the maximum number of coverage areas you need to compare?

I would think that you should try at first to limit your queries to a single
source region.
If you then test the overlap area with each other region and save the result
in a table, you will gradually build a result set. Otherwise you will be
trying to test every combination of overlaps, which with a normal sized
radio network would be a huge number of combinations.

Hth
Gentreau

www.plan-opt.com

[M-A Gagnon]

Yes I have many layers but I have many ressources to do this.
(dedicated system) Would rather do this as 1 querry.

I'm really trying to get the area where there is an overlap between
each of the regions and what regions are overlapping within that
area. I was thinking of creating an all_layers region and using a
proportionoverlap function to see the specifics areas where there is
an overlap. ( I only want the overlapping area, not the region as a
whole)
All_layers being all the areas combined in 1 layer. Let me know if
you think this is a good methodology....

Here is what we had last year:
http://mapinfo-l.googlegroups.com/web/coverage.jpg?gsc=iYvk6QsAAAAr8TMlIgWmf1b4zrZlzLzS
The "maker" of this left the company.

Here is what I have so far

select:
proportionoverlap(proportionoverlap(proportionoverlap(all_layers.obj,
area1.obj), area2.obj), area3.obj)

From: all_layers, area1, area2, area3

Where: all_layers.dummy=area1.dummy and area1.dummy=area2.dummy and
area2.dummy=area3.dummy and proportionoverlap(all_layers.obj,
area1.obj) >0 and proportionoverlap(all_layers.obj, area2.obj) >0 and
proportionoverlap(all_layers.obj, area3.obj) >0

> of solving this, given the solution proposed by the Mapinfo office.- Hide quoted text -
>
> - Show quoted text -

[Gentreau]

Why don't you take a slightly different approach?
You could create a grid of square regions to cover the area you want to
display.
Then query for each grid square to get the number of regions which overlap
it.
You can then update the grid table with the number of overlaps and move on
to the next square.

That would allow you to generate grids of different resolutions depending
upon the size of view
you want to create, for the whole of Canada a 1km grid would probably be
plenty.

Gentreau.

[Gentreau]

OK I just tried this and it may give you what you need.
I have a layer of regions called Regions_layer in a map window.

1. I create a Grid using the Gridmaker tool, I made a grid of 1km by 1km
squares with approx. 25000 regions
2. I add a column to the grid called Number
- Alter Table "Grid" ( add Number Integer )
3. I query the overlaps and update the Number column
- Add Column Grid (Number) From Regions_layer Set To Count(*)
Where Intersects

I now have a 1km grid with each square containing the number of regions
which cover any part of the square.
The query took less than 10 seconds to run, it took longer to create the
grid, in fact.

Hth

[Driver, Greg 9434]

If you merge all of your layers into 1 table you could then use the Check Regions function under the Objects menu to detect overlaps. This will return an object based on the overlap, which you could then save as a new table and then use in subsequent SQL queries.

Just a thought.

Greg Driver

System Administrator
Applications Support
ICT
Surrey Police
NOT PROTECTIVELY MARKED

*Internet communications are not secure and therefore Surrey Police does not accept legal responsibility for the contents of this message. This email and any attachments may be confidential. They may contain privileged information and are intended for the named addressee (s) only. They must not be distributed without our consent. If you are not the intended recipient, please notify us immediately and delete the message and any attachments from your computer, do not disclose, distribute, or retain this email or any part of it. Unless expressly stated, opinions in this email are those of the individual sender, and not of Surrey Police. We believe but do not warrant that this e-mail and any attachments are virus free. You must therefore take full responsibility for virus checking. Surrey Police reserves the right to monitor all email communications through their networks.*

[Andy Harfoot]

Hi there,

You could try following this method from a post I made back in Sept 05 -
unfortunately all the archives seem to be unavailable now - whilst this
doesn't answer all your questions, I think with a bit of work you should
be able to. One advantage of it is that the number of concurrent,
overlapping regions does not influence the steps. It assumes that all
the polygons are in a single table to start with.

Andy

> 1. With a copy of your polygon table, make it editable, and convert the
> polygons to polylines using the Objects > Convert to polyline command.
>
> 2. Create a new, mappable, table with a smallint attribute named
> 'OverlapCount', call it Polygon_Overlap or something similar, add it to a map
> window with the polyline table.
>
> 3. Make the Polygon_Overlap table editable, and then select all the
> records from the polyline table. Choose the Object > Enclose command,
> this will build a set of non-overlapping polygons in the
> polygon_overlap table.
>
> 4. Now use the Table > Update Column command and fill it in as follows:
>
> Table to Update: Polygon_Overlap
> Column to update: OverlapCount
> Get Value from: Original_polygon_table
> (The Join should be where object from Original_polygon_table contains
> object from Polygon_Overlap)
> Calculate: Count
>
> Hey presto, the OverlapCount column should be updated with the number of
> overlapping objects.

Note that you may find regions with no overlaps - these will be created
by the enclose method for 'holes' in the polygon coverage and can be
deleted.

--
Andy Harfoot

GeoData Institute
University of Southampton
Southampton
SO17 1BJ

Tel: +44 (0)23 8059 2719
Fax: +44 (0)23 8059 2849

www.geodata.soton.ac.uk

---------------------------------------
For further information about GeoData's
Training Courses, please visit:
www.gis-train.com
---------------------------------------