1 / 13

RDBMS-based GIS

RDBMS-based GIS. Using SpatialWare with SQL Server to manage forestry maps A case Study of Port Blakely Tree Farms. Chris Lacy, GIS Forester, Port Blakely Tree Farms Philip Woods, GIS Contractor, Isolines.net January 10 th , 2006. What is SpatialWare?.

rhonda
Download Presentation

RDBMS-based GIS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. RDBMS-based GIS Using SpatialWare with SQL Server to manage forestry maps A case Study of Port Blakely Tree Farms Chris Lacy, GIS Forester, Port Blakely Tree Farms Philip Woods, GIS Contractor, Isolines.net January 10th, 2006

  2. What is SpatialWare? • SpatialWare is software that extends MS SQL Server database capabilities via stored procedures and other code that enables the manipulation of spatial data. • To spatially enable the database three component parts are necessary: • 1. Spatial Data Type defining the data structure and storage mechanism. SpatialWare provides a user defined data type called ST_Spatial. • 2. Spatial Indexing providing custom index structure to handle spatial data. SpatialWare provides R-Tree indexing of spatial data. • 3. Spatial Operators extending the SQL interface to the data. SpatialWare provides: • Spatial Functions Perform operations on geometries to create new geometries. (e.g., create a buffer zone around a road (linear) geometry.) • Observer Functions Return numbers, objects, or attributes from within a geometry. (e.g., X ordinate of a point, Nth Vertex of a polyline.) • Spatial Predicate Functions Analyze geometries or pairs of geometries to see if they meet specific conditions. These functions return TRUE or FALSE (1 or 0) values and are generally used within a WHERE clause. (e.g., find overlapping geometries.) • Measurement Functions Perform calculations on geometries to find a measurable characteristic, such as length, or area. • Aggregate Functions Work across rows in a group taking one or more spatial objects of type ST_Spatial as input. They return a single-row result of a spatial object. • General Functions Perform operations, make queries, or change settings. • Geometry Construction Create geometry from a string using the ST_Spatial function. • Coordinate Functions Transform geometries from one coordinate system to another. Coordinate systems may be geographic (longitude/latitude), or projected (e.g., Mercator, Robinson).

  3. What are the Benefits Over Standard MapInfo Tables? • Data integration • Combine your GIS data with other business systems data within SQL Server • Data security. • Control read write access to the data at the table level of even column level. • Enforcement of data integrity • Use of Check constraints, referential integrity, triggers etc. enable tight control over attribute and table modifications • Backup and recovery • Automated database backup provides for data recovery in the case of system failure • Multi-user access to the data • Concurrent editing of tables by multiple users • Transaction processing • All work is completed or none of it is • Data redundancy • Reduction of data redundancy via relational database design techniques • Multiple data views • Use multiple views of the same data • Programmatic interfaces. • Interface with the database via different programs: MI Pro, MapX, or even non-geographic custom applications

  4. Setting up SpatialWare • Install the SpatialWare software to a SQL Server instance • Once installed all databases running on that server can use spatial data • Create a SQL Server database and spatially enable it via SpatialWare procedures • EXEC sp_spatialize_db • Create database tables and create spatial properties for the tables • exec sp_sw_spatialize_column 'dbo', 'geomtest','sw_geometry', 'sw_member' • Populate the tables with data from MapInfo tab files • Use the MapInfo EasyLoader utility to upload tab files to SQL Server • Use MapInfo to save spatial tables by choosing File > Save Copy As or File > New Table from the main menu. • Use SQL Server commands to create and populate tables • Create R-tree indexes for the spatial tables • exec sp_sw_create_rtree 'dbo', 'geomtest', 'sw_geometry', 'sw_member', null,200, 12000

  5. How It Fits Together

  6. Sample Queries • Select a record based on a point • exec sp_spatial_query 'Select StandKey, sw_geometry from StandsView where ST_Contains(StandsView.sw_geometry, ST_Spatial(''ST_Point(1267253.69, 4296780.58)''))‘ • Select all records within 0.5 of a mile • exec sp_spatial_query 'select Standkey, sw_geometry from StandsView where ST_Overlaps(ST_buffer(ST_Spatial(''ST_Point(1267253.69, 4296780.58)''),2600.0,5.0), StandsView.sw_geometry)‘ • Select a specific record and create a new object buffered by 150’ • exec sp_spatial_query 'select Standkey, ST_Buffer(SW_Geometry, 150.0, 0.1) From StandsView Where Standkey = ''0115N06W270006'‘’

  7. Using SpatialWare • Basic level • Take your current MI tables and load on to SQL Server • Intermediate level • Design simple relational database • Conform exist MI tables to the new structure • Add integrity constraints and rules to the database • Advanced level • Intermediate level • Using advanced SpatialWare functionality and custom code for data maintenance and analysis on the server • Integration spatial data with non-spatial business systems • Basic level benefits • Multi-user editing, incremental backup • Intermediate level benefits • Base level benefits • Data integrity • Multiple data views • Advanced level benefits • Intermediate level benefits • Enhanced data maintenance and analysis on the server • Multiple user interfaces to the data • Integration spatial data with non-spatial business systems

  8. SpatialWare at Port Blakely Tree Farms • Main focus is GIS data maintenance • Data entry is dialog driven • Uses SpatailWare and SQL Server functionality to ensure data integrity • Dynamically maintains historical states • Future expansion will be integration of spatial data with other business systems • The current GIS database contains • 13 spatial tables + 13 spatial history tables • 25 domain or lookup tables • In addition numerous stored procedures control how, what and when operations are performed on individual tables

  9. Uses at PBTF • Database maintenance – basic CRUD • Creation • Data created during maintenance process • New data sets uploaded to SpatialWare • Retrieval • Use of custom data views for different staff needs • Use of spatial queries for generating ad hoc data sets • Update • Use of MapInfo and custom data entry screens for data maintenance • Deletion • Use of MapInfo for record deletions

  10. What Does it Take to Leverage SpatialWare? • Knowledge of MapInfo • Database design and development • Knowledge of SpatialWare • Knowledge of MS SQL Server

  11. Conclusions So Far… • Practical to implement even in relatively small implementations • Provides a secure multi-user environment • Makes the data maintenance process easier to control • higher data quality and tracking • Provides the tools for server side analysis • Has enormous potential for corporate data integration

  12. Contact Info’ • Chris Lacy • GIS Forester • Port Blakely Tree Farms • clacy@portblakely.com • http://www.portblakely.com • Philip Woods • GIS Contractor • spatialpro@hotmail.com • http://www.isolines.net

More Related