[postgis-users] Problems with PHP and PostgreSQL/PostGIS
Gerald Baumgartner
baumgartner.gerald at gmail.com
Fri Apr 13 06:38:45 PDT 2007
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>:
>
> 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($breitengrad1
> > $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/e9af28f7/attachment.html>
More information about the postgis-users
mailing list