GeoNotes

My latest project is to turn my favorite Linux desktop configurations into stand alone distributions that I can run as LiveCDs or install anywhere.

Here are some links so that I don’t forget.

http://www.ubuntugeek.com/creating-custom-ubuntu-live-cd-with-remastersys.html

I needed to update a column with the date and time whenever a record changed in my database. So here is the recipe for the trigger in MySQL 5.0.

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `my_database`.`UpdatedOn` BEFORE UPDATE
    ON `my_database`.`my_table`
    FOR EACH ROW BEGIN
set NEW.update_on = now();
    END$$

DELIMITER ;

Here is a

Here is a script (below) you can use to copy dump files between machines using scp from an
automated script. Please see attached. The script usage is as
follows:

./auto_scp.sh  
local_file   user@host:remote_folder  
user_password

or

./auto_scp.sh  
user@host:remote_file   local_folder  
user_password

Example:

./auto_scp.sh   dump.dmp   oracle@hostname:/U01/oracle
  <oracle password>

and here is the script
————————————————–

#!/usr/bin/expect -f

# connect via scp
spawn scp “[lindex $argv 0]” “[lindex $argv 1]”
#############################################
expect {
-re “.*es.*o.*” {
exp_send “yes\r”
exp_continue
}
-re “.*sword.*” {
exp_send “[lindex $argv 2]\r”
}
}
interact


I do this about twice per year and every time I have to look back at my notes to remember how. Maybe you are in the same boat. So, to save us both some trouble I’m going to blog my notes on the subject here.

exp schemaname/password@instance FILE=d:\mydump.dmp [follow instructions]
Give the file a name after FILE, most of the rest of the defaults are fine in the interactive prompting that exp will provide

For example, given a user called mike, his schema will be called mike in Oracle. The instance value is the same as is listed in the tnsnames.ora file in your Oracle client installation.

If you are using the free Oracle version called Oracle XE, the instance name will be XE
so given the example above,

exp mike/password@XE FILE=d:\mydump.dmp

Now to import

imp differentschemaname/password@instance fromuser=schemaname touser=differentschemaname FILE=d:\mydump.dmp

This example imports the dump file into a schema with a different name than what we exported from.

So here is an import example with an export from a schema called mike to a schema called tim

imp time/password@XE fromuser=mike touser=tim FILE=d:\mydump.dmp

To see the command line options for exp, enter exp help=y as below
exp help=y

You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,…,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
————————————————————————–
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.

To see the command line options for imp, enter imp help=y as below

$ imp help=y

Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:

Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,…,valueN)
Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword  Description (Default)       Keyword      Description (Default)
————————————————————————–
USERID   username/password           FULL         import entire file (N)
BUFFER   size of data buffer         FROMUSER     list of owner usernames
FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
DESTROY                overwrite tablespace data file (N)
INDEXFILE              write table/index info to specified file
SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
FEEDBACK               display progress every x rows(0)
TOID_NOVALIDATE        skip validation of specified type ids
FILESIZE               maximum size of each dump file
STATISTICS             import precomputed statistics (always)
RESUMABLE              suspend when a space related error is encountered(N)
RESUMABLE_NAME         text string used to identify resumable statement
RESUMABLE_TIMEOUT      wait time for RESUMABLE
COMPILE                compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION  import streams general metadata (Y)
STREAMS_INSTANTIATION  import streams instantiation metadata (N)

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set

EPA appears to be using ESRI’s ArcIMS 9.2 OGC connectors to publish OGC WMS/WFS services of their WATERS data.

See below for threads
that mention the limitations and issues with working with these semi-standard
services from ESRI.

http://mailman-viper.python-hosting.com/pipermail/users/2006-July/000103.html

http://openlayers.org/pipermail/users/2007-May/001571.html

http://www.mail-archive.com/users@openlayers.org/msg04240.html
- Little thread that I started a while ago to check on support for pure ESRI
services.

If you’ve ever needed to make nice looking relief maps like I do now, have a look at these sites.

http://www.reliefshading.com/ and http://www.shadedrelief.com/

I want to make something along the lines of Google’s terrain map, but with a client’s higher-resolution DEM data. I don’t if ESRI’s Spatial Analyst is going to get me quite where I want to go, so I’m going to have to maybe try some other tools

In my opinion you can’t beat UMN Mapserver or Mapnik for making gorgeous online maps. Mapserver has installed and run for me flawlessly every time I’ve tried it — without days of arguing with it. Mapnik has taken me nearly a week to get running sometimes. But, just like you wouldn’t want to shovel snow with a pitch fork, — always use the right tool for the right job. Mapnik is a focused tool for quickly turning geographic data (think shapefiles) into good looking static images (tiles) for serving to modern internet tiling maps.

If you need something like this (and a few other things) then read this excellent article.

http://mike.teczno.com/notes/mapnik.html

Plus it seems to be a good blog to check out from time to time.

I love this post thread so much I’m going to stick it into my blog. First because if you read into it and follow the links you can see the history of raster support in PostGIS (or reasons for lack thereof) for the PostGIS newbie readers. You can also see Paul’s encouragement and explanation of how this open source project works. Open Source developers develop stuff for their own needs. However, if you can make a well educated and clear argument for why they should spend their time putting your functionality into the project, they might just do it. Otherwise, they usually say, “it’s open source. code it yourself.”

——-

----- Original Message ----From: Paul Ramsey 
To: Pierre Racine 
Cc: warmerdam@pobox.com; nicolas.gignac@msp.gouv.qc.ca; smarshall@wsi.com; PostGIS Users Discussion 
Sent: Monday, July 14, 2008 10:11:05 PMSubject: Re: [postgis-users] PostGIS WKT Raster

Pierre,

Firstly, let me commend you for your understanding of the open sourcecommunity process! Not being shy, not taking “no” for an answer, andneedling the people you need to move are all excellent ways to keepthe wheels turning, particularly when done with good humor as you haveshown.  Other first timers could/should learn from your example!

So now I owe you a reply, no doubt:

You have gotten over my first hurdle, in that you have an actual usecase for raster, that is more involved than “I want to my images,because I hear database are faster”.

I have long thought that analysis, in particular a fusing of vectorand raster analysis was the only really compelling use case for rasterin database, so again, you’re on the side of the angels (me).

So, is your *design* good?

Probably as good as possible, but let me point out places of concernand see what you think:

- You propose to do this because their is a “great demand for it”, butthe great demand is generally the stupid demand for images-in-database“just because”. You must either somehow *stop* those people, or ensureyour solution is capable of meeting their needs.  Since you propose tomeet the demand, presumably you aim for the latter.  This *will*involve a good deal of non-analytical pre-processing infrastructure toconvert people’s multi-resolution, overlapping/underlapping filelibraries into a uniform resolution coverage.  I suggest you ignorethese people.- You are going to carry a certain amount of information into thedatabase and process data into bands, potentially for externalstorage.  Why not store external data in a format like TIFF that canhold all the bands and pyramids, etc?- Once you have the idea of external storage, you could as easily moveto internal storage with the BLOB interface.  Not that I recommendthis, it’s easier to back up and restore a filesystem of files than ahuge database full of BLOBs.- If you step back a bit and don’t even bother splitting up therasters into tiles, you can use an existing raster access library likeGDAL to work with serialized data.- Or you could use GRASS as your serialization format and hook intothat.  Added bonus: free algorithms.- Basically the less you muck with creating a “disk format for raster”(solved problem) and the more you muck with “integrating raster andvector analysis in SQL” (unsolved problem) the more leverage you willget.- I like your external storage idea. What are the implications forCREATE TABLE foo AS SELECT…?

Summary: your proposal is better than any I have seen, addressessolving problems that if solved will provide actual new functionalityand benefit to users, and clearly you’ve thought this through oversome time.

So, I look forward to seeing your initial plan of attack for development   

As you begin looking at the code base, you’ll find a few stubs ofraster support that Sandro built at my request for rasterizing vectorsinto CHIPs in the database… basically the first tip toe down thepath you have written up so completely.

Go with God.

Paul


On Mon, Jul 14, 2008 at 12:43 PM, Pierre Racine wrote:

 Paul Ramsey hasn’t yet said my design was:

 -a “meme” (http://blog.cleverelephant.ca/2008/06/x-my-l.html),

 -a “waste of time” (http://postgis.refractions.net/pipermail/postgis-devel/2007-July/002653.html)

-”useless” (http://postgis.refractions.net/pipermail/postgis-users/2007-May/015578.html)

 -that it was “pointless” (http://postgis.refractions.net/pipermail/postgis-users/2007-October/017250.html)

 -or that I was a “fool” (http://postgis.refractions.net/pipermail/postgis-users/2007-October/017239.html)

So I conclude it’s a good design!!!   

 He asked for a good design some time ago for raster in the database (http://postgis.refractions.net/pipermail/postgis-users/2006-October/013628.html)

 Where is the clever elephant?

 Pierre

—–Message d’origine—–

De : postgis-users-bounces@postgis.refractions.net

[mailto:postgis-users-bounces@postgis.refractions.net] De la

part de Pierre Racine

Envoyé : 7 juillet 2008 11:30

À : postgis-users@postgis.refractions.net

Cc : warmerdam@pobox.com; nicolas.gignac@msp.gouv.qc.ca;

smarshall@wsi.com

Objet : [postgis-users] PostGIS WKT Raster

Hi raster people,

I’m starting a 2-3 year project to develop a web-based application to

automate certain GIS tasks commonly needed in ecological research. The

tool will support queries over VERY large extent based on a Canada-wide

assemblages of raster and vector data layers. The queries will

typically

involve intersecting those layers with buffers defined around points or

transects.

We would like to implement this system with PostGIS, but it currently

does not to support raster data. We could convert all our data to a

single format (raster or vector) and use other tools, however PostGIS

seems to us the best and most powerful vector analysis tool available

and we would prefer to use it. We would like to develop a unified

toolkit so that the application mostly need not worry about

whether base

layers are in vector or raster format. We are then strong proponents of

having raster functionality in PostGIS. I have reviewed every thread on

this list on the subject. I have analysed them and I have compiled them

in the wiki

(http://postgis.refractions.net/support/wiki/index.php?RasterNotes).

The argument goes like this: The geodatabase paradigm has been a major

recent enhancement in GIS technology, I feel that the seamless

integration of raster and vector analysis should be one of the next.

Spatial analysis is emerging, beside the making and publishing of maps,

as one of the main desktop and web-GIS applications. Rastor/vector

seamless integration is already done for display (in most GIS and with

MapServer or ArcIMS on the web) but definitely not for

analysis. Desktop

analysts must still learn to use two distinct toolsets within most

(all?) GIS packages: one toolset for raster and another for

vector data.

Would not it be easier to build and use applications if we had a unique

data query and analysis toolset independent of the data model? I don’t

know any tool having this approach right now. A PostGIS foundation that

addressed this problem would offer better directions to application

developers than the dichotomic one proposed by ESRI since their

beginning. It would provide the necessary abstraction to develop GIS

with ONE set of analysis tools. I feel this is one good reason to

integrate raster in PostGIS. There is “GIS” in the word “PostGIS”.

PostGIS should then provide a COMPLETE GIS data foundation (read

“base”!) for geoapplication developers. I think Steve Marshall’s design

is a good step in this direction

(http://postgis.refractions.net/pipermail/postgis-users/2006-De

cember/01

4059.html).

At the same time, we would have a chance to reconsider the raster model

as a coverage instead of a series of images. Spatial databases got rid

of map sheets by allowing complete vector coverages to be stored as

single table (e.g. the entire United States) This approach should works

for raster data as well. Isn’t this the approach taken by ArcSDE?

In summary, I think we must stop thinking about PostGIS as a

mere vector

data repository to support mapping applications. This is the way most

objectors to raster integration seem to view it. We must think about

PostGIS as a powerfull indexed data analysis tool. Seamless

raster/vector analysis in the database could lead to a major

simplification of geoapplications.

I prepared a PowerPoint presentation with a complete specification of

raster in PostGIS and an analysis of what should be the result of

overlay operation between raster and vector layers. You can download

this PPT at: http://www.cef-cfr.ca/uploads/Membres/WKTRasterPostGIS.ppt

Please have a look at it, feel free to answer questions I ask and

comment.

Here are the main propositions of this design:

-Like a vector feature, rasters are stored as a subtype of “geometry”(a

new WKB/WKT geometry type called RASTER instead of POINT or POLYGON)

-There is no distinction between a raster and a tile. A tile

is a raster

and a table of rasters can be seen as a tile coverage. Hence, contrary

to Oracle GeoRaster, only one table is needed to store a coverage and

there is only one type.

-It support raster in the database AND raster out of the database. This

mailing list has shown that both approaches have their pros and cons.

Let’s not impose one approach over the other.

-It supports: multi-band, pyramids and variable nodata value, raster

size, pixel size and pixel type for each row.

-It supports import/export from/to Tiff and JPEG.

But moreover seamless raster/vector integration is materialized by

theses propositions:

-A lot of existing vector-only functions are adapted to work

with raster

also.

-Most new raster functions are also implemented to work with vector.

Only basic raster derivation functions are proposed.

-Most vector/vector existing functions are adapted to work seamlessly

with raster/vector or raster/raster

I also want to ask:

-What is the status of PGRaster? Is any development now underway?

We have some resources to devote to this project over the next

few years

and are very interested in forming collaborations to move this work

forward.

Pierre Racine

GIS/Programmer Analyst

University Laval

http://www.cef-cfr.ca/index.php?n=Membres.PierreRacine

Here is a nice GWT-based charting kit. It looks like it might be a reasonable replacement for a lot of we normally use JfreeChart for.

http://code.google.com/p/gchart/

I keep having to fish around for this URL so I’m just going to blog into this online note space that I maintain. One stop shopping.

http://maps.co.mecklenburg.nc.us/gp/

This is the Mecklenburg County GIS data portal to both environmental and cadastral-type information. I ran across it some time ago as an avid reader of Tobin Bradley’s excellent blog . I can’t say enough about Tobin’s blog and the work that he writes about. I think he employs some well thought out, no nonsense, light-weight-yet-effective approaches to solving common problems and the the projects he writes about demonstrate clean implementations. The mapping site above and the framework behind it embodies this ethic in my mind.