Freely available enterprise-class spatial database servers:
This presentation is the property of its rightful owner.
Sponsored Links
1 / 45

James Mooney, Brett Morgan, Kevin Ross, Adam Ruf, Ben Sigrist, and Art Lembo PowerPoint PPT Presentation


  • 90 Views
  • Uploaded on
  • Presentation posted in: General

Freely available enterprise-class 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. Introduction.

Download Presentation

James Mooney, Brett Morgan, Kevin Ross, Adam Ruf, Ben Sigrist, and Art Lembo

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


James mooney brett morgan kevin ross adam ruf ben sigrist and art lembo

Freely available enterprise-class 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


Introduction

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


Background

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.


Background1

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?


Spatial databases

Spatial databases

PostGIS

SQLServer Express

Oracle Express


Companies

  • Oracle

    • Oracle Database 10g Express Edition

    • Current Version 10.2.0.1.0 (Express Edition)

    • 32-bit

    • Company’s Purpose:

      • http://www.oracle.com/technology/products/database/xe/index.html

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

  • 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/


Supported data types

Supported Data Types

SQLSERVER

  • 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

POSTGIS

  • 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)))

ORACLE

  • OpenGIS Consortium Standards

    • WKT (Well Known Text)

    • WKB (Well Known binary)

    • Oracle spatial


Known limitations

Known Limitations

SQLServer

Data type restrictions

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,symdiff) return null if bigger than a single hemisphere

Oracle

  • 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

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)


  • Installation

    Installation


    Installation1

    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


    Loading spatial data

    Loading Spatial Data


    Simple line insertion with sql

    Simple line insertion with SQL


    Line segment

    Line Segment


    Oracle data loading

    Oracle Data Loading


    Data file

    Data File


    Ithparc2 linked to oracle

    Ithparc2 Linked to Oracle


    Query in sqlserver sqlcmd

    Query in SQLServer sqlcmd


    The query in manifold along with the original parcel drawing and the one from the database

    The query in manifold along with the original parcel drawing and the one from the database.


    Loading data with 3 rd party products

    Loading data with 3rd party products


    James mooney brett morgan kevin ross adam ruf ben sigrist and art lembo

    Manifold’s database console was used to Connect to the new database.


    Sqlserver exporting data with manifold

    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


    Sharpgis shapefile uploader

    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


    James mooney brett morgan kevin ross adam ruf ben sigrist and art lembo

    This shows the County Data Opened in manifold


    Postgis with spit

    PostGIS with SPIT


    Traditional and spatial queries

    Traditional and Spatial Queries


    Sql queries

    SQL Queries

    SQLServer

    • SELECT * From

      dbo.Cnty_sf1_Drawing

      where P001001 > 100000

    • SELECT SUM(P001001) as SUM

      FROM dbo.Cnty_sf1_Drawing

    • SELECT AVG(P001001) as AVG

      FROM dbo.Cnty_sf1_Drawing

    PostGIS

    • SELECT * FROM "BigBadData” WHERE "Population" > 90000

    • SELECT SUM("Population") AS Sum FROM "BigBadData"

    • SELECT AVG("Population") AS Average FROM "BigBadData"

    Oracle

    • SELECT GEODESC FROM Cnty_sf1_drawing WHERE P001001 > 100000;

    • SELECT SUM(P001001) AS SUM FROM Cnty_sf1_drawing;

    • SELECT AVG(P001001) AS AVG FROM Cnty_sf1_drawing;


    Sqlserver spatial queries

    SQLServerSpatial 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);


    Sqlserver spatial queries1

    SQLServerSpatial 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);


    Oracle spatial query buffer

    Oracle Spatial Query: Buffer

    SDO_BUFFER(Geometry, Distance in meters, Tolerance)


    Oracle spatial query union

    SDO_UNION(Geometry1, Geometry2, Tolerance)

    Oracle Spatial Query: Union


    James mooney brett morgan kevin ross adam ruf ben sigrist and art lembo

    PostGISSpatial Queries

    BUFFER

    SELECT ST_Buffer(geometry,.1)

    FROM "BigBadData"

    WHERE "Population" > 90000


    James mooney brett morgan kevin ross adam ruf ben sigrist and art lembo

    PostGISSpatial Queries

    UNION

    SELECT ST_UNION((SELECT geometry FROM "Parcels" WHERE gid = 27),(SELECT geometry FROM "Parcels" WHERE gid = 419))


    Discussion

    Discussion

    Observations and expectations of first time users


    Postgis

    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)


    Oracle

    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


    Sqlserver express 2008

    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.


    Future activities

    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.


    Conclusion

    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


    Workgroup ideas

    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 middle-ware tier?

      • What are the tradeoffs of this approach?


  • Login