Need Help Displaying Info from DBFs

[Sandeep Bashyal]

On Fri, 14 Jan 2005 13:08:46 -0500, Michael Elepano <michael at LIVEPROCESS.COM> wrote:

> Hey:
>
> I've been using MapServer for about a month now and have gotten
> relatively far with it.  But now I've hit a brick wall.
>
> The problem I'm having is showing info from DBFs on my map of the US.
> I've got two DBFs (one with all the US counties and one with data-- say
> occurences of avalanches-- for each of those county).  I'm trying to
> color code each county according to occurences of avalanches.
>
> Each DBF has a column called FIPS.  The avalanche DBF has a column
> called COUNT that is the basis for my colors.  I can use EXPRESSION to
> single out any column in my county DBF, but I can't seem to reach my
> avalanche DBF.
>
> I'm assuming I'll need to do a JOIN.  My attempt follows below.  Any
> advice would be appreciated.
>
> Michael
>
> ######################
> ## my attempt at an avalanche layer
> ######################
>         LAYER
>                 NAME "layerAvalanche"
>                 TYPE POLYGON
>                 STATUS ON
>                 DATA "counties.dbf"
>                 PROJECTION
>                         "proj=latlong"
>                         "ellps=GRS80"
>                         "datum=NAD83"
>                 END
>
>                 CLASS
>                         NAME "classGreen"
>                         OUTLINECOLOR 255 0 255
>                         COLOR 0 255 0
>                         EXPRESSION ([COUNT] > 5)
>                 END
>
>                 JOIN
>                         NAME "joinAvalanche"
>                         TABLE "C:\my\path\to\avalanches.dbf"
>                         FROM "FIPS"
>                         TO "FIPS"
>                 END
>         END
>
>
>
Michael,

As far as I understand, you want to set up classes using data from a dbf file that is not associated with the shapefile. The JOIN thing can only be used for querying and only when you are using templates for displaying query results. If you want to color code the counties using external data the simplest way is to append the fileds in from the avalanches.dbf to counties.dbf. If, for any reason this cannot be done, then you can generate classes and expressions dynamically with mapscript. Here is the php mapscript code I use to classify districts into "$no_classes" number of classes (using arcview-style quantile method) using data from an external mysql database (the external database has a field called DIST_ID that associates with the shapefile):

//run sql query
$sql = "SELECT DIST_ID, DIST_NAME, ".$databasefield." FROM DISTRICTS_".$databasetable.$filter_where;
$result = mysql_query($sql, $connection);
// assign sql results into arrays
while ($row = mysql_fetch_assoc($result)){
        $ID[]=$row["DIST_ID"];
        $DIST_NAME[]=$row["DIST_NAME"];
        ${$databasefield}[]=$row[$databasefield];
} //end while
//sort the query results
if (${$databasefield}){array_multisort(${$databasefield}, SORT_ASC, SORT_NUMERIC, $DIST_NAME, SORT_STRING, $ID, SORT_STRING);}
mysql_free_result($result);
mysql_close($connection);
//count the number of records in the array
$no_records=count($ID);
if ($no_classes > $no_records){$no_classes = $no_records;}
if ($no_classes > 0){
        //number of class items
        $no_classitems = ceil($no_records / $no_classes);
        //split the arrays into chunks
        $class_ID=array_chunk($ID,$no_classitems,true);
        $class_value=array_chunk(${$databasefield},$no_classitems,true);
} // end if $no_classes > 0
//set classitem
$layer_districts->set("classitem","OBJID");
//draw classes
$i=0;
while ($i < $no_classes){
        $i++;
        //class expressions
        $class_array = $class_ID[$i-1];
        if ($class_array){
                $expression[$i]="/".implode("|",$class_array)."/";
                //legend key labels
                $class_legend_key1=$class_value[$i-1][($i-1)*$no_classitems];
                $class_legend_key2=$class_value[$i-1][($i-1)*$no_classitems + $no_classitems -1];
                if ($class_legend_key2 == NULL){$class_legend_key2 = $class_value[$i-1][$no_records-1];}
                $class_legend_key[$i]=$class_legend_key1." to ".$class_legend_key2;
                //draw layers
                $class[$i] = ms_newClassObj($layer_districts);
                $class[$i]->set("name",$class_legend_key[$i]);
                $class[$i]->setexpression($expression[$i]);
                $class_style[$i] = ms_newStyleObj ($class[$i]);
                $class_style[$i]->outlinecolor->setRGB(-1,-1,-1);
                $class_style[$i]->color->setRGB($color_r[$i-1],$color_g[$i-1],$color_b[$i-1]);
        } // end if $class_array
}//end draw classes


--
Sandeep Bashyal
--
   National GIS Officer
   UNCU, United Nations Nepal
   UN House, Pulchowk, G.P.O. Box 107, Kathmandu, Nepal
   Tel: (+977) 01 5543957, (+977) 01 5554304
   Cell:(+977) 9841270854,  Fax: (+977) 01 5528059
------------------------------------------------------
   mailto:gis at undphais.org.np
   mailto:sandeep at earthmapping.com
   http://www.un.org.np