[postgis-users] Using two tables in mapserver

[Jan Hartmann]

Daryl,

There are many solutions for creating maps based on more than one table. 
I generally use a subselect in the DATA statement

LAYER
CONNECTIONTYPE POSTGIS
CONNECTION "your PG server"
DATA "the_geom from
         (  select map.the_geom, data.myvar
            from maptable as map, datatable as data
            where map.sid = data.sid
            and (... more filter conditions ... )
          ) as foo
      "
     CLASS
        expression ([myvar] > 1 and [myvar] < 10)
        color 10 20 30
     END
     CLASS
         expression ([myvar] >=10 and [myvar] < 20)
         color 20 30 40
      END
      etc ...
END

I don't think you should use MapServer's FILTER statement. I don't know 
if it works on a PostGIS table, but doing your filtering in PostgreSQL 
is anyhow much cleaner.

As you can see, the "myvar" variable in the subselect clause is 
available to MapServer in its class expressions. This doesn't have to be 
a table variable: you can just as well use a computed variable, provided 
you give it a synonym, so MapServer can refer to it:

the_geom from
     (  select map.the_geom,
        case when data.myvar> 1 and data.myvar < 10 then 1
             when data.myvar >=10 and data.myvar < 20 then 2
        end as mycolor
        from maptable as map, datatable as data
        where map.sid = data.sid
      ) as foo

In this case you can use CLASS expressions like:

CLASSITEM mycolor
CLASS
     expression 1
     color 10 20 30
END
CLASS
     expression 2
     color 20 30 40
END
etc ...

For simple cases you can let MapServer itself take care of the class 
computations, as in the first example. With multiple tables and complex 
computations, the second way quickly becomes more attractive.

Performance should be good, PROVIDED you put indices on your variables: 
GIST for geometry, and regular ones for every field you use as a link.

BTW the reason your DATA statement dies so horribly is that you use two 
tables without using a join (where a.var=b.var). PostgreSQL will try to 
return the cross product of both tables (every row of a combined with 
every row of b)!

Hope this helps. Took me some time to figure out!

Jan

Daryl Herzmann wrote:
> 
> I have a database with two tables.
> 
> Table (a) has my data in it.  One of the columns is called sid, which is 
> an identifier for the data point.
> 
> Table (b) has the geometry info with a unique column called sid, which is 
> the same as sid in table a.
> 
> So with Mapserver, I would like to cross-reference these tables.  I bet 
> dollars to donuts, that I am doing something stupid here!!  Here are the 
> relevant .map lines
> 
> DATA "b.geom from tablea a, tableb b"
> FILTER "b.sid = a.sid and a.day = '2000-02-10'"
> 
> This dies a horrible death, since OID::text is ambiguous.  And based on 
> the assembled query, there are other problems....
> 
> I could put a geom column on tablea and make it work, but I would like 
> something more elegant!  Basically, I have many tables and would like them 
> all to use the geom information in one table.  Maybe the performance would 
> be too poor?  tableb has only 500 entries in it, but the data tables have 
> 200 million rows or so combined.
> 

Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl