the power of spatial sql in mapinfo professional
DESCRIPTION
This is the presentation I used at the Insights User Conference for a training session back in June 2012TRANSCRIPT
![Page 1: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/1.jpg)
Peter Horsbøll MøllerSenior Systems Engineer
Pitney Bowes Software
![Page 2: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/2.jpg)
The Power of Spatial SQL in MapInfo Professional
Peter Horsbøll MøllerSenior Systems EngineerJune 15, 2012
Every connection is a new opportunity™
![Page 3: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/3.jpg)
Workshop agenda
• In this workshop we will play around with the spatial SQL capabilties of MapInfo Professional
• We will look at joining tables based on the spatial objects• We will calculate size of parts of the spatial objects• We will extract and insert parts into new tables
3
![Page 4: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/4.jpg)
SQL Select
• Columns, expressions
• Tables• Conditions
4
![Page 5: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/5.jpg)
Joining via the spatial object
• Select the two tables– Object will come from
first table
• Specify spatial condition:– Contains– Within– Intersects– Partly Within– Contains Part– Entirely Within– Contains Entire
5
![Page 6: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/6.jpg)
Contains / Within
• Comparing the Centroid!
• Order does matter• Good with points
• A Contains C• A Contains D• B Contain A• C Contains D
• C Within A• D Within A• B Within A• D Within C
6
![Page 7: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/7.jpg)
Intersects / Partly Within / Contains Part
• Comparing the entire object!
• Order doesn’t matter
• A Contains Part B• A Contains Part C• A Contains Part D• B Contain Part A• B Contains Part C• B Contains Part D• C Contains Part A• C Contains Part B• C Contains Part S• D Contains Part A• D Contains Part C
7
![Page 8: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/8.jpg)
Contains Entire / Entirely Within
• Comparing the entire object!
• Order does matter
• A Contains Entire D• C Contains Entire D
• D Entirely Within A• D Entirely Within C
8
![Page 9: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/9.jpg)
Spatial operators
• Within/Contains– Very fast– Inprecise when working with other objects than points
• Intersects/Partly Within/Contains Part– Slower then Within/Contains– Should be used when comparing lines/polylines and regions
• Entirely Within/Contains Entire– Slower then Within/Contains– To be used if one object should be ”entirely within” the other
9
![Page 10: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/10.jpg)
Specifying columns
• Pick the columns from the ”Columns” list
• Create expressions using the ”Functions” list or type in some functions manually
• Use aliases (”some new name”) for expressions
10
![Page 11: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/11.jpg)
Exercise
• Join the two tables Cadastre and Urban_Area• Join them using the Intersects operator• Select these columns
– CADASTRE.AREA_NAME– CADASTRE.LOT_NO– URBAN_AREAS.NAME
• Add these functions and aliases:– CartesianArea(CADASTRE.OBJ, "sq m") "Area_Cadastre”– CartesianArea(URBAN_AREAS.OBJ, "sq m") "Area_UrbanArea"
11
![Page 12: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/12.jpg)
Adding a overlap tolerance
• Overlap(obj1, obj2):– Creates an object where
the two objects have a common ”area”
• MapInfo Pro will create this object and calculate the area of it
• If this area is larger then 10 sq m, the intersection will be used
12
![Page 13: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/13.jpg)
Calculating area overlap
There are a number of ways to calculate the size of the overlapping area:
• CartesianArea(Overlap(obj, obj), units)– Uses the defined units– Should be used for projected data (non Longitude/Latitude data)
• SpericalArea(Overlap(obj, obj), units)– Uses the defined units– Should be used for Longitude/Latitude data
• AreaOverlap(obj, obj)– Uses the current units in MapInfo Professional– Uses the SpericalArea calculation
13
![Page 14: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/14.jpg)
Exercise
• Add a overlap tolerance by adding this to the condition:– CartesianArea(Overlap(Cadastre.obj, Urban_Areas.obj), "sq m") >
10
• Try different tolerances• How do you change the unit used?
14
![Page 15: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/15.jpg)
Extracting spatial data
• By default MapInfo Professional takes the spatial object from the first table in your query
• You can however add ”more” spatial objects to your query thru the ”Column” field
• To see these extra columns you need to insert the result into a new/another table
15
![Page 16: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/16.jpg)
Adding an extra spatial column
• Add your extra spatial object, can be an expression
• Consider using an alias
• Note that the result still will highlight the entire area from the first table
16
![Page 17: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/17.jpg)
Creating a new table
• You can consider using the structure from your first table and just modify it to match the columns in your query
17
![Page 18: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/18.jpg)
Inserting the query
• You need to do this thru the MapBasic window• Show the MapBasic Window from the Options menu• Write this:
Insert Into OverlapResult (AREA_NAME, LOT_NO, NAME, OBJ) Select AREA_NAME, LOT_NO, NAME, OverlapObj From Query1
• Where– OverlapResult is your new table– OverlapObj is your spatial column alias– Query1 is the name of yoru query
• And hit enter• Make sure your new table hasn’t been added to the map
as that will slow it down – a lot!18
![Page 19: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/19.jpg)
Final result
19
![Page 20: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/20.jpg)
Exercise
• Add a spatial column to your result• Create a new table• Insert the result into this new table using this:Insert Into OverlapResult (AREA_NAME, LOT_NO, NAME, OBJ) Select AREA_NAME, LOT_NO, NAME, OverlapObj From Query3
20
![Page 21: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/21.jpg)
Regions and lines/polylines
• Works just like regions and regions
• Note that Overlap uses the style from the first object
21
![Page 22: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/22.jpg)
Exercise
• Find the roads that intersects the urban areas• Use these columns from the roads:
– ROADNAME– ROUTE_NO– ID
• And this column from the urban areas– NAME
• Extract only the part of the road within the urban area• Create a new table• Insert the result into this new table
Insert Into UrbanRoads (ROADNAME,ROUTE_NO,ID,NAME, OBJ) Select ROADNAME,ROUTE_NO,ID,NAME, LineOverlap From Query1
22
![Page 23: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/23.jpg)
Adding multiple spatial columns
• The SQL statement can include several spatial columns
23
Select Cadastre.LOT_NO , Cadastre.AREA_NAME , Cadastre.obj "CObj" , Urban_Areas.obj "UObj" , Overlap(Cadastre.obj , Urban_Areas.obj) "JOBJ" From Cadastre, Urban_Areas Where Cadastre.Obj Intersects Urban_Areas.Obj into Selection
![Page 24: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/24.jpg)
Extracting multiple spatial objects
• You need MapBasic to get to these ”many” spatial columns
• They could be inserted into the cosmetic layer in this way:
Fetch First From SelectionDim oTemp As ObjectoTemp = selection.UObjInsert Into WindowInfo(FrontWindow(), 10) (OBJ) Values (oTemp)
24
![Page 25: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/25.jpg)
Exercise
• Select multiple spatial columns• Insert a few of the selected spatial data into the cosmetic
layer of a map window
25
![Page 26: The power of spatial SQL in MapInfo Professional](https://reader031.vdocuments.us/reader031/viewer/2022012309/54b710584a7959a8588b468d/html5/thumbnails/26.jpg)
Some other spatial function
• Overlap()• Erase()• Combine()
• IntersectNodes()• ExtractNodes()
• MBR()• Centroid()
• CreatePoint()• CreateLine()• Buffer() (Cart/Sper)• Offset() (Cart/Sper)• OffsetXY() (Cart/Sper)• Rotate()• RotateAtPoint()
26