Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
‹date/time›
‹footer›
‹#›
------=_NextPart_01C6271C.BD5B6990
Content-Location: file:///C:/2EC238C2/PBTFSpatialDB_files/master03.xml
Content-Transfer-Encoding: quoted-printable
Content-Type: text/xml; charset="utf-8"
RDBMS-based GIS
Using
SpatialWare<=
span
style=3D'font-size:75%'> 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 Contract=
or,
Isolines.net
January 10th=
,
2006
What is
SpatialWare?
•<=
/span>SpatialWare is software that extends MS SQL Server
database capabilities via stored procedures
and other code that enables the manipulation of spatial data.
<=
/span>
•
•<=
/span>To spatially enable the database three component p=
arts
are necessary:
–<=
/span>1. Spatial=
Data
Type defining the data structure =
and
storage mechanism. SpatialWa=
re provides a user defined data type called
ST_Spatial.
–<=
/span>2. Spatial
Indexing providing custom index
structure to handle spatial data.
SpatialWare provi=
des
R-Tree indexing of spatial data.
–3. Spatial
Operators extending the SQL inter=
face
to the data. SpatialWare
provides:
•<=
/span>Spatial Functions Perform operations on geometries to create new geometries. (e.g., cre=
ate
a buffer zone around a road
(linear) geometry.)
•<=
/span>Observer Functions Return numbers, objects, or attributes from within a geometry. (e.g.,=
X
ordinate of a point, Nth V=
ertex
of a polyline.)
•<=
/span>Spatial Predicate Functions Analyze geometries or pairs of geometries to see i=
f they
meet specific condi=
tions.
These functions return TRUE or FALSE (1 or 0) values and are generally used
within a WHERE clause. (e.=
g.,
find overlapping geometries.)
•<=
/span>Measurement Functions Perform calculations on geometries to find a measu=
rable
characteristic, such =
as
length, or area.
•<=
/span>Aggregate Functions Work across rows in a group taking one or more spa=
tial
objects of type ST_Sp=
atial
as input. They return a single-row result of a spatial object.
=
•<=
/span>General Functions Perform operations, make queries, or change settings.
•<=
/span>Geometry Construction Create geometry from a string using the ST_Spatial
function.
•<=
/span>Coordinate Functions Transform geometries from one coordinate system to
another. Coordinate system=
s may
be geographic (longitude/latitude), or projected (e.g., Mercator, Robinson=
).
What ar=
e the
Benefits Over Standard MapInfo
Tables?
•<=
/span>Data integration
–<=
/span>Combine your GIS data with other business systems =
data
within SQL Server
•<=
/span>Data security.
–<=
/span>Control read write access to the data at the table=
level
of even column level.
•<=
/span>Enforcement of data integrity
------=_NextPart_01C6271C.BD5B6990
Content-Location: file:///C:/2EC238C2/PBTFSpatialDB_files/slide0005.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="us-ascii"
–<=
/span>Use of Check constraints, referential integrity,
triggers etc. enable tight control over attribute and table modifications
•<=
/span>Backup and recovery
–<=
/span>Automated database backup provides for data recove=
ry in
the case of system failure
•<=
/span>Multi-user access to the data
–<=
/span>Concurrent editing of tables by multiple users =
3;
•<=
/span>Transaction processing
–<=
/span>All work is completed or none of it is
•<=
/span>Data redundancy
–<=
/span>Reduction of data redundancy via relational databa=
se
design techniques
•<=
/span>Multiple data views
–<=
/span>Use multiple views of the same data
•<=
/span>Programmatic interfaces.
–<=
/span>Interface with the database via different programs=
: MI
Pro, MapX, or even non-geographic custom
applications
Setting=
up
SpatialWare
•I=
nstall
the SpatialWare software to a SQL Server instance
–O=
nce
installed all databases running on that server can use spatial data
•Create a S=
QL
Server database and spatially enable it via SpatialWare procedures
–E=
XEC sp_spatialize_db
•C=
reate
database tables and create spatial properties for the tables
–exec sp_sw=
_spatialize_column 'dbo', 'geomtest','sw_geometry', 'sw_member'
•P=
opulate
the tables with data from MapInfo tab files
–U=
se the
MapInfo EasyLoader utility to upload tab files to SQL Server
–Use MapInfo to save spatial tables by choosing Fil=
e >
Save Copy As or File =
>
New Table from the main menu.
–U=
se SQL
Server commands to create and populate tables
•C=
reate
R-tree indexes for the spatial tables
–exec sp_sw=
_create_rtree 'dbo', 'geomtest', 'sw_geometry', <=
span
style=3D'position:absolute;top:73.0%;left:14.04%;width:53.37%;height:2.75%=
'>'sw_member', null,200, 12000
![](3D"slide0005_image002.gif")
How It =
Fits
Together
![](3D"slide0011_image004.gif")
Sample
Queries
•
•Select a record based on a point
–<=
/span>exec sp_spatial_query 'Select StandKey, sw_geometry from StandsView where ST_Contains(StandsView.sw_geometry, =
ST_Spatial(''ST_Point(1267253.69,
4296780.58)''))‘
–
–
–
–
–
–
•<=
/span>Select all records within 0.5 of a mile
–<=
/span>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)‘
<=
/div>
–
–
–
–
•<=
/span>Select a specific record and create a new object buffered by 150’
–<=
/span>exec sp_spatial_query 'select Standkey, ST_Buffer(SW_Geometry, 150.0, 0.1) From StandsView Where Standkey =3D
''0115N06W270006'‘’
![](3D"slide0012_image006.jpg")
![](3D"slide0012_image008.jpg")
![](3D"slide0012_image010.jpg")