Need Help Displaying Info from DBFs

[Michael Elepano]

Sandeep:

Thanks for your advice.  I had a feeling that JOIN was not what I was
looking for.  I was hoping to avoid your solution as my EXPRESSION
statement sometimes reaches up to 2500 clauses causing MapServer, PHP,
and IIS to timeout.  My EXPRESSION statements gets too big due to the
number of counties (or FIPS codes which number over 3000) in the US.
And while some of the datasets I use only involve a handful of
counties, some other datasets involve over 2500 of the counties.

I fear my only recourse is to manually join each dataset to my
county.dbf and build simple EXPRESSIONs

e.g. EXPRESSION ([RAINFALL] > 8)

rather than complex ones

e.g. EXPRESSION (([FIPS] = 00022) OR ([FIPS] = 00042) OR ([FIPS] =
00081) ... ad nauseum)).

If this is my only recourse, can anyone suggest a good dbf
editor/joiner?  Or is there any good php script that can handle this?
It doesn't seem that the PHP/DBF module can do this out-of-box.

Thanks to all for your help.

Michael

On Jan 16, 2005, at 11:44 PM, Sandeep Bashyal wrote:

> 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
>