Gordon Luckett – gordon.luckett_arrowgeo.com GS222-1 Spatial by jizhen1947

VIEWS: 6 PAGES: 44

									GS222-1: Spatial Databases With AutoCAD®
Map 3D and Autodesk MapGuide®
Gordon Luckett
GIS Consultant and Trainer




                        Gordon Luckett – gordon.luckett@arrowgeo.com
GS222-1: Spatial Databases With AutoCAD®
Map 3D and Autodesk MapGuide®
This class will explore how AutoCAD Map 3D stores data within
 Microsoft® SQL Server 2008 Spatial and Oracle® databases,
 and how Autodesk MapGuide Enterprise connects to these
 spatial repositories.

90-Minute Class

Wednesday, 5:00 PM (Banyan A) (Room: Banyan A)




                  Gordon Luckett – gordon.luckett@arrowgeo.com
Did you know that AutoCAD Map 3D:

Was released on October 31rst, 1995?
    Has had a full topology model since then?


Can perform spatial analysis such as Network Tracing, Overlay
 Analysis and Buffering since day-1?

Could store spatial objects in Oracle since March 1rst, 2002
 (AutoCAD Map R5)?

Had the first Oracle FDO connector? FDO Release 1 –
 introducing the Feature Data model.



                       Gordon Luckett – gordon.luckett@arrowgeo.com
Analysis Required

PROBLEM:
Municipality distributes its Spatial Tasks
 between departments
 (Planning and Engineering)

   Cadastral (parcels.shp) managed by Engineering
   Zoning Areas (zoning.shp) managed by Planning


REQUIREMENT:
Find out the zoning designation of each parcel
  so it can be thematically rendered for Council
  Meeting.



                          Gordon Luckett – gordon.luckett@arrowgeo.com
Agenda for AutoCAD Map 3D 2010
   Ensure Databases are available:
     SQL Server 2008 Express (supports geometry and geography objects)
     Oracle 10G Express (supports SDO_GEOM objects)


   Using AutoCAD Map 3D 2010
     Create datastore in SQL Server
     Create datastore in Oracle


   Choose datasets to load
     Parcels.shp
     Zoning.shp


   Attach datastores for both Oracle and SQL Server
     Batch copy Parcels.sdf in Oracle and SQL
     Batch copy Zoning.sdf in Oracle and SQL

                           Gordon Luckett – gordon.luckett@arrowgeo.com
Agenda for Autodesk MapGuide Enterprise 2010
   Create Data Connections:
     SQL Server Spatial
     Oracle


   Join data to dynamic views

   Create – Layers, Map, Web Layout




                           Gordon Luckett – gordon.luckett@arrowgeo.com
Install SQL Server 2008 Express




             Gordon Luckett – gordon.luckett@arrowgeo.com
Install Oracle 10G Express




              Gordon Luckett – gordon.luckett@arrowgeo.com
Create SQL Server Datastore

   Choose Add SQL Server Spatial Connection
   Log into SQL Server (sa user)
   Choose Add New Data Store




                    Gordon Luckett – gordon.luckett@arrowgeo.com
Create SQL Server Datastore

   Choose Coordinate System
   Enter extents of data
   Check Use FDO Enabled Schema




   Edit Later for Edit Schema
                     Gordon Luckett – gordon.luckett@arrowgeo.com
Create Oracle Datastore

   Choose Add Oracle Connection
   Log into Oracle (system user)
   Choose Add New Data Store




                    Gordon Luckett – gordon.luckett@arrowgeo.com
Create Oracle Datastore

   Enter password
   Choose Coordinate System
   Enter extents of data
   Choose USERS tablespace




   Edit Later for Edit Schema
                     Gordon Luckett – gordon.luckett@arrowgeo.com
Choose datasets to load

   Drag and drop into Drawing Editor
   Parcel.shp & Zoning.shp




                     Gordon Luckett – gordon.luckett@arrowgeo.com
Confirm Datasets Attached




             Gordon Luckett – gordon.luckett@arrowgeo.com
BULK COPY BOTH SHPs into SQL SERVER




           Gordon Luckett – gordon.luckett@arrowgeo.com
BULK COPY BOTH SHPs into ORACLE




           Gordon Luckett – gordon.luckett@arrowgeo.com
View Data In SQL Server

   Microsoft SQL Server
    Management Studio
    Express

   Free download from
    microsoft.com




                    Gordon Luckett – gordon.luckett@arrowgeo.com
View Data In Oracle

   Oracle SQL Developer
    v 1.1 patch 3

   Install GeoRaptor
    georaptor.sourceforge.net


   Free downloads




                        Gordon Luckett – gordon.luckett@arrowgeo.com
Query back into Map 3D

   Add to Map
    from Oracle




                  Gordon Luckett – gordon.luckett@arrowgeo.com
Query back into Map 3D

   Add to Map
    from
    SQL Server




                 Gordon Luckett – gordon.luckett@arrowgeo.com
Query back into Map 3D

   Right-click any layer to draw features into database directly.




                      Gordon Luckett – gordon.luckett@arrowgeo.com
Create from Existing AutoCAD Objects

 New Feature from Geometry
NOTE: Does not retain:
 Object Data
 Block Attributes
 Link Templates




   Require export to SDF and
    Bulk Copy to keep attributes




                     Gordon Luckett – gordon.luckett@arrowgeo.com
What About Analysis Required?

PROBLEM:
Municipality distributes its Spatial Tasks between departments
 (Planning and Engineering)

   Cadastral (parcels.shp) managed by Engineering
   Zoning Areas (zoning.shp) managed by Planning


REQUIREMENT:
Find out the zoning designation of each parcel so it can be thematically
  rendered for Council Meeting.




                          Gordon Luckett – gordon.luckett@arrowgeo.com
Overlay Within AutoCAD Map 3D

   Use any two FDO Datastores (even 1 layer Oracle and 1 layer
    SQL Server Spatial)
   Results in a new SDF file.




                    Gordon Luckett – gordon.luckett@arrowgeo.com
What About MapGuide?




            Gordon Luckett – gordon.luckett@arrowgeo.com
Good news – Use Database for Analysis




             Gordon Luckett – gordon.luckett@arrowgeo.com
Oracle – Use Database for Analysis

create view PARCEL_ZONING_JOIN as
SELECT A.APN, B.ZONING ,B.ZONE_CLASS
FROM ORACLE_DATASTORE.PARCELS A,
  ORACLE_DATASTORE.ZONING B
WHERE SDO_RELATE(A.Geometry, B.Geometry,
  'mask=anyinteract') = 'TRUE'




               Gordon Luckett – gordon.luckett@arrowgeo.com
Oracle – Use Database for Analysis

For Details, check out:
http://oracle.com/technology/documentation/spatial.html




                 Gordon Luckett – gordon.luckett@arrowgeo.com
SQL Server – Use Database for Analysis

create view PARCEL_ZONING_VIEW as
select parcels.apn,
  zoning.zone_class,
  zoning.zoning
from parcels, zoning
where
  zoning.geometry.STIntersects(parcels.geometry) = 1




                  Gordon Luckett – gordon.luckett@arrowgeo.com
SQL Server – Use Database for Analysis

For details:
Google: “Geometry Methods Supported by Spatial Indexes”




                 Gordon Luckett – gordon.luckett@arrowgeo.com
Add Oracle and SQL Server to MapGuide

   Create Links to Datastores

   Join tables to PARCEL_ZONING_VIEW in either Oracle or SQL
    Server to get dynamic Zoning values for each parcel




                     Gordon Luckett – gordon.luckett@arrowgeo.com
Add Datasources to MapGuide
   Choose OSGeo FDO Provider for SQL Server Spatial




                   Gordon Luckett – gordon.luckett@arrowgeo.com
Add Datasources to MapGuide
   Choose Autodesk FDO Provider for Oracle




                    Gordon Luckett – gordon.luckett@arrowgeo.com
ODBC – Required for Non-Spatial Tables

   Create ODBC Connection to Oracle
   Create ODBC Connection to SQL Server

START > RUN “odbcad32” to open ODBC dialog or in
 Administrative Tools under Control Panel




                   Gordon Luckett – gordon.luckett@arrowgeo.com
ODBC Connections in MapGuide

   Join SQLSERVER_GIS to SQLSERVER_ODBC
   Parcels table will join to view called PARCEL_ZONING_JOIN




                    Gordon Luckett – gordon.luckett@arrowgeo.com
ODBC Connections in MapGuide

   Join ORACLE_GIS to ORACLE_ODBC
   Parcels table will join to view called PARCEL_ZONING_JOIN




                    Gordon Luckett – gordon.luckett@arrowgeo.com
MapGuide Parcel Layers now have ZONING




            Gordon Luckett – gordon.luckett@arrowgeo.com
Summary




          Gordon Luckett – gordon.luckett@arrowgeo.com
Summary

AutoCAD Map 3D: FDO Overlay “Identity”

Advantages:
   Done “Out of the Box”
   Completed without any programming or SQL
   Performed using any FDO Data Source (SDF, SHP, Oracle, SQL)


Disadvantages:
   Slow
   Result is SDF
   Static output
   Output not available in Database


                     Gordon Luckett – gordon.luckett@arrowgeo.com
Summary

GIS analysis using Oracle

Advantages:
   SQL Analysis rather than code
   Small amount of development


Disadvantages:
   Requires knowledge of SQL
   Requires Oracle…(Map 3D Overlay is data agnostic)
   Must join results to data table




                     Gordon Luckett – gordon.luckett@arrowgeo.com
Summary

GIS analysis using SQL Server 2008

Advantages:
   SQL Analysis rather than code
   Small amount of development


Disadvantages:
   Requires knowledge of SQL
   Requires SQL Server…(Map 3D Overlay is data agnostic)
   Must join results to data table




                     Gordon Luckett – gordon.luckett@arrowgeo.com
Summary

1.    Installed Oracle Express 10G
2.    Created Datastore in Oracle
3.    Install SQL Server 2009 Express
4.    Creating Datastore in SQL Server
5.    BULK Copied SHP into Oracle and SQL Server
6.    Overlay analysis with Map 3D
7.    Overlay analysis with SDO_RELATE is Oracle
8.    Overlay analysis with STIntersect in SQL Server
9.    Create Join between Views and Database in MapGuide
10.   Create Layers in Autodesk MapGuide Enterprise



                    Gordon Luckett – gordon.luckett@arrowgeo.com
Thanks and…




              Gordon Luckett – gordon.luckett@arrowgeo.com
Gordon Luckett – gordon.luckett@arrowgeo.com

								
To top