Integrating attribute data from relational databases with MapServer

[Stephen Lime]

Here are a couple of ideas using mapscript:

Assuming a key between the xbase file and the database you could do the
query and store all results in a hash based on the key, then you could step
through the shapefile pulling the classification value from the hash based
on the current shapes key value, now draw the shape. You would probably
draw ancillirary layers before and after.

Another method would be to create a temp shapefile. Assuming you can
produce
a temporary xbase file (remember order MUST match that of the shapefile)
you
could pair that file with a copy of the .shp/.shx county data and draw the
layer
as normal. You need to put a dummy layer in the map file so it's in the
right order
and then edit it to match the temp data. Then a simple $map->draw() would
be
possible. Hard part is keeping the data in sync. You can use the sortshp
utility
to order the counties by some common field (i.e. county code) and then use
an ORDER BY in the SQL statement.

There may others but off the top of my head this is what I thought of. Both
should
work with fairly minimal coding.

Steve

Stephen Lime
Internet Applications Analyst

Minnesota DNR
500 Lafayette Road
St. Paul, MN 55155
651-297-2937

>>> Vinod Chettur <chetturv at sdhu.moh.gov.on.ca> 08/10/00 12:39PM >>>

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.

Thanks, Vinod

> -----Original Message-----
> From:   Stephen Lime [SMTP:steve.lime at dnr.state.mn.us]
> Sent:   Thursday, August 10, 2000 12:00 PM
> To:     Vinod Chettur
> Subject:     Re: Integrating attribute data from relational databases
> with MapServer
>
> Do you want to map the rates or display the rates summaries based on
> a users interaction with a map. Depending on what exactly you'd like to
> do there are different stratagies you can use.
>
> Steve
>
> Stephen Lime
> Internet Applications Analyst
>
> Minnesota DNR
> 500 Lafayette Road
> St. Paul, MN 55155
> 651-297-2937
>
> >>> Vinod Chettur <chetturv at sdhu.moh.gov.on.ca> 08/10/00 10:12AM >>>
>    Hello everyone:
>    Just wondering about the state of things with regards to relational
> database integration with mapserver. I am currently invoking a bunch of
> stored SQL procedures to generate tabular reports of statistical
> computations done at the database server (NT 4.0 /SQL Server). I hope to
> be
> able to represent these statistical rates for various counties in Ontario
> using MapServer. Wondering if any pointers or examples are out there
> perhaps
> with the new PHP module or otherwise.
>
>    Thanks in advance,
>    Vinod Chettur
>    Senior Systems Consultant, NHIP
>    Canada
>