[Mapserver-users] ODBC connection data entry

[Jean-Francois.Doyon at CCRS.NRCan.gc.ca]

Actually, IT WILL be a problem with PostGIS.

The issue would be that the ODBC driver for PostGreSQL isn't PostGIS aware
at all (That i know of?).

PostGIS creates new datatypes within PostgreSQL, which you couldn't make MS
Access aware of, unless Refractions was to create an updated ODBC driver
(!!) :P

Although using MS Access to enter or export data to postgresql is no
problem, this only works into "regular" tables and columns, using built-in
datatypes.

You could add points X and Y into respective columns in a pgsql table, but
you would not in anyway benefit from the use of PostGIS, and actually
couldn't read those natively from mapserver using it's postgis interface.
You would have to use a scripting language to "manually" bind your database
to mapserver, as lionel suggested.

If you're familiar enough with PostgreSQL, you could maybe create triggers
and stored procedures to convert your X,Y points from two columns into a
PostGIS point geometry type however, on the fly, as data is loaded.  This
sounds like a good alternative to me, especially if you don't want to get
into scripting.

Cheers,
J.F.

-----Original Message-----
From: Lionel Bargeot [mailto:l.bargeot at educagri.fr]
Sent: Wednesday, February 12, 2003 4:55 PM
To: randy james
Cc: jeff at refractions.net; mapserver-users at lists.gis.umn.edu
Subject: Re: [Mapserver-users] ODBC connection data entry


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


_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users