MapInfo Pro

Expand all | Collapse all

SQL Select "INTERSECT" understanding

  • 1.  SQL Select "INTERSECT" understanding

    Posted 02-19-2020 19:50
      |   view attached
    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
    ------------------------------


  • 2.  RE: SQL Select "INTERSECT" understanding

    Posted 02-20-2020 02:39

    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
    ------------------------------



  • 3.  RE: SQL Select "INTERSECT" understanding

    Posted 02-20-2020 17:11
    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
    ------------------------------



  • 4.  RE: SQL Select "INTERSECT" understanding

    Posted 02-20-2020 14:56
    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
    ------------------------------



  • 5.  RE: SQL Select "INTERSECT" understanding

    Posted 02-20-2020 17:12
    Nicolas. I love the simplicity of this. Thank you. Much appreciated. Rich

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