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

Fri Apr 13 06:30:15 PDT 2007

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

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