Overlap

[james...@gmail.com]

Hi all,

I cant seem to get the overlap function to work in a SQL Select query.
I just want the query to return the area that overlaps with a buffer
table.

Can anyone please tell me the correct syntax to use?

Thanks in advance,

James

[Peter Horsbøll Møller]

if you want the area as a value this should do the trick:

Select CartesianArea(Overlap(MyTable.OBJ, MyBufferTable.OBJ), "sq m") "Overlap_sqm"
From MyTable, MyBufferTable
Where MyTable.OBJ Intersects MyBufferTable.OBJ

Peter Horsbøll Møller
GIS Developer, MTM GeoInformatics
Geographical Information & IT

COWI A/S
Odensevej 95
DK-5260 Odense S.
Denmark

Tel     +45 6311 4900
Direct  +45 6311 4908
Mob     +45 5156 1045
Fax     +45 6311 4949
E-mail  p...@cowi.dk
http://www.cowi.dk/gis

[James Stott]

Thanks Peter. That works great.

I would like to return a polygon of the area of overlap. Is that possible?

James

[Peter Horsbøll Møller]

Yes, in several ways ;-)

 

Here is one:

 

1. Make a copy of your table

2. Open the copy into the map and make it mappable

3. Select all from thsi table and set these as the target objects (Objects > Set Target)

4. Select all from the buffer table

5. Now use Objects > Erase outside to remove the part of the objects that not are within the buffer

 

Peter Horsbøll Møller
GIS Developer, MTM GeoInformatics
Geographical Information & IT
 
COWI A/S
Odensevej 95
DK-5260 Odense S.
Denmark
 
Tel     +45 6311 4900
Direct  +45 6311 4908
Mob     +45 5156 1045
Fax     +45 6311 4949
E-mail  p...@cowi.dk
http://www.cowi.dk/gis

 

---

From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of James Stott
Sent: Monday, November 19, 2007 12:32 PM
To: mapi...@googlegroups.com
Subject: [MI-L] Re: Overlap

[James Stott]

Hi Peter,

Thanks for the second response. I have used that in the past but I would like to be able to do it with a SQL Select which I can then put in a MapBasic application, without having to go through all those steps. I would like to be able to do it similar to the way I am doing an intersect query using a buffer (see below - (dataset, buffersize and studyArea are variables in the MapBasic program that the user specifies)):

Select * From dataset where OBJ Intersects (Select CartesianBuffer(OBJ ,100, buffersize,"m") From studyArea) Into Selection

Is that one of the others ways? Or is it not possible to achieve this?

Thanks,

[Spencer Simpson]

Sub-selects and object operations don't work all that well together.  Even worse, object expressions in select statements don't return objects, they return the name of the object type.  So,

 

Select CartesianBuffer(OBJ ,100, buffersize,"m") From studyArea

 

creates a temporary table with a row for each object in studyArea.  The table has a single column, all of whose values are "Region".  The objects are identical to the objects in studyArea.

 

This means you can't do it all with just one SQL setatement.

 

So you must create the buffer(s) in a temporary table.

 

Close table mybufs

Commit table studyArea as "C:\Documents and Settings\myusername\My Documents\mybufs.tab"

Open table "C:\Documents and Settings\myusername\My Documents\mybufs.tab" as mybufs

Update mybufs set obj= CartesianBuffer(obj, 100, buffersize,"m")
Select * From dataset, mybufs where dataset.OBJ Intersects mybufs.obj Into Selection
…do stuff with selection…

Drop table mybufs

 

To get overlapping polygons, you need to go through each row of mybufs and create each intersecting object separately.  There are elaborations you can make with progress bars and counters, but the basic code template is:

 

Declare sub main

 

Sub main

 

Dim studyarea_key_col_name as string

Dim dataset_key_col_name as string

Dim bufo_a as alias

Dim bufkey_a as alias

Dim overo_a as alias

Dim overkey_a as alias

Dim tdir as string

Dim sakey as string

dim dskey as string

Dim bufo as object

Dim overo as object

 

Dim bres as integer

Dim bunits as string

dim buffersize as float

 

OnError goto sowhat

close table studyArea

close table dataSet

close table mybufs

close table overlappers

close table bufoverlap

OnError goto 0

Open table studyArea interactive

Open table dataset interactive

 

studyarea_key_col_name = You set this

dataset_key_col_name = You set this

buffersize = You set this

bres = You set this

bunits = You set this

 

Tdir = "C:\Documents and Settings\myusername\Local Settings\Temp\"

 

Commit table studyArea as tdir+"mybufs.tab"

Open table tdir+"mybufs.tab" as mybufs

Update mybufs set obj= CartesianBuffer(obj, bres, buffersize, bunits)

Create table bufoverlap (bufkey You set this, studyarea_key You set this) file tdir+"bufoverlap.tab"

Create map for bufoverlap coordsys table dataset

Set table bufoverlap FastEdit on

Bufo_a = "mybufs.obj"

Bufkey_a = "mybufs."+studyarea_key_col_name

Fetch first from mybufs

Do While not EOT (mybufs)

   If (bufo_a)

      Then bufo = bufo_a

           Sakey = bufkey_a

           Select * from dataset where obj intersects bufo into overlappers noselect

           Overo_A = "overlappers.obj"

           Overkey_a = "overlappers."+dataset_key_col_name

           Fetch first from overlappers

           Do While not EOT (overlappers)

              If (overo_a)

                 Then overo = overo_a

                      Dskey = overkey_a

                      Overo = overlap (bufo, overo)

                      Insert into bufoverlap (obj, bufkey, studyarea_key) values (overo, sakey, dskey)

              End if

              Fetch next from overlappers

           Loop

           Close table overlappers

   End if

   Fetch next from mybufs

Loop

Commit table bufoverlap

Set table bufoverlap fastedit off

Drop table mybufs

exit sub

sowhat: resume next

end sub

[Spencer Simpson]

You probably noticed the flaw in the code I posted yesterday.  It will work perfectly but one of the columns in the table it produces has a misleading name.  It should be "dataset_key" instead of "studyarea_key".   I'll leave the updating of the actual code as an exercise for the reader.

 

Hope this helps

Spencer