Tag Archives: postgis

Make’ing PostGIS database

Posted on by
Reply

I’ve extended Sean’s idea of baking PostGIS-enabled databases using GNU Make a little bit and prepared new version of Makefile.postgis.

How to use it:

  • Install it to save yourself some typing: 
    $ ln -s Makefile.postgis Makefile
  • Run make to get basic usage information: 
    $ make
****** Makefile.postgis usage ******
*** Create new PostGIS database:
	DBNAME=mydb make -f Makefile.postgis create
*** Drop PostGIS database:
	DBNAME=mydb make -f Makefile.postgis drop
*** Check if database exists and PostGIS if enabled with PostGIS:
	DBNAME=mydb make -f Makefile.postgis check
*** Check if database exists:
	DBNAME=mydb make -f Makefile.postgis check-db
*** Check if database is enabled with PostGIS:
	DBNAME=mydb make -f Makefile.postgis check-postgis
  • Check if your database exists: 
    $ DBNAME=mydb make check-db
****** Makefile.postgis ******
****** Database 'mydb' not found
  • Create your database with PostGIS extension installed: 
    $ DBNAME=mydb make create
****** Makefile.postgis ******
****** Creating database 'mydb'...
****** Loading PostGIS into 'mydb'...
  • Check what has been created and installed: 
    $ DBNAME=mydb make check
****** Makefile.postgis ******
****** Database 'mydb' found
****** Makefile.postgis ******
****** Database 'mydb' is enabled with PostGIS
1.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
  • When you don’t need your database anymore, just drop it: 
    $ DBNAME=mydb make drop

WKT Raster Cruncher

Posted on by
1

I’ve been playing with some nice amount of pixels using WKT Raster engine recently. Today, I tiled the japan.tif (gdalinfo output is here) using 3 different sizes of tile block. All the work was done using gdal2wktraster.py loader available from WKT Raster repository. Below, I included more interesting numbers which may be helpful for others who will decide to juggle rasters in PostGIS database.

Tiling schemes applied on japan.tif file:

  1. Using natural block size reported by GDAL gives 700 tiles, 14000 x 20 pixels each: 
    gdal2wktraster.py -r japan.tif -t japan -o japan.sql -k
  2. Using block size 100 x 100 pixels gives 140 x 140 = 19600 tiles: 
    gdal2wktraster.py -r japan.tif -t japan_100 -o japan_100.sql -k -m 100x100
  3. Using block size 200 x 200 pixels gives 70 x 70 = 4900 tiles: 
    gdal2wktraster.py -r japan.tif -t japan_100 -o japan_200.sql -k -m 200x200

Sizes of all .sql text files produced are close to 1.2 GB. The timing for gdal2wktarster falls to around 40 minutes on Ubuntu 8.10 (i386) running under VirtualBox 2.with assigned single CPU core of Intel Xeon 3.2 GHz and 1024 MB RAM (host machine: 4 x Intel Xeon 3.2 GHz with 16 GB RAM). psql loads single table in about 6 minutes:

psql -d japan -f japan_100.sql

Interestingly, memory usage during processing WKT Raster by gdal2wktraster.py (Python run-time) seems not high (10-20%) and was on constant level of ~85 MB. However, 100% of CPU power was being eaten. It’s got me interested in comparing these results vector-brother of gdal2wktraster, to the shp2pgsql from PostGIS.

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?

PostGIS SVN moved

Posted on by
Reply

Paul just announced that Subversion repository of PostGIS source code has moved to OSGeo server. The new URL of SVN is: http://svn.osgeo.org/postgis/.

Only base location of the repository has changed, so the cheapest way to switch existing working copy that points to old location of SVN is to switch & relocate:

mloskot@dog:~/dev/postgis/_svn/trunk$ svn switch --relocate \
   http://svn.refractions.net/postgis/trunk/  \

https://svn.osgeo.org/postgis/trunk

For the sake of security, I would recommend committers to use https scheme which is well supported on OSGeo server.

WKT Raster crash course #1

Posted on by
2

Recently, a good friend of mine Sandro has started spreading the story of WKT Raster project. Here, I’d like to join him and post a bunch of technical notes about how to start using tandem of PostGIS and WKT Raster. This is the first post that hopefully will start a series discussing various aspects of use and development of WKT Raster extension.

What

From the overview of WKT Raster project:

WKT Raster is an ongoing project aiming at developing raster support in PostGIS. (…) WKT Raster’s goal is to implement the RASTER type as much as possible like the GEOMETRY type is implemented in PostGIS and to offer a single set of overlay SQL functions (like ST_Intersects) operating seamlessly on vector and raster coverages.

The idea of WKT Raster extension was presented (PDF 1.1 MB) in December 2008 by Pierre Racine from University Laval. Pierre’s presentation made foundation of the WKT Raster Specification.

Hacker #1: Why WKTRaster? sounds a bit silly :D
Hacker #2: Apparently the initial thoughts on it were expressed using a textual representation for the rasters and the thinking was that this would be core to the effort, though it has proven not so important. I also find the name unhelpful.

Actually, this is not as trivial problem as it may seem. I am not sure myself that the name is WKT Raster or WKTRaster, nor I’m confident if the name is fixed and will be valid in future.

Update 2009-03-08 14:05: See Pierre’s comment below.

Where

At the moment, WKT Raster project does not have a single home, but different parts of it live in different places:

Who

Currently, there is a small team of people who contribute their time, skills and money to move the project forward. Here is a summary of contributors everyone can collect from the scattered homepage(s) of WKT Raster:

  • Pierre Racine – originator and author of the WKT Raster idea and specification and implementation developer.
  • Sandro Santilli – well-known member of the PostGIS core team who is the architect of RASTER type definition, canonical and Well-Known-Binary format of stored raster, developer of input/output operations, and more.

There are also prime financial contributors who established development of the WKT Raster project: Steve Cumming, Martin Daly (from Cadcorp, where I work as member of core development team) and Tyler Erickson.

Recently, I’ve also joined the WKT Raster team and being led by Martin Daly I’m focused on programming new features and core testing.

News

Today and during next 3 days, a few people interested in the PostGIS / WKT Raster project are meeting together for the Toronto Code Sprint 2009. I’m also present there, though in spirit by joining the event on #tosprint IRC channel. I have a hope that during this meeting, we will discuss and agree about a couple of outstanding issues:

  • meta data tables: Do we need them? How to define schema of meta data?
  • review of specification of RT functions
  • improvements in the solution architecture and design to handle common raster use cases, for both categories, visualizations and analysis
  • how to answer needs of pyramids/overviews
  • does the project need an infrastructure, a new home?
  • and last but not least, how to achieve sustainability and rise more funds.

By the way, isn’t it good time to create WKT Raster article on Wikipedia? Writers are welcome!

Linux.com about GeoServer

Posted on by
2

Linux.com published an interesting article – a tutorial – about famous components of Free and Open Source Software stack for Web Mapping. Justin Palk, the author, gives a very accessible introduction to building Web Mapping solution using GeoServer, PostGIS and OpenLayers. I think it’s a great writing for anyone who takes first steps in Web Mapping with FOSS4G.

Serving and styling maps with GeoServer by Justin Palk on September 24, 2008