Transcript Open Source Databases and GIS - PostGIS

Open Source Databases
and GIS
Flexible Technologies for
Flexible People
Open Source Databases

What Open Source Databases are there?






MySQL
PostgreSQL
Berkeley DB
mSQL
SAP DB
InterBase
Berkeley DB


Embedded database
Widely used in software




Extremely fast
Only usable via language bindings


Sendmail
Netscape
C, C++, TCL, Python
Commercially Supported

Sleepycat Software
MySQL




Most widely used OSDB
Very fast read access, good for web apps
SQL interface, ODBC, JDBC
Language bindings


C, C++, Perl, Python, TCL
Commercially supported


MySQL AB
NuSphere
PostgreSQL





Most extensible OSDB
Object relational design
ACID database
SQL interface, ODBC, JDBC
Language bindings


C, C++, Perl, Python, TCL
Commercially supported


PgSQL Inc
Red Hat (as Red Hat Database)
Why Not Oracle?

Complexity


Overhead


OSDBs have a lighter system footprint
Cost


OSDBs are relatively simple to install and
administer
OSDBs have no licensing fees
Accessibility

OSDBs are not a black box
When to use an OSDB?





When it is organizationally appropriate
When you are accessing data using standard
APIs
When you are building web based
applications
When you are building deployable
applications
When you control the architecture from end
to end
Spatial Data and RDBMs

Spatial Databases


Spatial data and attributes reside together in the
database
Complex hybrid queries are possible
select area(forestpoly)
from forestcover,history
where
forestcover.ageclass > 80 and
history.restocking = true and
forestcover.fid = history.fid and
distance(
forestcover.forestpoly,
GeometryFromText(’POINT(514234,5019313)’,26910)
) < 500


Very large seamless datasets can be stored
Unified data models are possible
Spatial Databases

Proprietary software




ArcSDE
DB2 Spatial
OracleSpatial
Open source software

PostGIS / PostgreSQL
PostGIS / PostgreSQL


Spatial database functionality in
PostgreSQL
OpenGIS “Simple Features for SQL”
(SFSQL) as design guide
PostGIS Objects

OpenGIS SFSQL Objects


POINT, LINESTRING, POLYGON, MULTIPOINT,
MULTILINESTRING, MULTIPOLYGON,
GEOMETRYCOLLECTION
OpenGIS SFSQL Representations


AsBinary()  Well-Known Binary
AsText()  Well-Known Text


POINT(-128 45)
MULTILINESTRING((-128 45,-128 46),(-130 50,-131 55))
PostGIS Functionality

Spatial indexing for all object types


R-Tree on GiST Index
Analysis functions




Area()
Length()
Distance()
Transform()
PostGIS Data Access

Loading / Dumping




Java


Includes shp2pgsql and pgsql2shp utilities
OGR utility allows importing from multiple formats
E00Pg utility allows importing from E00 files
Includes JDBC Extension Objects for Geometries
C/C++

Using Well-Known Binary over the PostgreSQL
C/C++ API
PostGIS Data Visualization

Internet mapping with Mapserver



PHP/Mapscript and PHP/PgSQL to build
interactive web mapping applications
ArcIMS emulator with Mapserver to view
PostGIS data within Arc8
Java mapping with GeoTools

GeoTools has a beta PostGIS layer data
source
PostGIS in the Real World (1)

City of Boston, USA


Online property analysis
internal web application
Identify parcels which
are proximate and
display results using
Mapserver

Queensland, Australia
Department of Natural
Resources


Forestry permitting
internal web application
Query database by
attribute and return map
of parcel area with
supporting information
from database
PostGIS in the Real World (2)


I-Cubed Inc, USA
Large automated image
processing system

Uses database to
coordinate image
processing functions,
tracking spatial extents
of incoming requests and
required imagery and
farming work out to
client computers.


Intevation Gmbh,
Germany
Massive multiplayer
online game

Players will have mobile
devices (pagers, cell
phones) and their
position recorded in the
database. Extraction and
visualization tools will be
used to visualize game
play.
PostGIS in the Real World (3)

Finnish Geodetic
Institute

Creating nationwide
distributed GIS network
using the GeoServer
OpenGIS WFS with
PostGIS as the database
backend

Refractions Research,
Canada

Maintaining the Digital
Road Atlas, a richly
attributed provincial road
network. Geometries are
integrated into a complex
data model including
versioning, network
integrity, and multiple
views of the data.
In Conclusion



Open source databases have the capabilities
to handle real world problems, and are in
widespread use
Open source spatial databases (PostGIS) are
new, but are already in use around the world
Many GIS database problems can now
realistically be addressed with open source
technology