[postgis-users] Problems with PHP and PostgreSQL/PostGIS
Gerald Baumgartner
baumgartner.gerald at gmail.com
Fri Apr 13 07:06:45 PDT 2007
OK, I have done it.
Thanks for your help.
Gerald
2007/4/13, Obe, Regina <robe.dnd at cityofboston.gov>:
>
> 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>:
> >
> > 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
> >
> >
>
> _______________________________________________
> 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/2cd05b78/attachment.html>
More information about the postgis-users
mailing list