Downloaded 212 times
Uploaded byPeter Horsbøll Møller
The power of spatial SQL in MapInfo Professional
The document outlines a workshop agenda led by Peter Horsbøll Møller on the use of spatial SQL capabilities in MapInfo Professional. It covers techniques for joining tables based on spatial objects, calculating areas, and extracting and inserting spatial data. Various spatial operators and exercises are presented to illustrate practical applications of the concepts discussed.
More Related Content
Similar to The power of spatial SQL in MapInfo Professional
More from Peter Horsbøll Møller
The power of spatial SQL in MapInfo Professional
- 1. Peter Horsbøll Møller SeniorSystems Engineer Pitney Bowes Software
- 2. The Power ofSpatial SQL in MapInfo Professional Peter Horsbøll Møller Senior Systems Engineer June 15, 2012 Every connection is a new opportunity™
- 3. Workshop agenda • Inthis 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
- 4. SQL Select • Columns,expressions • Tables • Conditions 4
- 5. Joining via thespatial 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
- 6. 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
- 7. Intersects / PartlyWithin / 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 • C Contains Part A • C Contains Part B • C Contains Part D • D Contains Part A • D Contains Part C 7
- 8. 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
- 9. 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
- 10. Specifying columns • Pickthe 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
- 11. Exercise • Join thetwo 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
- 12. Adding a overlaptolerance • 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
- 13. Calculating area overlap Thereare 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
- 14. Exercise • Add aoverlap 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
- 15. 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
- 16. Adding an extraspatial 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
- 17. Creating a newtable • You can consider using the structure from your first table and just modify it to match the columns in your query 17
- 18. 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
- 19.
- 20. Exercise • Add aspatial 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
- 21. Regions and lines/polylines •Works just like regions and regions • Note that Overlap uses the style from the first object 21
- 22. Exercise • Find theroads 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
- 23. Adding multiple spatialcolumns • 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
- 24. Extracting multiple spatialobjects • You need MapBasic to get to these ”many” spatial columns • They could be inserted into the cosmetic layer in this way: Fetch First From Selection Dim oTemp As Object oTemp = selection.UObj Insert Into WindowInfo(FrontWindow(), 10) (OBJ) Values (oTemp) 24
- 25. Exercise • Select multiplespatial columns • Insert a few of the selected spatial data into the cosmetic layer of a map window 25
- 26. Some other spatialfunction • Overlap() • Erase() • Combine() • IntersectNodes() • ExtractNodes() • MBR() • Centroid() • CreatePoint() • CreateLine() • Buffer() (Cart/Sper) • Offset() (Cart/Sper) • OffsetXY() (Cart/Sper) • Rotate() • RotateAtPoint() 26
- 27. Thank You Peter HorsbøllMøller peter.moller@pb.com