[Mapserver-users] ODBC connection data entry

[Lionel Bargeot]

Le mer 12/02/2003 à 18:35, randy james a écrit :
> Hi
Hi,
> 
> I am just starting to look at postgis, and my SQL
> knowledge is somewhat limited, so i thought it would
> be a good idea to ask before i begin developing.
> 
> I am trying to set-up postgis for data entry from
> Microsoft Access through the postgresql ODBC driver. I
> was wondering if it is possible to convert two
> coloumns, (X and Y coodinates) to the geom column in
> the postgis enabled postgresql table? From reading the
> documentation looks like i can use the INSERT INTO and
>  FROM statements.
> 
> We have a large number of points in dbf format and i
> am looking for a easy way to load the data so it can
> be available for MapServer.

I don't know about postgis, but there is an easy way to create shapefile
on the fly from points in pgsql database. So if your points are in dbf
files, you just have to put them in a database table. You can do it via
Access and odbc driver for postgres, or you can do it in Perl.
> 
> Also since most of our staff works with MSaccess i am
> planning on using it to enter new points into the
> system so it can be available for mapserver.

I don't think there will be any problem. But, again, I'never tried to do
this with postgis.

here is a sample piece of code in PHP with mapscript module wich extract
X,Y from a postgresql table and create a shapefile on the fly. comments
are in french, sorry.

// 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=database host=localhost user=username
password='pass' port=5432 ");
$Query="select id,nom,e as x ,n as y from communes_pers LIMIT 150";
$Res = pg_exec ($conn, $Query);
$iNbr = pg_numrows($Res);
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();

Regards,
Lionel

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