Transcript Spatial databases - GeoReference.org

Freely available enterpriseclass spatial database servers:
experiences with Oracle 10g Express,
SQLServer Express 2008 (CTP), and PostGIS
James Mooney, Brett Morgan,
Kevin Ross, Adam Ruf, Ben
Sigrist, and Art Lembo
© Salisbury University
Introduction
• Purpose: chronicle experiences in
using free offerings of spatial database
management systems for GIS.
– Installation, loading, analysis, and display
• Databases
– Oracle Spatial Express
– Microsoft SQLServer Express 2008 (CTP)
– PostGRES/PostGIS
© Salisbury University
Background
• The traditional GIS marketplace is
rapidly changing.
– Google: One of the largest Internet-based content
providers introduces Google Earth and Google Maps,
introducing geospatial information to the mass market.
– Microsoft: World’s largest software company
introduces spatial database capabilities in their
product offerings, and introduces Microsoft Virtual
Earth to compete with Google maps. Spatial
capability is now available to all Microsoft Office users.
– Open Source: A burgeoning market of freely
developed geospatial software places sophisticated
geospatial technology in the hands non-traditional
users.
© Salisbury University
Background
• Results of a new geospatial
information super-highway
– Volume: more users are now aware of a geospatial
information super-highway.
– Affordability: more users are now able to afford the tools
to drive on the geospatial information super-highway
– Navigation: more users require assistance to utilize the
tools of the geospatial information super-highway
• What geospatial information super-highway
capabilities are available to the general public at little
or no cost, and what is the potential for using these
tools?
© Salisbury University
Spatial databases
PostGIS
SQLServer Express
Oracle Express
© Salisbury University
•
Companies
•
Microsoft
– Microsoft: Redman, WA
– Microsoft is a software
company that has widespread
adoption in the marketplace
– 6 Editions of software from
Compact Edition to Enterprise
Edition.
– Prices range from $0 to
$25,000/processor
– We tested the Express Edition
– Latest version: SQL Server
2008
Oracle
– Oracle Database 10g
Express Edition
– Current Version 10.2.0.1.0
(Express Edition)
– 32-bit
– Company’s Purpose:
• http://www.oracle.com/tec
hnology/products/databas
e/xe/index.html
•
PostGIS
– PostGIS is a spatial
extension to PostgreSQL,
an advanced open source
database
– Developed and maintained
by Refractions Research
– Freely available, Enterprise
grade, Active user
community
– http://postgis.refractions.net/
© Salisbury University
Supported Data Types
POSTGIS
SQLSERVER
•
•
OpenGIS Consortium Standards
–
–
–
•
–
–
–
WKT (Well Known Text)
WKB (Well Known Binary)
Both of these formats include
information about the type of the
object as well as the coordinates
which form the object
–
Examples of object types
–
–
–
–
–
–
–
POINT (0 0)
LINESTRING (0 0,1 3,5 7)
POLYGON ((0 0,1 1,2 2,2 0,0 0),(0 5,5
1,6 1,0 5)
MULTIPOINT (0 0,1 3)
MULTILINESTRING ((0 0,1 1,1 2),(2
3,3 2,5 4))
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0
0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2
-2,-2 -1,-1 -1)))
GEOMETRYCOLLECTION(POINT(2
3),LINESTRING((2 3,3 4)))
Proprietary data format
•
Conforms to OGC standards
WKT (Well Known Text)
Geometry Data Type
• Represents data in an Euclidean
(flat) coordinate system
Geography Data Type
• Represents data in an Ellipsoidal
(round-earth) coordinate system
Examples of object types
–
–
–
–
–
–
–
Point
MultiPoint
LineString
MultiLineString
Polygon
MultiPolygon
GeometryCollection
ORACLE
•
OpenGIS Consortium Standards
–
–
–
WKT (Well Known Text)
WKB (Well
Known binary)
© Salisbury University
Oracle spatial
Known Limitations
SQLServer
• Data type restrictions
Oracle
•
– Each Geog instance must fit
in a Hemisphere
– Instance from (OGC),
(WKT),(WKB) object
larger than a hemisphere
throws an Argument
exception
– Geog methods that require
input from two Geog
instances
(intersect,union,differences,sy
mdiff) return null if bigger than
a single hemisphere
•
•
•
© Salisbury University
Memory – Only 1gb RAM is
addressed, limiting the
number of concurrent users.
XE will only use one CPU. It
can be run on a multi-CPU
configuration, but will not
scale up to use those other
CPUs.
Only one XE Database may
be run on an individual
computer.
4gb Disk Space limitation.
Architecture for testing
• Hardware
– Standard PC with Microsoft XP
– Shuttle PC P2 Prima 3500
– 64-bit, 3.0 GHZ.
– 8 GB RAM
– Quad-core
• Software
–
–
–
–
Spatial databases: Oracle, SQLServer,
PostGIS
Viewing: Manifold GIS, Quantum GIS
Loading: Manifold GIS, SPIT, SharpNet
• Data
–
–
–
–
Simple point, line, area
City of Ithaca parcels (5,000 area features)
Large fictitious grid (5 million area features)
TIGER data for Northeast US (4 million linear
features)
© Salisbury University
Installation
© Salisbury University
Installation
• Each product has a Window’s based installation
process
– Oracle had the simplest installation process
– PostGIS and SQLServer had numerous difficulties within
the University network.
– PostGIS and SQLServer worked fine on individual
computers
© Salisbury University
Loading Spatial Data
© Salisbury University
Simple line insertion with SQL
© Salisbury University
Line Segment
© Salisbury University
Oracle Data Loading
© Salisbury University
Data File
© Salisbury University
Ithparc2 Linked to Oracle
© Salisbury University
Query in SQLServer sqlcmd
© Salisbury University
The query in manifold along with the original parcel drawing and the
one from the database.
© Salisbury University
© Salisbury University
Loading data with 3rd party
products
© Salisbury University
Manifold’s database console was used
to Connect to the new database.
© Salisbury University
© Salisbury University
SQLServer - Exporting data
with Manifold
- Exporting data using Manifold is fairly
straightforward.
- Used Manifold to export BigBadData (5
million records) and the roads data (4
million records) into separate databases
- The BigBadData took about one hour
and twenty minutes
- The roads data took about forty five
minutes
© Salisbury University
SharpGIS Shapefile uploader
•
•
•
This tool was recently created by
Morten Nielsen
It can be obtained freely from
http://www.sharpgis.net
This tool was designed to easily
upload ESRI Shapefiles into
SQLServer 2008
© Salisbury University
This shows the County Data Opened in manifold
© Salisbury University
PostGIS with SPIT
© Salisbury University
Traditional and Spatial
Queries
© Salisbury University
SQL Queries
SQLServer
PostGIS
Oracle
•
SELECT * From
dbo.Cnty_sf1_Drawing
where P001001 > 100000
•
SELECT * FROM
"BigBadData” WHERE
"Population" > 90000
•
SELECT GEODESC
FROM Cnty_sf1_drawing
WHERE P001001 >
100000;
•
SELECT SUM(P001001) as
SUM
FROM
dbo.Cnty_sf1_Drawing
•
SELECT SUM("Population")
AS Sum FROM
"BigBadData"
•
SELECT SUM(P001001)
AS SUM FROM
Cnty_sf1_drawing;
•
SELECT AVG("Population")
AS Average FROM
"BigBadData"
•
SELECT AVG(P001001)
AS AVG FROM
Cnty_sf1_drawing;
•
SELECT AVG(P001001) as
AVG
FROM
dbo.Cnty_sf1_Drawing
© Salisbury University
SQLServer
Spatial Queries
Buffer
DECLARE @Wicomico geometry;
SET @Wicomico= (SELECT top 1 geom
FROM dbo.Cnty_sf1 WHERE
GEODESC= 'Wicomico County');
SET @Wicomico=
@Wicomico.MakeValid();
SET @Wicomico= (SELECT
@Wicomico.STBuffer(250).ToString());
Insert into bobo2(geom)
VALUES(@Wicomico);
© Salisbury University
SQLServer
Spatial Queries
UNION
DECLARE @Parc1 geometry;
DECLARE @Parc2 geometry;
DECLARE @Result geometry;
SET @Parc1= (SELECT top 1 geom1 FROM
dbo.Parcels WHERE gid= 27);
SET @Parc2= (SELECT top 1 geom1 FROM
dbo.Parcels WHERE gid= 419);
SET @Parc1= @Parc1.MakeValid();
SET @Parc2= @Parc2.MakeValid();
SET @Result= (SELECT
@Parc1.STUnion(@Parc2));
INSERT INTO Dist(geom)
VALUES (@Result);
© Salisbury University
Oracle Spatial Query: Buffer
SDO_BUFFER(Geometry, Distance in meters, Tolerance)
© Salisbury University
Oracle Spatial
Query: Union
SDO_UNION(Geometry1, Geometry2, Tolerance)
© Salisbury University
PostGIS
Spatial Queries
BUFFER
SELECT
ST_Buffer(geometry,.1)
FROM "BigBadData"
WHERE "Population" > 90000
© Salisbury University
PostGIS
Spatial Queries
UNION
SELECT ST_UNION((SELECT
geometry FROM "Parcels"
WHERE gid = 27),(SELECT
geometry FROM "Parcels"
WHERE gid = 419))
© Salisbury University
© Salisbury University
© Salisbury University
© Salisbury University
Discussion
Observations and expectations of
first time users
© Salisbury University
PostGIS
• Positive
– SQL syntax was the most straightforward
– PGAdmin was very easy to use (little directions
necessary)
– Useful GUI capabilities for management
– Easy integration with Manifold and QGIS
– Large and helpful user committee
• Negative
– Installation in certain environments was difficult
– Very large datasets locked up the server (possibly
due to our hardware configuration)
© Salisbury University
Oracle
• Positive
–
–
–
–
–
Very easy installation
Good documentation
Spatial SQL easy to write
Administration tool easy to use
Easy integration with Manifold
• Negative
– Oracle Express limited character length in
enormous INSERT statements
– Geometry queries required more thought, as
more parameters were required.
– Use of control files was cumbersome
© Salisbury University
SQLServer Express 2008
• Must remember that this was a CTP, and part of
the objective is to uncover inefficiencies. Therefore,
this is not a criticism of SQLServer Express 2008.
• Positive
–
–
–
–
Good online help
SQL straightforward
Relatively fast processing
Good integration with Manifold and SharpGIS
• Negative
–
–
–
–
Installation was difficult (but remember, it was a CTP)
Spatial SQL more cumbersome than other products
Currently no management studio
Reverse format of X,Y coordinates made certain loading
difficult.
© Salisbury University
Future Activities
• GEOG 415 – Special topics class (Spring 2009) at
Salisbury University in building enterprise spatial
database management systems
– Currently evaluating pilot projects for implementing an
enterprise-wide, low cost spatial database management
system within a local Eastern Shore agency.
• EFRI- RESIN: Decision Support for Water Supplies
and Critical Interdependent Infastructure
– Central enterprise coordination of shared geospatial storage
(Cornell University, University of Delaware, University of
Southern California, and Salisbury University) for a large
National Science Foundation (NSF) proposal. Spatial
database includes PostGRES/PostGIS with client access
from ArcGIS, Manifold GIS, Quantum GIS, and Microsoft
Office applications.
© Salisbury University
Conclusion
• Many free versions of large-scale spatial databases exist
beyond what we investigated
• Products were easy enough to use so that an evaluation of all
three was possible in a one semester course
• Each software product had its strengths and weaknesses in
terms of
–
–
–
–
Installation
Data loading
Data analysis
Data integration with third party products
• Having a third party product to manage data loading, analysis
and display is beneficial
• Cutting one’s teeth on the free versions of the data products are
an ideal way to prepare a full migration to the full package
© Salisbury University
Workgroup ideas
• Manifold does not really operate in a true
client/server implementation with spatial
databases
– Still too committed to caching all the data
– What are the tradeoffs of this approach?
• Should Manifold become a true client to
these spatial databases?
• Should Manifold create their own middleware tier?
– What are the tradeoffs of this approach?
© Salisbury University