[mapserver-users] query points from database

[Homme Zwaagstra]

Joost,

> Is it possible to display points from a database?  If so, how can they be 
> queried for thier attributes?

You can do this using PostGIS/PostgreSQL and CGI mapserver. An alternative approach that 
gives you lots of flexibility, however, would be to use MapScript in conjunction with your 
database. The following example uses PHP/MapScript and the MySQL database. It only uses 
one attribute (id) but by altering your query and placing the additional attributes in 
the $points array you can get as much info as you want. Note that for clarity's sake I've 
removed most of the error checking from the script.

$points = array(); # associative array to contain the point data
$dblink = mysql_pconnect("localhost", "user", "pass"); # connect to MySQL database
mysql_select_db("your_database"); # select database
$result = mysql_query('SELECT id,x,y FROM table'); # query database for x and y data
if (mysql_affected_rows($dblink) > 0) { # check to see if query produced output and 
complain if it didn't
	#iterate through results placing x y data in $points and associating it with 
attribute id
	while (($row = mysql_fetch_array($result, MYSQL_ASSOC))) {
		$points[$row['id']]['x'] = $row['x'];
		$points[$row['id']]['y'] = $row['y'];
	}
} else {
	die ('Query produced no output');
}
		
$mapObj = ms_newMapObj('yourmapfile.map'); # create a map object using your mapfile
$imageObj = $mapObj->draw(); # obtain an image object from the map
$layerObj = $mapObj->getLayerByName('points'); # get a layer object representing your point 
layer (see mapfile snippet)
$classObj = $layerObj->getClass(0); # get a class object representing the first class in 
the layer
$pointObj = ms_newPointObj(); # create a new point object to hold the X Y data

# iterate through the point data retreived from the database
foreach ($points as $id => $coordinates) {
	$pointObj->setXY($coordinates['x'], $coordinates['y']); # set the X Y values of the 
point object
	$pointObj->draw($mapObj, $layerObj, $imageObj, 0, $id); # draw the point on the 
image, using the id attribute to label it
}

$mapObj->drawLabelCache($imageObj); # draw the labels
$img_url = $imageObj->saveWebImage(MS_PNG, MS_FALSE, MS_FALSE, -1); # save the image to a 
PNG file and obtain the url

print '<img src="'.$img_url.'" alt="Mapserver output" />'; # output the url for the image

?>

# Mapfile snippet of points layer used in above code
LAYER
	NAME points
	TYPE POINT
	STATUS ON
	LABELCACHE ON
	CLASS
		OUTLINECOLOR 255 89 89
		COLOR 255 136 136
		SYMBOL 'circle'
		SIZE 8
		LABEL
			ANTIALIAS true
			FONT verdana
			TYPE truetype
			SIZE 8
			COLOR 10 10 153
			OUTLINECOLOR 199 216 252
			POSITION auto
		END
	END
END


Good luck!

Homme Zwaagstra