Integrating attribute data from relational databases with Map Server

[Daniel Morissette]

Vinod Chettur wrote:
> 
> Hi Stephen:
> The rates are dynamically computed using stored procedures based on user
> input. So for example, death rates/1000 by counties for cancer for a given
> year are obtained as a result set:
> County 1 - 4.2/1000
> County 2 - 3.0/1000
> and so on.
> 
> So I would like to represent these rates by county as color coded categories
> such as
> 
> blue =  >0 and <1
> green = >=1 and < 5
> red >=5 etc.
> Categories could be done in SQL itself if needed. Just wondering what my
> strategy will be to merge this resultset with my counties shape file on the
> fly.
> 

Hi Vinod,

I have implemented an application that does exactly that using
PHP/MapScript, and funny enough, it maps the same type of data as you:
Cancer and other disease rates and the provincial and Census Division
level.  Unfortunately the web site is not publicly available yet, but
here is the trick that I used:

1- Do the query to the database based on user selections and get a
recordset.
2- Copy county.shp and county.shx to a new unique filename 
   (e.g. 213545232.shp).  If you're on Unix then use logical links
   since it's faster and will save you disk space!
3- Create a new DBF file with the same temporary filename 
   (e.g. 213545232.dbf) with 1 field only: "RATE".  Then for each 
   record in the original county.dbf file, you write the actual rate
   value obtained from the database to the corresponding record in
   the temporary dbf file.

Now you've got a shapefile dataset with all the rates to add to your
map.  Use MapScript to add a layer to your map, set the DATA value in
the layer to point to the temporary shapefile, set the classes and
redraw the map.

Another advantage of creating the temporary DBF file is that if the user
wants to zoom in, etc. then you do not need to redo the query to the SQL
database... as long as the user does not change his selections (cancer
site, year, etc.) you just redraw the map using the same temporary DBF
file.  The overhead of creating the temp. DBF file should be minimal
compared to running a query on the database every time.


Before implementing that trick I had discussed with Steve the
possibility to add in mapserver a type of join that would allow us to
"append" the temporary DBF to the original shapefile dataset's DBF.  We
decided to not add that feature yet since the trick described above is
quite simple and works very well.

I hope that helps,
-- 
------------------------------------------------------------
 Daniel Morissette                       danmo at videotron.ca
              http://pages.infinit.net/danmo/
------------------------------------------------------------
  Don't put for tomorrow what you can do today, because if 
      you enjoy it today you can do it again tomorrow.