1 / 49

Database Server Extension for managing and querying 4D gridded spatiotemporal data

Database Server Extension for managing and querying 4D gridded spatiotemporal data. Presented at the Edinburgh e-Science Institute Nov 1-2, 2005 conference on “Spatiotemporal Databases” by Ian Barrodale Barrodale Computing Services Ltd. (BCS) http://www.barrodale.com.

abra-collier
Download Presentation

Database Server Extension for managing and querying 4D gridded spatiotemporal data

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. Database Server Extension formanaging and querying 4D griddedspatiotemporal data Presented at the Edinburgh e-Science Institute Nov 1-2, 2005 conference on “Spatiotemporal Databases” by Ian Barrodale Barrodale Computing Services Ltd. (BCS) http://www.barrodale.com

  2. “At BCS we let the actual tasks that our clients are trying to accomplish guide our solutions, rather than producing software that dictates how clients can perform their work.” Provides customized software and R&D services to technical clients Successfully completed 450+ software development projects since incorporation in 1978 Long-term professional staff IBM Business Partner Major clients include: Canada - Province of BC (Elections BC, Ministry of Forests), DND,... USA - US Navy, NOAA, IBM, SPAWAR, Univ. of Mississippi,... Barrodale Computing Services Ltd. (BCS)

  3. Some Application Areas: Defense Sciences (ASW, MCM, METOC) Elections/Census (Geo-Spatial Database) Forestry (Spatial Timber Supply Models) Terrain Modeling (Watershed Delineation) Seabed Monitoring (Gas Hydrates) Some Skill Sets: Mathematical Analysis Algorithm Development Signal & Image Processing Modeling & Simulation Software Engineering Spatial Data Analysis Spatial Database Design Database Server Extensions Large Dataset Management Graphical User Interfaces Data Visualization Web Map Services Barrodale Computing Services Ltd. (BCS)

  4. (Simplistic)Database Classification Matrix Object Relational DBMS Relational DBMS Query Object Oriented DBMS No Query File System Complex Data Simple Data

  5. File Servervs.RDBMS + File Server Files for both metadata and data vs.RDBMS for metadata & files for data. File Server alone: • Simpler. • Less expensive. • Metadata stored in data file name/directory or inside gridded data file. RDBMS + File Server: • Integrity checking of metadata - integrity checking of metadata can be performed by built-in RDBMS features (check constraints, triggers, etc.). • Efficient access to metadata - e.g., indices can be used. • Easier to locate gridded data of interest - e.g., complicated queries on metadata can be performed. • Metadata separated from gridded data - data inconsistencies possible.

  6. Object Relational DBMS RDBMS for metadata & fileserver for data vs. ORDBMS (metadata & data integrated). ORDBMS: • Improved concurrency - concurrent users can safely query the same gridded data. • Composite data types - gridded data bundled with their metadata. • Improved integrity - ability to reject bad gridded data before it is stored in ORDBMS. • Database extensibility - easy addition of data types and operations. • Uniform treatment of data items - SQL interface can perform complex queries based on any of these data items, e.g., metadata as well as gridded data; less need for custom 3GL programming. • Custom data access methods - e.g., R-tree indexes. • Point-in-time recovery of gridded data possible. • Built-in complex SQL functions for gridded data operations- e.g., aggregating, slicing, subsetting, reprojecting, subsampling, ...

  7. BCS specializes in ORDBMS ApplicationsThe current main platforms for BCS database applications are IBM Informix Dynamic Server and PostgreSQL . Object Relational Data Base Management Systems (ORDBMSs) have four features that set them apart from traditional DBMSs: User-defined abstract data types (ADTs).ADTs allow newdata types with structures suited to particular applications to be defined. User-defined routines (UDRs).UDRs provide the means for writing customized server functions that have much of the power and functionality expressible in C. “SmartBLOBs”. These are disk-based objects that have the functionality of random access files. ADTs use them to store any data that does not fit into a table row. Flexible spatial indexing.R-tree indexing for multi-dimensional data enables fast searching of particular ADTs in a table. Example: Sum the “area” (UDR) of all “lakes” (ADT) contained (R-tree) in “British Columbia” (ADT)

  8. Query:From a given point on a stream, what is the entire area from which drainage is received?

  9. “SQL” Example 1: Find the area of the watershed that is upstream from where a given road crosses a given stream. SELECT Area(Watershed(streamElement, (Intersection(streamElement, roadElement)))) FROM streamNetwork, roadNetwork WHERE Overlap(Intersection(Box(streamElement), Box(roadElement)), userDefinedArea); Note: userDefinedArea is, say, a string provided by the user. UDRs: BOX - rectangle enclosing object INTERSECTION - common area OVERLAP - T or F WATERSHED - calculates watershed upstream from a point AREA - calculates area

  10. “SQL” Example 2: Find all side-scan sonar images, that are in a user-defined area, with a heading within one degree of 128.3 degrees and with an average slant range of less than 50 m. SELECT image FROM sonarImageArchive WHERE Overlap(Box(image), userDefinedArea) AND ABS(Heading(image) - 128.3) < 1.0 AND Average(SlantRange(image)) < 50.0; Note: userDefinedArea is, say, a string provided by the user. UDRs: SLANTRANGE - calculates slant range AVERAGE - calculates average HEADING - supplies heading of object ABS - absolute value BOX - rectangle enclosing object OVERLAP - T or F

  11. “SQL” Example 3: In a user-defined area, overlay on a sea floor map all “West”-looking side scan sonar images of “sandy” sea floor bottom type. SELECT Overlay(image, map) FROM sonarImageArchive, seaFloorMapping WHERE Overlap(Box(image), userDefinedArea) AND Overlap(Box(map), Box(image)) AND SlantDirection(image) = “West” AND surfaceType = “sandy”; Note: userDefinedArea is, say, a string provided by the user, and surfaceType is a column in seaFloorMapping. UDRs: SLANTDIRECTION - calculates slant direction BOX - rectangle enclosing object OVERLAP - T or F OVERLAY - overlays one image on another

  12. Gridded Data in Databases • Gridded data occurs in meteorology, oceanography, the life sciences, non-destructive testing, exploration for oil, natural gas, coal & diamonds,… • These datasets range from simple, uniformly spaced grid points along a single dimension (e.g., time series) to multidimensional grids containing several types of values (e.g., 4D cubes of meteorological attributes). • Grids have typically been stored in simple files and then manipulated by programs that operated on these files. Nowadays there is increasing justification for storing and manipulating gridded data in DBMSs: the principal advantages are their ability to (i) ensure data integrity and consistency, and (ii) provide diverse users with independent and effective query-based access to these data across multiple applications and systems.

  13. Gridded Data in Databases • However, implementing an efficient gridded DBMS can be very challenging, particularly when it involves Binary Large Objects (BLOBs), user-defined abstract datatypes (ADTs) that encapsulate grid data structures and attributes, and user-defined routines (UDRs) with which applications can create, manipulate and access the gridded data stored in these new datatypes. • BCS has developed an efficient technology that supports database storage, update, and fast retrieval of gridded data; it uses BLOBs, ADTs, and UDRs. • Our first implementation of this technology was a Grid DataBlade for IBM Informix, and then a Grid Extension for PostgreSQL; we are currently developing an analogous Grid Cartridge for use with Oracle.

  14. The BCS Grid DataBlade/Extension • is designed to handle 1D, 2D, 3D, 4D (and “5D”) grids. • stores grids using SmartBLOBS and a (user-controlled) tiling scheme that together permit very efficient generation of products (e.g., oblique slices or 1D sticks from4D grids). • sometimes provides more than 50-fold increases in speed of data product generation compared to the conventional approach that does not involve tiling or SmartBLOBs. • can store the data in, and convert it between, hundreds of mapping projections. • can handle irregularly spaced grids in any/all grid dimensions. • can handle the presence of multiple vector and/or scalar values. • provides several interpolation options. • provides for convenient database loading and extraction of grid files via one form of the commonly used NetCDF format. • provides C, Java, and SQL application programming interfaces. • is supplied with full user/programmer documentation.

  15. U.S. Navy Solution Worldwide weather grid Sample of interest ORDBMS User query Get grid sample of interest Used API to develop Grid types grid types, functions Grid functions & indexes BCS Grid DataBlade SQL

  16. U.S. Navy: Tactical Environmental Data Services Air Temperature Dust Aerosols Humidity-Refractive Effects Terrain Land Cover Fog Shelf /InternalWaves Wind Speed / Direction Rain Rate Surf Wind - Driven Circulation Sensible andLatent Heat Trafficability Soil Moisture Island Flow Wrecks Beach Profile Ice Waves Reefs, Bars, Channels Tidal Pulse Sediment Transport Coastal Configuration Turbidity Slope (Sea Floor) Swell / WaveRefraction Straits Hydrography - Fine Scales Biologics

  17. Medical Application Demo http://www.barrodale.com/grid_Demo/GridBladeApplet.html

  18. Medical Application Demo http://www.barrodale.com/grid_Demo/GridBladeApplet.html

  19. Grid Fusing: Visualized through IDV

  20. Grid Fusing: Visualized through IDV

  21. Grid Fusing: Visualized through IDV

  22. Grid Fusing: Visualized through IDV

  23. Grid Fusing: SQL for this example SELECT GRDFuse(         GRDFuseCollect(GRDPriorityGrid(image,1.0)),          '((grdspec             (translation -90.4 29.57 0 0)             (affine_transformation 0 0 0 .001  0 0 .001 0  0 1 0 0  1 0 0 0)             (dim_sizes 1 1 800 800))                                       (rules(weight)) )')                  FROM images i, places_of_interest p       WHERE i.imageType = 'aerialPhoto' AND           overlap(grdbox(i.image),grdbox(p.loc)) AND             p.name = 'New Orleans';

  24. SQL driving the Grid Fusion SELECT GRDFuse(         GRDFuseCollect(GRDPriorityGrid(image,1.0)),          '((grdspec             (translation -90.4 29.57 0 0)             (affine_transformation 0 0 0 .001  0 0 .001 0  0 1 0 0  1 0 0 0)             (dim_sizes 1 1 800 800))                                       (rules(weight)) )')                  FROM images i, places_of_interest p       WHERE i.imageType = 'aerialPhoto' AND           overlap(grdbox(i.image),grdbox(p.loc)) AND             p.name = 'New Orleans';UDR to resample a set of grids into a single grid.

  25. SQL driving the Grid Fusion SELECT GRDFuse( GRDFuseCollect(GRDPriorityGrid(image,1.0)),         '((grdspec             (translation -90.4 29.57 0 0)             (affine_transformation 0 0 0 .001  0 0 .001 0  0 1 0 0  1 0 0 0)             (dim_sizes 1 1 800 800))                           (rules(weight)) )')                  FROM images i, places_of_interest p       WHERE i.imageType = 'aerialPhoto' AND           overlap(grdbox(i.image),grdbox(p.loc)) AND             p.name = 'New Orleans';Two UDRs to build a set of transient grids, associating a floating-point value with each of these grids. This floating-point value is later used to establish the relative weight of each grid’s elements in producing the fused grid. We’ve chosen each grid to have equal weight.

  26. SQL driving the Grid Fusion SELECT GRDFuse( GRDFuseCollect(GRDPriorityGrid(image,1.0)), '((grdspec             (translation -90.4 29.57 0 0)             (affine_transformation 0 0 0 .001  0 0 .001 0  0 1 0 0  1 0 0 0)             (dim_sizes 1 1 800 800))            (rules(weight)) )')                  FROM images i, places_of_interest p       WHERE i.imageType = 'aerialPhoto' AND           overlap(grdbox(i.image),grdbox(p.loc)) AND             p.name = 'New Orleans';Each source grid is resampled at the same locations, using the source images’ spatial reference system, which is a Lat-Lon grid. The fused grid’s horizontal resolution is 0.001 degrees.

  27. SQL driving the Grid Fusion SELECT GRDFuse(         GRDFuseCollect(GRDPriorityGrid(image,1.0)),          '((grdspec             (translation -90.4 29.57 0 0)             (affine_transformation 0 0 0 .001  0 0 .001 0  0 1 0 0  1 0 0 0)             (dim_sizes 1 1 800 800))                                       (rules(weight)) )')                  FROM images i, places_of_interest p       WHERE i.imageType = 'aerialPhoto' AND           overlap(grdbox(i.image),grdbox(p.loc)) AND             p.name = 'New Orleans';The source of the grids is a table called “images”.

  28. SQL driving the Grid Fusion SELECT GRDFuse(         GRDFuseCollect(GRDPriorityGrid(image,1.0)),          '((grdspec             (translation -90.4 29.57 0 0)             (affine_transformation 0 0 0 .001  0 0 .001 0  0 1 0 0  1 0 0 0)             (dim_sizes 1 1 800 800))                                       (rules(weight)) )')                  FROM images i, places_of_interest p       WHERE i.imageType = 'aerialPhoto' AND           overlap(grdbox(i.image),grdbox(p.loc)) AND             p.name = ‘New Orleans';We use metadata stored in another column to pick only those images derived from aerial photographs.

  29. SQL driving the Grid Fusion SELECT GRDFuse(         GRDFuseCollect(GRDPriorityGrid(image,1.0)),          '((grdspec             (translation -90.4 29.57 0 0)             (affine_transformation 0 0 0 .001  0 0 .001 0  0 1 0 0  1 0 0 0)             (dim_sizes 1 1 800 800))                                       (rules(weight)) )')                  FROM images i,places_of_interest p       WHERE i.imageType = 'aerialPhoto' AND  overlap(grdbox(i.image),grdbox(p.loc)) AND             p.name = 'New Orleans';A second table called “places_of_interest” is used to include only source grids that overlap a region called “New Orleans”.

  30. Barrodale Grid Datablade for IBM Informix Resample and reproject a 386 MB raster image of the World www.barrodale.com/ projectionDemo/ ProjectionApplet.html select GRDExtract(grid, "((dim_sizes 1 1 600 600)(dim_names time level row column)(translation -1489986.000000 6574741.000000 0 0)(affine_transformation 0 0 0 3338.898164 0 0 3338.898164 0 0 1 0 0 1 0 0 0)(srtext 'PROJCS[@World_Hammer_Aitoff@,GEOGCS[@GCS_WGS_1984@,DATUM[@D_WGS_1984@,SPHEROID[@WGS_1984@,6378137.0,298.257223563]],PRIMEM[@Greenwich@,0.0],UNIT[@Degree@,0.0174532925199433]],PROJECTION[@Hammer_Aitoff@],PARAMETER[@False_Easting@,0.0],PARAMETER[@False_Northing@,0.0],PARAMETER[@Central_Meridian@,-65.0],UNIT[@Meter@,1.0]]'))"::GRDSpec) from grdImages where g_keytext = "world2k";

  31. Barrodale Grid Datablade for IBM Informix Sample a 4D grid along a flight path Cross Wind Head Wind Ocean Applications? Humidity

  32. BCS Grid DataBlade/Extension: Grids of Data • Grids can have 1, 2, 3, or 4 dimensions. • Each grid point can store several variables. • Some grid point values can be NULL. • Grid spacing along axes can be non-uniform.

  33. BCS Grid DataBlade/Extension: Grids of Data

  34. BCS Grid DataBlade/Extension: Types of Extraction • Orthogonal …. • Oblique………………………... • Radial ….

  35. BCS Grid DataBlade/Extension: Orthogonal Extraction

  36. BCS Grid DataBlade/Extension: Oblique Extraction

  37. BCS Grid DataBlade/Extension: Radial Extraction

  38. BCS Grid DataBlade/Extension: Types of Updates • Individual Points • Appending • Replacing Slices

  39. BCS Grid DataBlade/Extension: Updating Points

  40. BCS Grid DataBlade/Extension: Appending a Grid

  41. BCS Grid DataBlade/Extension: Replacing a Slice

  42. BCS Grid DataBlade/Extension: Types of Aggregation • JoinNew • JoinExisting • Union

  43. BCS Grid DataBlade/Extension: JoinNew

  44. BCS Grid DataBlade/Extension: JoinExisting

  45. BCS Grid DataBlade/Extension: Union

  46. How much memory does the server need whenextracting a large gridded derived product using the BCSGrid DataBlade (Informix) or the BCS GridExtension (PostgreSQL)?

  47. BCS Grid DataBlade: The Effect of Tile Size A good choice of tile size allows larger grids to be extracted.

  48. SUMMARYThe BCS Grid DataBlade/Extension …..is designed for applications where: • The data volumes are such that they can’t be kept in memory. • The amount of data extracted, in a particular query, is small relative to the amount stored. • The data needs some form of resampling.

  49. CONTACT INFORMATION: Dr. Ian Barrodale, President Barrodale Computing Services Ltd. (BCS) P.O. Box 3075 STN CSC Victoria BC V8W 3W2 Canada (250) 472 4332 voice, (250) 472 4373 fax e-mail: ian@barrodale.com For more information about BCS projects, experience, and capabilities, please visit: http://www.barrodale.com

More Related