If you are curious and want to know what is behind PostGIS online you can download the database, php-pages and others from:
http://download.jordogskog.no/pgo
There is two files, pgo.sql.tar.gz is the database as a dump and pgo.tar.gz is the rest of the files.
Just so it’s said:
Do whatever you want with this code, but don’t blame me if things goes wrong.
If you develop it I would appreciate to get a note, just of curiosity.
You will find no rocket science but maybe a few ideas that might be interesting. Now it is just presented as a quite raw copy of the site without much explanation so I will give a few notes here.
The basic idea behind the site is to take the sql-string the user gives and create a table from the result and then show that table through MapServer. In the beginning it created a view instead of a table to save some disc writing, but I found that the view had to be evaluted several times to find resulting geometry types,srid and finally to show the view. So my conclusion was that it is worth writing down the table. So what is actually happening when the sql-string is sent is that the “CREATE TABLE” and a table name is put in front.
The table gets the name map1, map2 or map3 depending on what button the user clicked. To make it working with many users the session is is also added after mapX, for example map139853657d34373592f51e5e8c45f06b7.
What also happens is that when a user sends his first sql-string the session id is stored in table adm.sessions with a timestamp. then the timestamp is updated for each new query the user runs. That makes it possible for a function in the database found in file functions.sql to drop all tables belonging to session id’s that has not been used for half an hour.
On the Mapserver side of the story there is a mapfile with definitions of 9 different layers. One for Map1 point, one for Map1 linestring, one for Map1 polygon, one for Map2 point and so on. To make it possible to run queries that return geometry collections that contains different types I use the new function in PostGIS 1.5, ST_CollectionExtract(geometry collection, integer type). It gives som overhead but makes the system working with results containing mixed types and as mentioned even mixed geometry collections. So each geometry is checked both for points, linestrings and polygons. The DATA definition for points in Map1 looks like this for instance:
DATA “the_geom FROM (
select ST_CollectionExtract(ST_Force_Collection(the_geom),1) as the_geom,
idmap1 from userdata.%relation%) as a
USING UNIQUE idmap1 USING SRID=%srid%”
As you see on the above example there is also an unique id added to the table. That id, here called idmap1, is never showed to the user and is just added to the table after it was created to make Mapserver happy.
What I think from this maybe is most noticeable is the last part, how to make Mapserver work with geometry collections. As said earlier it gives some overhead, but can be usable anyway.
That’s it for now. Any comments or suggestion for improvement is very welcome.
