[Mapserver-users] SQL Server, Data entry

[Bargeot Lionel]

Le mar 11/03/2003 à 20:54, bugbug at shaw.ca a écrit :
> Hi Im a newbie to the GIS world in general and I was hoping someone could
> point me in the right direction.
Hi Dennis
> 
> The data basically consists of water wells within a region( Province of
> Nova Scotia) . I have access to shape files for the region( county
> borders..etc) 
> 
> Each record in my database has location values allowing the well to be
> mapped( I guess). 
> 
> Im guessing I can convert the sql tables to text then use gen2shp or
> something by scheduling a task to call a script nightly to create a point
> shape file from the coordinates in the sql database( no idea if thats how
> one should do it). Combine that with the county/province shape files to
> display on a map and query using mapserver( havent tried out yet). I have
> no idea if Im even headed in the right direction. I need to be able to
> add/change and delete wells on the fly. So just being able to read and
> query shapefiles wont be good enough as new wells are being added by users
> all the time.

We developped a method to create shapefile with point features on the
fly. You need phpmapscript, dbase php module. Then you will be able to
query your database, create .shp and .dbf files on the fly.
This method is quiet interessant because you can put all what you need
in dbf fields (item for labeling, item for symbolsize etc ...). You can
then calculate on the fly any parameters you need with a SQL query.
We use postgres but I'm sure PHP supports SQLserver.
> 
> It sure be cool to allow users to visually/spatial query the SQL database
> AND have that visual data updated via the updated data in the sql
> database. Is mapserver the way to go here? Will I be able to do what I am
> describing ? Im I limited to just displaying static data in premade shape
> files ? Can I use mapserver with Sql Server and asp ?

You could find the thread I wrote in the ML archive.
> 
> Im completely new to GIS, Maps..shapefiles..etc.  Any guidance would be
> much appreciated.
You will need minimal knowledge about shapefiles, but I think it's quiet
easy.
Here is a piece of code that query a database, create points in the
shapefile and insert a record in the associate dbf file.
All dbf/shp files are temporary.

Sorry, Comments are in french
---------------------------------------------
/* Création dynamique des points
principe : 
- on exécute la requête sur la base de données sémantique
- on en sort une liste de points avec leurs coordonnées X Y
- on créé un shapefile dans le repertoire data/tmp de ces points
- on créé manuellement le dbf parcequ'il ne le fait pas automatiquement
	- on créé sa structure et on enregistre
	- on rouvre en rw le fichier
	- on insert les records
- on créé un layer à partir du shapefile qu'on vient de créer
- on créé une classe pour l'affichage
- on peut utiliser les champs du dbf pour les labels par exemple
mais aussi pour les symbols

- avantage : c'est bcp plus rapide que de passer par la méthode
setExpression 
de la classe "class"
*/
// On ouvre un shapefile unique de points
$shapetempname="tmp/tmp".time();
$shapefile_temp=ms_newShapefileObj("../data/$shapetempname",
MS_SHP_POINT);

//création d'un fichier dbase
// son nom
$dbname="../data/$shapetempname".".dbf";
// sa structure
$def= array(array("nom", "C", 20),array("uai_cdn", "N", 15, 0));
// création proprement dite de sa structure
$dbid=dbase_create("$dbname",
$def);dbase_pack($dbid);dbase_close($dbid);
// réouverture du fichier en rw
$dbidrw=dbase_open("$dbname", 2);

//connection à la B2D et extraction des X,Y
$conn = pg_connect("dbname=dbname host=localhost user=user
password='mypass' port=5432 ");
$Query="select id,nom,e as x ,n as y from communes_pers LIMIT 150";
echo $Query;
$Res = pg_exec ($conn, $Query);
$iNbr = pg_numrows($Res);
echo "<BR>$iNbr enregistrements<BR>";
for ($i = 0; $i < $iNbr; $i++) {
	$Lign = pg_fetch_array($Res, $i); 
	$pointobj = ms_newPointObj();
	$pointobj->setXY($Lign["x"],$Lign["y"],1);
	$shapefile_temp->addPoint($pointobj);
	$pointobj->free();
	$valuedbf=array($Lign["nom"],$Lign["id"]);
	$test=dbase_add_record($dbidrw, $valuedbf);
}
dbase_pack($dbidrw);
dbase_close($dbidrw);
// On écrit le shapefile
$shapefile_temp->free();
-----------------------------
Then you can use your new shapefile with classical phpmapscript commands
to display the data you need 

> 
> Thanks
> Dennis

Bye 
Lionel




-- 
AgrogeomaTIC
CNERTA-ENESAD, 4 rue champs-prevois, batiment grand-champs, 21000 Dijon
03.80.77.28.49
http://stb.educagri.fr