Need Help Displaying Info from DBFs
Sean Gillies
sgillies at FRII.COM
Tue Jan 18 11:48:38 EST 2005
Michael,
As I see it, there are 3 solutions to your problem:
1) SYSTEM SOLUTION
Create a temporary shapefile with a feature table (.dbf) that is
joined. This temp dataset could be produced at daily or hourly
intervals and could stay fairly up to date. You could readily script
this in Perl (using DBI and XBase) or Python (using shapelib) and run
it under cron. No need for PHP.
2) SOFTWARE SOLUTION
You can do your classification in PHP code outside of mapscript. This
means setting the classindex and label attributes for all the county
shapes, then adding them to an inline layer using "addFeature()". They
will be rendered according to the class at the specified classindex.
3) RDBMS SOLUTION
Consider using PostGIS/SDE/Oracle to store your counties and avalanche
data and take advantage of the RDBMS ability to perform joins and cache
query results.
cheers,
Sean
On Jan 18, 2005, at 9:20 AM, Michael Elepano wrote:
> 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
>>
>
>
--
Sean Gillies
sgillies at frii dot com
http://users.frii.com/sgillies
More information about the mapserver-users
mailing list