Tag Archives: postgresql

PostGIS explains DE-9IM

Posted on by
3

PostGIS spatial database extension for PostgreSQLI am happy I belong to the hordes of PostGIS users. Recently I asked for a very small addition to the PostGIS manual that will explain the three basic terms of the geospatial geometry: interior, boundary and exterior.

Kevin Neufeld delivered a very well written chapter about Dimensionally Extended 9 Intersection Model (DE-9IM) with series of excellent visualisations of the non-trivial mathematics.

PostGIS - Dimensionally Extended 9 Intersection Model (DE-9IM)

Moreover, Kevin started completing the PostGIS Functions Reference with visual presentation of geometric problems together with SQL commands using various PostGIS functions that can be applied to solve particular situations. For example, what does the ST_Buffer function, how boolean predicates like ST_Contains check spatial relation of two geometries or what’s the difference between ST_Difference and ST_SymDifference.

Clearly, I’ve got way way more than I asked for or I expected. On behalf of myself and users who are about to start their adventure with PostGIS, I’d like to give big kudos to Kevin for this fantastic work!

Together with the recently documented PostgreSQL PostGIS Types and Function Support Matrix, PostGIS team is making abrupt manual a pleasant reading book.

By the way, here is a bunch of references about DE-9IM I found very useful myself:

PostGIS In Action

Posted on by
Reply

It really must be very hot and fresh news, so the virtual devil spirit of social networking hasn’t fished it out yet and also Google (check this if you haven’t heard yet about this local family company) lists less than 15 pages.

PostGIS provides over 300 spatial operators, spatial functions, spatial data types and spatial indexing enhancements. If you add to the mix the complimentary features that PostgreSQL and other PostgreSQL related projects provide, then you’ve got one jam-packed powerhouse at your disposal well suited for hardcore work as well as a valuable training tool for spatial concepts.

Three words: PostGIS In Action. The first book about PostGIS spatial database being written by Regina O. Obe and Leo S. Hsu with release planned for the beginning of 2010. First chapter of the book has been published and is freely available as PDF file through the Manning Early Access Program. Chapter two and three are also available for MAEP subscribers.

Next to the early access, another cool thing about the way Manning Publications release their books is possibility to comment chapters and discuss with authors directly through Manning Sandbox forums. There is no exception for the PostGIS in Action :-)

Update 2009-05-08T23:08:21+00:00: The book official announcement has been posted on postgis-devel and postgresqlonline.com.

I’m looking forward to grab the book!

Pierre, check the TOC for chapter thirteen. Cool, isn’t it?

The World is shaking

Posted on by
3

Oracle Buys Sun. Tom asked What this will mean for MySQL?. I can make a wager this is going to happen:

  • MySQL will be laid to rest (R.I.P.)
  • Oracle will continue (harder?) promoting Oracle Express as the leading free-of-charge database solution for Web shops and similar.
  • PostgreSQL stays where it is on position of the best Open Source Database Management System in the World.

Anybody is going to predict what will happen to Java?

Update 2009-04-22 09:09:07: Sun and Oracle: End of a beautiful dream – Open source goes to work by Gavin Clarke, The Register.

SOCI 3.0.0 RPM Packages

Posted on by
2

Denis Arnaud prepared RPM packages for SOCI library version 3.0.0. As Denis explains, these RPM packages primarily target Fedora family, but should work on other RPM-based distributions.

Currently, SOCI 3.0.0 RPM packages are available to download from Open Travel Request Parser project website. By the way, I’ve revealed Open Travel Request Parser uses SOCI, it’s really nice.

SOCI is a database access library for C++ that makes the illusion of embedding SQL queries in the regular C++ code, staying entirely within the Standard C++. It also fits very well to the world of templates defined in C++ Standard Library and offers extensive integration with data types from Boost C++ Libraries like optional, tuple and fusion:

Rowset rs = (sql.prepare << "select name from persons");
std::copy(rs.begin(), rs.end(), std::ostream_iterator(cout, "\n"));

Not to mention the coolness of flexible support for user-defined types.

Building PostGIS using Visual C++

Posted on by
8

I don’t like MinGW. I’ve been dreaming about building PostGIS using Visual C++ – the native development toolset for Windows platform – without being forced to install Unix-like environment inside Windows. Finally, I’ve got motivated enough and decided to make my dreams reality, so here is the story.

First, I collected all run-time dependencies required by PostGIS. I intentionally used the word run-time to indicate I’m not going to install MinGW neither Cygwin or any other GCC-for-Windows package. GNU make is also not required.

I use PostGIS source code from trunk in the Subversion repository.

Dependencies

There are only 3 software packages required:

  1. PostgreSQL 8.3.6 – official Win32 binaries and source code
  2. GEOS – build from sources, it’s safe to grab SVN trunk
  3. PROJ.4 – also build from sources and also from SVN trunk

I installed the PostgreSQL 8.3.6 without PostGIS extensions, as I’m going to provide my own :-), using default location c:\Program Files\PostgreSQL\8.3. The source code of PostgreSQL 8.3.6 went to d:\dev\postgresql\postgresql-8.3.6.

Short note on directories layout using for projects downloaded directly from repositories. Root path of source tree of each project have the same layout: D:\dev\PROJECT\_svn\trunk. For Visual Studio projects, these paths are defined as macros in postgis.vsprops (Visual C++ Property Sheet), so it should be easy to redefine them without any need to hack other project settings like Additional Include Directories and others.

Continue reading →

Run WKT Raster, run!

Posted on by
4

I have a not-so-small GeoTIFF raster dataset. Here is what GDAL has to say about it:

$ gdalinfo japan.tif
Driver: GTiff/GeoTIFF
Files: japan.tif
Size is 14000, 14000
Coordinate System is:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.2572235630016,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0],
    UNIT["degree",0.0174532925199433],
    AUTHORITY["EPSG","4326"]]
Origin = (137.386330630776030,38.325757833006122)
Pixel Size = (0.000256020461176,-0.000256020461176)
Metadata:
  AREA_OR_POINT=Area
  TIFFTAG_DOCUMENTNAME=ER Mapper 6.4
  TIFFTAG_IMAGEDESCRIPTION=ER Mapper GeoTiff raster translator V1.0: Band 1 = Red, Band 2 = Green, Band 3 = Blue
  TIFFTAG_SOFTWARE=ER Mapper 6.4
  TIFFTAG_XRESOLUTION=0
  TIFFTAG_YRESOLUTION=0
Image Structure Metadata:
  COMPRESSION=LZW
  INTERLEAVE=PIXEL
Corner Coordinates:
Upper Left  ( 137.3863306,  38.3257578) (137d23'10.79"E, 38d19'32.73"N)
Lower Left  ( 137.3863306,  34.7414714) (137d23'10.79"E, 34d44'29.30"N)
Upper Right ( 140.9706171,  38.3257578) (140d58'14.22"E, 38d19'32.73"N)
Lower Right ( 140.9706171,  34.7414714) (140d58'14.22"E, 34d44'29.30"N)
Center      ( 139.1784739,  36.5336146) (139d10'42.51"E, 36d32'1.01"N)
Band 1 Block=14000x20 Type=Byte, ColorInterp=Red
Band 2 Block=14000x20 Type=Byte, ColorInterp=Green
Band 3 Block=14000x20 Type=Byte, ColorInterp=Blue

I loaded it to PostGIS/WKT Raster table with regular blocking enabled in PostgreSQL 8.3 database running on Ubuntu 8.10 (32-bit) installed as a guest system under VirtualBox:

$ gdal2wktraster.py -r japan.tif -t japan_rb -o japan_rb.sql -k
$ psql -d test -f japan_rb.sql

The loader generated output file japan_rb.sql of size of 1.1 GB and it makes 700 records (raster tiles or blocks) in the database. The disk usage reported for the raster table is:

sistest=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'japan_rb';
 relfilenode | relpages
-------------+----------
       71700 |        5
(1 row)

I run simple test query:

$ psql -d test
test=# SET log_statement_stats TO 1;
SET
test=# SELECT rid FROM japan_rb WHERE RT_Width(rast) != 14000 OR RT_Height(rast) != 20;
 rid
-----
(0 rows)

In the PostgreSQL log I got dumped a bunch of interesting statistics:

2009-03-27 15:23:38 GMT LOG:  QUERY STATISTICS
2009-03-27 15:23:38 GMT DETAIL:  ! system usage stats:
 ! 5.084838 elapsed 2.544159 user 2.468154 system sec
 ! [4.960310 user 5.204325 sys total]
 ! 0/0 [16/28160] filesystem blocks in/out
 ! 0/320190 [0/647479] page faults/reclaims, 0 [0] swaps
 ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
 ! 0/155 [9/327] voluntary/involuntary context switches
 ! buffer usage stats:
 ! Shared blocks:      74192 read,          0 written, buffer hit rate = 51.71%
 ! Local  blocks:          0 read,          0 written, buffer hit rate = 0.00%
 ! Direct blocks:          0 read,          0 written

The virtual machine has assigned 1024 MB of RAM and one CPU Intel Xeon 3.2 GHz.

You are strongly welcome to share your comments?

Lambda, I love you!

Posted on by
4

I’m writing a small driver for readint WKT Raster data from RASTER column in PostGIS-enabled database and I want to report name of database I’m connected with. My reader eats connection string, and here I’ve fallen in love with Python lambda.

Given connstr stores connection string to PostgreSQL database in format well-known from libpq, single-line anonymous function can do the whole job:

filter(lambda db: db[:6] == 'dbname', connstr.split())[0].split('=')[1]

Complete example:

$ python
Python 2.5.2 (r252:60911, Oct  5 2008, 19:24:49)
>>> connstr = "dbname='rtest' host='localhost' user='mloskot'"
>>> filter(lambda db: db[:6] == 'dbname', connstr.split())[0].split('=')[1]
"'rtest'"
>>> connstr = "password='xxx' port=5432 dbname='rtest' host='localhost' user='mloskot'"
>>> filter(lambda db: db[:6] == 'dbname', connstr.split())[0].split('=')[1]
"'rtest'"
>>>

Have fun!