[postgis-users] Problems with PHP and PostgreSQL/PostGIS

Fri Apr 13 06:52:29 PDT 2007

Yap that should do it.

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Gerald Baumgartner
Sent: Friday, April 13, 2007 9:39 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Problems with PHP and PostgreSQL/PostGIS

Yes, now I can see the old name in the table!
And now I had just to alter the table entry to "fahrtenbuchtabelle", or?

2007/4/13, Obe, Regina < robe.dnd at cityofboston.gov
<mailto:robe.dnd at cityofboston.gov> >: 

	Strange.  I assumed you had created your geometry field with
AddGeometryColumn and that command puts an entry in the geometry_columns
table.  Do you see any records in your geometry_columns table?

	There should be records with your old table name in there since
I don't think postgis is smart enough to correct the entry when you
rename a table.

	Try running this select command

	SELECT * from geometry_columns where lower(f_table_name) =
'fahrtenbuchtabelle'

	That should bring up entries for both the old and the new name.

________________________________

	From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Gerald Baumgartner
	Sent: Friday, April 13, 2007 9:22 AM 

	To: PostGIS Users Discussion
	Subject: Re: [postgis-users] Problems with PHP and
PostgreSQL/PostGIS

	where should I change an entry in geometry_columns table? I
can't see there anything about the table I had changed.

	Gerald

	2007/4/13, Obe, Regina <robe.dnd at cityofboston.gov>: 

		Yap that was what I was saying that I thought you might
have problems with your date.  Sorry didn't notice the $datum
declaration you had at the top.

		I think the way you format the date has to match with
your PostGreSQL date style or I think also the ISO date standard which
is YYYY-MM-DD

		and as I mentioned you need to quote your date field in
your sql so your sql should be

		INSERT INTO
fahrtenbuchtabelle(fid,date_start_fahrt,date_insert,strecke) VALUES
($z_eintraege,'$datum','$datum',GeometryFromText('LINESTRING($breitengra
d1 $laengengrad1, $breitengrad $laengengrad)',4326))"; 

		I also tend to go with the ISO date standard - less
headache since it is not ambiguous like the other formats.  So your
$datum would be declared

		$datum = date("Y-m-d", $timestamp);

		Also since you renamed your table, make sure to change
the entry in geometry_columns table if it is not correct otherwise you
may run into issues displaying the data.

		Hope that helps,
		Regina

________________________________

		From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Gerald Baumgartner
		Sent: Friday, April 13, 2007 8:55 AM
		To: PostGIS Users Discussion
		Subject: Re: [postgis-users] Problems with PHP and
PostgreSQL/PostGIS

		I have renamed the tablename, but now there are other
errors:

		Warning: pg_query() [function.pg-query
<http://localhost/GpsDaten/function.pg-query> ]: Query failed: ERROR:
column "date_start_fahrt" is of type date but expression is of type
integer at character 36 HINT: You will need to rewrite or cast the
expression. in C:\ms4w\Apache\htdocs\GpsDaten\GPS_Daten_einlesen.php on
line 103

		Warning: pg_query() [function.pg-query
<http://localhost/GpsDaten/function.pg-query> ]: Query failed: ERROR:
invalid input syntax for type numeric: "" in
C:\ms4w\Apache\htdocs\GpsDaten\GPS_Daten_einlesen.php on line 112

		php Code:

		    $timestamp=time();
		    $datum=date("d/m/y",$timestamp);

		    if($z_eintraege==2)
		    {
		       $sql_befehl="INSERT INTO
fahrtenbuchtabelle(fid,date_start_fahrt,date_insert,strecke) VALUES
($z_eintraege,$datum,$datum,GeometryFromText('LINESTRING($breitengrad1
$laengengrad1, $breitengrad $laengengrad)',4326))"; 
		       $res=pg_query($connection,$sql_befehl);
		       $sql_befehl_select="SELECT max(FID) as fid from
fahrtenbuchtabelle";

$res_fid=pg_query($connection,$sql_befehl_select);
		       $fahrtnummer=pg_result($res_fid,"fid"); 

		    }
		    if($z_eintraege>2)
		    {
		      $sql_befehl_append="UPDATE fahrtenbuchtabelle SET
strecke=AddPoint(strecke,GeomFromText('POINT($breitengrad
$laengengrad)',4326)) where fid='$fahrtnummer'"; 
		      $result=pg_query($connection,$sql_befehl_append);
		    }

		Gerald

		2007/4/13, Obe, Regina <robe.dnd at cityofboston.gov>: 

			When you created your table it appears you
created it as FahrtenbuchTabelle, but in your PHP sql, you don't have
the table name quoted, it will take what you have and insert into a
table called fahrtenbuchtabelle.  All table names that are not quoted in
sql will be lowercased when passed to the processor.

			To minimize on headache, you really should
rename your table in PostgreSQL to fahrtenbuchtabelle.  Note that
PostgreSQL table names are case sensitive so FahrtenbuchTabelle  and
fahrtenbuchtabelle are not the same name.

			Other possibly potential problem, I can't tell
how you are passing in your $datum date, but may need to be quoted.

			Hope that helps,
			Regina

________________________________

			From:
postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Gerald Baumgartner
			Sent: Friday, April 13, 2007 8:14 AM
			To: postgis-users at postgis.refractions.net
			Subject: [postgis-users] Problems with PHP and
PostgreSQL/PostGIS

			Hi everyone!

			I have created a table and now I want to put a
GPS Logfile into this table with a php file. But the php file doesn't do
the inserts.

			Please can someone help me!

			The error message from the php file in firefox:

			Warning: pg_query() [function.pg-query
<http://localhost/GpsDaten/function.pg-query> ]: Query failed: ERROR:
relation "fahrtenbuchtabelle" does not exist in
C:\ms4w\Apache\htdocs\GpsDaten\GPS_Daten_einlesen.php on line 103

			Here are the commands in the php file:

			    if($z_eintraege==2)
			    {
			       $sql_befehl="INSERT INTO
FahrtenbuchTabelle(FID,Date_Start_Fahrt,Date_Insert,Strecke) VALUES
($z_eintraege,$datum,$datum,GeometryFromText('LINESTRING($breitengrad1
$laengengrad1,    $breitengrad $laengengrad)',4326))"; 
			       $res=pg_query($connection,$sql_befehl);
			       $sql_befehl_select="SELECT max(FID) as
fid from FahrtenbuchTabelle";

$fahrtnummer=pg_query($connection,$sql_befehl_select);

			    } 
			    if($z_eintraege>2)
			    {
			      $sql_befehl_append="UPDATE
FahrtenbuchTabelle SET
Strecke=AddPoint(Strecke,GeomFromText('POINT($breitengrad
$laengengrad)',4326)) where FID='$fahrtnummer'"; 

$result=pg_query($connection,$sql_befehl_append);
			    }

			And there is my table in postgresql/postgis:

			CREATE TABLE "FahrtenbuchTabelle"
			(
			  "FID" numeric NOT NULL, 
			  "Date_Start_Fahrt" date NOT NULL,
			  "Date_Insert" date NOT NULL,
			  "Strecke" geometry,
			  CONSTRAINT "FahrtID" PRIMARY KEY ("FID"),
			  CONSTRAINT "enforce_dims_Strecke" CHECK
(ndims("Strecke") = 2), 
			  CONSTRAINT "enforce_geotype_Strecke" CHECK
(geometrytype("Strecke") = 'LINESTRING'::text OR "Strecke" IS NULL),
			  CONSTRAINT "enforce_srid_Strecke" CHECK
(srid("Strecke") = 4326) 
			) 
			WITHOUT OIDS;
			ALTER TABLE "FahrtenbuchTabelle" OWNER TO
postgres;

			Already thankful for any ideas.

________________________________

			The substance of this message, including any
attachments, may be
			confidential, legally privileged and/or exempt
from disclosure
			pursuant to Massachusetts law. It is intended
solely for the
			addressee. If you received this in error, please
contact the sender
			and delete the material from any computer.

			_______________________________________________
			postgis-users mailing list
			postgis-users at postgis.refractions.net 

http://postgis.refractions.net/mailman/listinfo/postgis-users

		_______________________________________________
		postgis-users mailing list
		postgis-users at postgis.refractions.net 

http://postgis.refractions.net/mailman/listinfo/postgis-users

	_______________________________________________
	postgis-users mailing list
	postgis-users at postgis.refractions.net 
	http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070413/55f1c0d4/attachment.html>