MapInfo Pro

hi. I am trying to understand how to INTERSECT (SQL Select) all ROADS ONLY inside an AREA (as shown in the example image attached).
The image shows Roads that extend OUTSIDE my area of interest - see blue arrows
My results always get Roads that begin in the area, but go outside the area. I only want intersecting WITHIN, not outside.
Can I get MIPro to CHOP these LINES at the edge of the Area of Interest? So it only displays Roads INSIDE?

------------------------------
Richard Lauder
UPPER HUNTER SHIRE COUNCIL
Scone NSW Australia
------------------------------

Hi Richard

You have multiple options which solution you should use depend on your needs.

I 'll be using the terms Roads and AreaOfInterest for your tables

Intersects

Select *
From Roads, AreaOfInterest
Where Roads.OBJ Intersects AreaOfInterest .OBJ

This will return all the objects that in some way intersect or touch your area of interest. The full extent of the roads will be returned.

Entirely Within

Select
From Roads, AreaOfInterest
Where Roads.OBJ Entirely Within AreaOfInterest .OBJ

This will only return the objects that are completely within your area of interest. It will also mean that roads that are 90% inside will not get selected.

Within

Select *
From Roads, AreaOfInterest
Where Roads.OBJ Within AreaOfInterest .OBJ

This will only return the objects where the centroid of the polyline is within the area of interest. The result here is almost impossible to predict. The centroid of a polyline is located on the middle segment of the polyline. You can no know if this is even close to half the distance from each end of the polyline.

Return part of the object

You can also use some expressions to make sure you only deal with the part of the object that is inside the area of interest. Here is an example where I measure the length of the roads inside the area of interest.

Select Roads.Name, ObjectLen(Overlap(Roads.OBJ, AreaOfInterest.OBJ), "km") "OverlapLength"
From Roads, AreaOfInterest
Where Roads.OBJ Intersects AreaOfInterest .OBJ

If you are using MapInfo Pro v2019, you can also make MapInfo Pro return only the part of the object that falls inside the AreaOfInterest. It the second line in the expression that makes MapInfo Pro return the roads that overlap the AreaOfInterest:

Select Roads.Name, ObjectLen(Overlap(Roads.OBJ, AreaOfInterest.OBJ), "km") "OverlapLength"
   , Overlap(Roads.OBJ, AreaOfInterest.OBJ) object
From Roads, AreaOfInterest
Where Roads.OBJ Intersects AreaOfInterest .OBJ

You can achieve the same with Erase Outside.

------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data
------------------------------

Original Message:
Sent: 02-19-2020 19:50
From: Richard Lauder
Subject: SQL Select "INTERSECT" understanding

hi. I am trying to understand how to INTERSECT (SQL Select) all ROADS ONLY inside an AREA (as shown in the example image attached).
The image shows Roads that extend OUTSIDE my area of interest - see blue arrows
My results always get Roads that begin in the area, but go outside the area. I only want intersecting WITHIN, not outside.
Can I get MIPro to CHOP these LINES at the edge of the Area of Interest? So it only displays Roads INSIDE?

------------------------------
Richard Lauder
UPPER HUNTER SHIRE COUNCIL
Scone NSW Australia
------------------------------

Awesome Peter as I didnt understand the functionality of the commands correctly. Thank you so much fo all that - Rich

------------------------------
Richard Lauder
UPPER HUNTER SHIRE COUNCIL
Scone
------------------------------

Original Message:
Sent: 02-20-2020 02:38
From: Peter Horsbøll Møller
Subject: SQL Select "INTERSECT" understanding

Hi Richard

You have multiple options which solution you should use depend on your needs.

I 'll be using the terms Roads and AreaOfInterest for your tables

Intersects

Select *
From Roads, AreaOfInterest
Where Roads.OBJ Intersects AreaOfInterest .OBJ

This will return all the objects that in some way intersect or touch your area of interest. The full extent of the roads will be returned.

Entirely Within

Select
From Roads, AreaOfInterest
Where Roads.OBJ Entirely Within AreaOfInterest .OBJ

This will only return the objects that are completely within your area of interest. It will also mean that roads that are 90% inside will not get selected.

Within

Select *
From Roads, AreaOfInterest
Where Roads.OBJ Within AreaOfInterest .OBJ

This will only return the objects where the centroid of the polyline is within the area of interest. The result here is almost impossible to predict. The centroid of a polyline is located on the middle segment of the polyline. You can no know if this is even close to half the distance from each end of the polyline.

Return part of the object

You can also use some expressions to make sure you only deal with the part of the object that is inside the area of interest. Here is an example where I measure the length of the roads inside the area of interest.

Select Roads.Name, ObjectLen(Overlap(Roads.OBJ, AreaOfInterest.OBJ), "km") "OverlapLength"
From Roads, AreaOfInterest
Where Roads.OBJ Intersects AreaOfInterest .OBJ

If you are using MapInfo Pro v2019, you can also make MapInfo Pro return only the part of the object that falls inside the AreaOfInterest. It the second line in the expression that makes MapInfo Pro return the roads that overlap the AreaOfInterest:

Select Roads.Name, ObjectLen(Overlap(Roads.OBJ, AreaOfInterest.OBJ), "km") "OverlapLength"
   , Overlap(Roads.OBJ, AreaOfInterest.OBJ) object
From Roads, AreaOfInterest
Where Roads.OBJ Intersects AreaOfInterest .OBJ

You can achieve the same with Erase Outside.

------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data

Original Message:
Sent: 02-19-2020 19:50
From: Richard Lauder
Subject: SQL Select "INTERSECT" understanding

hi. I am trying to understand how to INTERSECT (SQL Select) all ROADS ONLY inside an AREA (as shown in the example image attached).
The image shows Roads that extend OUTSIDE my area of interest - see blue arrows
My results always get Roads that begin in the area, but go outside the area. I only want intersecting WITHIN, not outside.
Can I get MIPro to CHOP these LINES at the edge of the Area of Interest? So it only displays Roads INSIDE?

------------------------------
Richard Lauder
UPPER HUNTER SHIRE COUNCIL
Scone NSW Australia
------------------------------

I have done this same exact processing on street data in order to break down my street dataset into individual municpality boundaries.  I go the second route Peter mentioned, which was using the ERASE OUTSIDE. 

1. Select streets
2. Set Target On
3. Select the polygon you wish to retain the streets inside of it
4. Choose Erase Outside

You'll be left with a new dataset that has been modified to crop the streets at the polygon's edge. ( as desired )

Good luck.

------------------------------
Nicholas Evanish
Senior Programmer Analyst
------------------------------

Original Message:
Sent: 02-19-2020 19:50
From: Richard Lauder
Subject: SQL Select "INTERSECT" understanding

hi. I am trying to understand how to INTERSECT (SQL Select) all ROADS ONLY inside an AREA (as shown in the example image attached).
The image shows Roads that extend OUTSIDE my area of interest - see blue arrows
My results always get Roads that begin in the area, but go outside the area. I only want intersecting WITHIN, not outside.
Can I get MIPro to CHOP these LINES at the edge of the Area of Interest? So it only displays Roads INSIDE?

------------------------------
Richard Lauder
UPPER HUNTER SHIRE COUNCIL
Scone NSW Australia
------------------------------

Nicolas. I love the simplicity of this. Thank you. Much appreciated. Rich

------------------------------
Richard Lauder
UPPER HUNTER SHIRE COUNCIL
Scone
------------------------------

Original Message:
Sent: 02-20-2020 14:56
From: Nicholas Evanish
Subject: SQL Select "INTERSECT" understanding

I have done this same exact processing on street data in order to break down my street dataset into individual municpality boundaries.  I go the second route Peter mentioned, which was using the ERASE OUTSIDE.

1. Select streets
2. Set Target On
3. Select the polygon you wish to retain the streets inside of it
4. Choose Erase Outside

You'll be left with a new dataset that has been modified to crop the streets at the polygon's edge. ( as desired )

Good luck.

------------------------------
Nicholas Evanish
Senior Programmer Analyst

Original Message:
Sent: 02-19-2020 19:50
From: Richard Lauder
Subject: SQL Select "INTERSECT" understanding

hi. I am trying to understand how to INTERSECT (SQL Select) all ROADS ONLY inside an AREA (as shown in the example image attached).
The image shows Roads that extend OUTSIDE my area of interest - see blue arrows
My results always get Roads that begin in the area, but go outside the area. I only want intersecting WITHIN, not outside.
Can I get MIPro to CHOP these LINES at the edge of the Area of Interest? So it only displays Roads INSIDE?

------------------------------
Richard Lauder
UPPER HUNTER SHIRE COUNCIL
Scone NSW Australia
------------------------------