[postgis] PostgreSql point to Postgis point conversion

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

[postgis] PostgreSql point to Postgis point conversion

M Vermeij
I have a database which contains some point fields. I would like to
convert these fields to PostGis point fields so that they can be
indexed. I can create the PostGis fields but would like to fill them
with the data from the PostgreSql fields, if possible via an UPDATE
or similar command. Is this possible?


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Universal Inkjet Refill Kit $29.95
Refill any ink cartridge for less!
Includes black and color ink.
http://us.click.yahoo.com/r9F0cB/MkNDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] PostgreSql point to Postgis point conversion

TONY J.Y.
Yes. I think you can use any of the programming language
to UPDATE each line, such as C, java, or PLpgSQL.


----- Original Message -----
From: "mj_vermeij" <[hidden email]>
To: <[hidden email]>
Sent: Friday, November 16, 2001 5:43 AM
Subject: [postgis] PostgreSql point to Postgis point conversion


> I have a database which contains some point fields. I would like to
> convert these fields to PostGis point fields so that they can be
> indexed. I can create the PostGis fields but would like to fill them
> with the data from the PostgreSql fields, if possible via an UPDATE
> or similar command. Is this possible?
>
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Universal Inkjet Refill Kit $29.95
Refill any ink cartridge for less!
Includes black and color ink.
http://us.click.yahoo.com/bAmslD/MkNDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] PostgreSql point to Postgis point conversion

Paul Ramsey-2
Tony is right, if you want to do it inside the db, you'll have to script
up a converter in one of the internal languages. If this is just a
one-shot deal conversion, it is probably far simpler to dump the table
to text, run a perl process on it to modify the geometry entries, and
then upload the table again.

"TONY J.Y." wrote:

>
> Yes. I think you can use any of the programming language
> to UPDATE each line, such as C, java, or PLpgSQL.
>
> ----- Original Message -----
> From: <[hidden email]>
> To: <[hidden email]>
> Sent: Friday, November 16, 2001 5:43 AM
> Subject: [postgis] PostgreSql point to Postgis point conversion
>
> > I have a database which contains some point fields. I would like to
> > convert these fields to PostGis point fields so that they can be
> > indexed. I can create the PostGis fields but would like to fill them
> > with the data from the PostgreSql fields, if possible via an UPDATE
> > or similar command. Is this possible?
> >
> >
> >
> > To unsubscribe from this group, send an email to:
> > [hidden email]
> >
> >
> >
> > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
> >
> >
> >
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

--
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: [hidden email]
     | Phone: (250) 885-0632
     \_

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] PostgreSql point to Postgis point conversion

David Blasby-3
Here's the easiest way to do it - just use postgresql's builtin TEXT
type to create the WKT:

t1=# create table a (x float, y float, g geometry);
                ....

t1=# select * from a;
 x  | y  | g
----+----+---
  1 |  2 |
 11 | 22 |
(2 rows)


To make these into points, we make some WKT (|| is the concat operator):


t1=# select 'POINT(' || x || ' ' ||y||')' from a;
   ?column?  
--------------
 POINT(1 2)
 POINT(11 22)
(2 rows)

So,

t1=# update a set g = geometryfromtext(('POINT(' || x || ' '
||y||')')::geometry,123);
t1=# select * from a;
 x  | y  |           g          
----+----+-----------------------
  1 |  2 | SRID=123;POINT(1 2)
 11 | 22 | SRID=123;POINT(11 22)
(2 rows)


dave

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Universal Inkjet Refill Kit $29.95
Refill any ink cartridge for less!
Includes black and color ink.
http://us.click.yahoo.com/r9F0cB/MkNDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] PostgreSql point to Postgis point conversion

M Vermeij
I am trying to use the :

update a set g = geometryfromtext(('POINT(' ||
> x || ' '
> ||y||')')::geometry,123);
 you mentioned. However instead of you're x and y
columns I would like to use the x and y coordinates
which are already stored in a postgres point field,
but can't seem to retrieve them seperately.

Question:
If the postgres point field is called 'location' what
whould the UPDATE command then be? ('location2' is the
PostGis point field.)

UPDATE table1 set location2 =geometryfromtext((
???????    )::geometry,-1);




--- Dave Blasby <[hidden email]> wrote:

> Here's the easiest way to do it - just use
> postgresql's builtin TEXT
> type to create the WKT:
>
> t1=# create table a (x float, y float, g geometry);
> ....
>
> t1=# select * from a;
>  x  | y  | g
> ----+----+---
>   1 |  2 |
>  11 | 22 |
> (2 rows)
>
>
> To make these into points, we make some WKT (|| is
> the concat operator):
>
>
> t1=# select 'POINT(' || x || ' ' ||y||')' from a;
>    ?column?  
> --------------
>  POINT(1 2)
>  POINT(11 22)
> (2 rows)
>
> So,
>
> t1=# update a set g = geometryfromtext(('POINT(' ||
> x || ' '
> ||y||')')::geometry,123);
> t1=# select * from a;
>  x  | y  |           g          
> ----+----+-----------------------
>   1 |  2 | SRID=123;POINT(1 2)
>  11 | 22 | SRID=123;POINT(11 22)
> (2 rows)
>
>
> dave
>


__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] PostgreSql point to Postgis point conversion

David Blasby-3
M Vermeij wrote:
>  you mentioned. However instead of you're x and y
> columns I would like to use the x and y coordinates
> which are already stored in a postgres point field,
> but can't seem to retrieve them seperately.

I looked at the postgresql documentation, and their doesnt seem to be
any way of accessing the X/Y field of the built-in geometry types.

Your best bet is to use C/Perl to write a little routine to
query/parse/update a table.  The other option would be to look at the
string handling routines available in postgresql - if you can remove the
"," you can easily re-build a WKT representation of the point.

dave

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Universal Inkjet Refill Kit $29.95
Refill any ink cartridge for less!
Includes black and color ink.
http://us.click.yahoo.com/1_Y1qC/MkNDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

RE: [postgis] PostgreSql point to Postgis point conversion

Obe, Regina     DND\MIS
In reply to this post by M Vermeij
To get the X and Y  values of a postgres sql object, you can try creating
functions in postgresql or just apply the following algorithm if its a one
shot deal.  Note I don't know how slow this would be though, but this is how
I would create the functions to compensate for the fact that postgresql
doesn't seem to have any built in.  Just check my logi

To figure out the X value of a postgres point P do

width(box(point '(0,0)', P)*(CASE WHEN (P >> point '(0,0)') THEN 1 ELSE -1
END)

To figure out the Y value of a postgres point P do

height(box(point '(0,0)', P)* (CASE WHEN P >^ point '(0,0)') THEN 1 ELSE -1
END)


So your update statement if you were updating a table would look like this
(I'm assuming your point field is called location and you will store the x
and y corrdinates in locationx locationy

UPDATE table1
        SET locationx = width(box(point '(0,0)', location)*(CASE WHEN
(location >> point '(0,0)') THEN 1 ELSE -1 END) ,
                locationy = height(box(point '(0,0)', location)* (CASE WHEN
location >^ point '(0,0)') THEN 1 ELSE -1 END)



The property I am using here (which is probably convoluted - but kind of
makes sense to me thinking about it) is that
If you create a box from the origin (0,0) to a point, then the width of that
box represents the absolute value of the x coordinate of that point.  To
find the sign of x - you then ask is the point to the right or left of the
origin using >> -- if to the right then it is positive otherwise it is
negative

Similarly to find the y coordinate of that point. It would be the height of
the bounded box (then check if it is above or below origin to figure out the
sign of it).









-----Original Message-----
From: M Vermeij [mailto:[hidden email]]
Sent: Monday, November 26, 2001 8:42 AM
To: [hidden email]
Subject: Re: [postgis] PostgreSql point to Postgis point conversion


I am trying to use the :

update a set g = geometryfromtext(('POINT(' ||
> x || ' '
> ||y||')')::geometry,123);
 you mentioned. However instead of you're x and y
columns I would like to use the x and y coordinates
which are already stored in a postgres point field,
but can't seem to retrieve them seperately.

Question:
If the postgres point field is called 'location' what
whould the UPDATE command then be? ('location2' is the
PostGis point field.)

UPDATE table1 set location2 =geometryfromtext((
???????    )::geometry,-1);




--- Dave Blasby <[hidden email]> wrote:

> Here's the easiest way to do it - just use
> postgresql's builtin TEXT
> type to create the WKT:
>
> t1=# create table a (x float, y float, g geometry);
> ....
>
> t1=# select * from a;
>  x  | y  | g
> ----+----+---
>   1 |  2 |
>  11 | 22 |
> (2 rows)
>
>
> To make these into points, we make some WKT (|| is
> the concat operator):
>
>
> t1=# select 'POINT(' || x || ' ' ||y||')' from a;
>    ?column?  
> --------------
>  POINT(1 2)
>  POINT(11 22)
> (2 rows)
>
> So,
>
> t1=# update a set g = geometryfromtext(('POINT(' ||
> x || ' '
> ||y||')')::geometry,123);
> t1=# select * from a;
>  x  | y  |           g          
> ----+----+-----------------------
>   1 |  2 | SRID=123;POINT(1 2)
>  11 | 22 | SRID=123;POINT(11 22)
> (2 rows)
>
>
> dave
>


__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 


------------------------ Yahoo! Groups Sponsor ---------------------~-->
E-mail viral marketing - with FastTree
Email to 50. You might reach 500.
Unlimited use and tracking, $20/mo.
http://www.fasttree.com/s/11.htm
http://us.click.yahoo.com/UGVLpD/MJRDAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

[postgis] Is there any solution to exceed the limit of PGLIB

TONY J.Y.

I am using the PGLIB C lib to connect to PgSql to finish the update of
the GEOMETRY of a table.

At first, everything is OK. I can insert, update, delete and select of
whatever I want.

However, I got a fatal running error.  There is an error when my SQL
string length exceeds 8190 Bytes.

Obviously, this is not the limits of the PgSQL itsself. It is the PGLIB.

Is there any solution or suggestion?

Thanks.



------------------------ Yahoo! Groups Sponsor ---------------------~-->
E-mail viral marketing - with FastTree
Email to 50. You might reach 500.
Unlimited use and tracking, $20/mo.
http://www.fasttree.com/s/11.htm
http://us.click.yahoo.com/UGVLpD/MJRDAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Is there any solution to exceed the limit of PGLIB

David Blasby-3
"TONY J.Y." wrote:
>
> I am using the PGLIB C lib to connect to PgSql to finish the update of
> the GEOMETRY of a table.
>
> At first, everything is OK. I can insert, update, delete and select of
> whatever I want.
>
> However, I got a fatal running error.  There is an error when my SQL
> string length exceeds 8190 Bytes.

I couldnt reproduce your error, I wrote a simple program that uses libpq
to insert large strings into a table.  Even with queries larger than
20,000 characters, I got no errors.

#include "libpq-fe.h"
#include <stdio.h>
#include <string.h>

        PGconn *conn;
        PGresult *rs;


// check the connection string for your database
//  create table testt (mytext text);
//  cc test.c -lpq -I/data3/postgresql-7.1.2/include -g
//  a.out
//  select length(mytext) from testt;


main()
{
        char sql[50000];
        char mytext[50000];
        char temp[100];
        int t;

                //connect to DB

        conn = PQconnectdb("host=localhost user=postgres port=5555 dbname=t1");

        if (PQstatus(conn) == CONNECTION_BAD)
        {
                printf ("couldnt connect to postgresql\n");
                exit (-1);
        }


        // build a long (but boring) string

        mytext[0] = 0;

        for (t=0;t<4000;t++)
        {
                sprintf(temp,"-%i-",t);
                strcat(mytext,temp);
        }


        printf("about to insert a string of length %i\n", strlen(mytext));

                // create SQL statement
        sprintf(sql,"INSERT INTO testt values ('%s');", mytext);

                // execute the statement
        rs = PQexec(conn,sql);

                // did it work?
        if (PQresultStatus(rs) != PGRES_COMMAND_OK)
        {
                printf ("an error occured in the sql insert statement:\n");
                printf ("%s\n", PQerrorMessage(conn) );
                exit (-1);
        }
                //clean up
        PQclear(rs);  // should close the connection too
        printf("worked ok!\n");
}

------------------------ Yahoo! Groups Sponsor ---------------------~-->
E-mail viral marketing - with FastTree
Email to 50. You might reach 500.
Unlimited use and tracking, $20/mo.
http://www.fasttree.com/s/11.htm
http://us.click.yahoo.com/UGVLpD/MJRDAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Is there any solution to exceed the limit of PGLIB

Paul Ramsey-2
Is it possible the libpq you are using, Tony, is precompiled with a
restriction? 8K is sometimes a restriction in the ODBC driver, but I've
never seen a restriction in libpq itself.

Dave Blasby wrote:

>
> "TONY J.Y." wrote:
> >
> > I am using the PGLIB C lib to connect to PgSql to finish the update of
> > the GEOMETRY of a table.
> >
> > At first, everything is OK. I can insert, update, delete and select of
> > whatever I want.
> >
> > However, I got a fatal running error.  There is an error when my SQL
> > string length exceeds 8190 Bytes.
>
> I couldnt reproduce your error, I wrote a simple program that uses libpq
> to insert large strings into a table.  Even with queries larger than
> 20,000 characters, I got no errors.
>
> #include "libpq-fe.h"
> #include <stdio.h>
> #include <string.h>
>
>         PGconn  *conn;
>         PGresult        *rs;
>
> // check the connection string for your database
> //  create table testt (mytext text);
> //  cc test.c -lpq -I/data3/postgresql-7.1.2/include -g
> //  a.out
> //  select length(mytext) from testt;
>
> main()
> {
>         char    sql[50000];
>         char    mytext[50000];
>         char    temp[100];
>         int t;
>
>                 //connect to DB
>
>         conn = PQconnectdb("host=localhost user=postgres port=5555 dbname=t1");
>
>         if (PQstatus(conn) == CONNECTION_BAD)
>         {
>                 printf ("couldnt connect to postgresql\n");
>                 exit (-1);
>         }
>
>         // build a long (but boring) string
>
>         mytext[0] = 0;
>
>         for (t=0;t<4000;t++)
>         {
>                 sprintf(temp,"-%i-",t);
>                 strcat(mytext,temp);
>         }
>
>         printf("about to insert a string of length %i\n", strlen(mytext));
>
>                 // create SQL statement
>         sprintf(sql,"INSERT INTO testt values ('%s');", mytext);
>
>                 // execute the statement
>         rs = PQexec(conn,sql);
>
>                 // did it work?
>         if (PQresultStatus(rs) != PGRES_COMMAND_OK)
>         {
>                 printf ("an error occured in the sql insert statement:\n");
>                 printf ("%s\n", PQerrorMessage(conn) );
>                 exit (-1);
>         }
>                 //clean up
>         PQclear(rs);  // should close the connection too
>         printf("worked ok!\n");
> }
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

--
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: [hidden email]
     | Phone: (250) 885-0632
     \_

------------------------ Yahoo! Groups Sponsor ---------------------~-->
E-mail viral marketing - with FastTree
Email to 50. You might reach 500.
Unlimited use and tracking, $20/mo.
http://www.fasttree.com/s/11.htm
http://us.click.yahoo.com/UGVLpD/MJRDAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

[postgis] Why is there the limitation of PGLIB to get the cursor?

TONY J.Y.
In reply to this post by TONY J.Y.
Hi,

I am using the PGLIB C lib to connect to PgSql to finish the query of
the GEOMETRY of a table.

At first, everything is OK. I can insert, update, delete and select of
whatever I want. But the geometry data is very small one.

So I wanted to try a bigger one. I uploaded a ESRI shape file into database
which in the attachment --- Canada.sql. Then I try the query.

    res = PQexec(conn, "DECLARE mycursor BINARY CURSOR FOR select gid, name,
asbinary(the_geom) from canada");

    if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR command failed\n");
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in mycursor");
    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL command didn't return tuples
properly\n");
        PQclear(res);
        exit_nicely(conn);
    }


Then I got the error when try to "FETCH ALL in mycursor".

Could you help me to figure out the problem? Thanks. I have attached
the files needed for you.


Best regards,
Tony

---
Tony Jiang, MSc
Research & Development Programmer/Analyst
MRF Geosystems Corp (www.mrf.com)
Suite 700, 665 8th Street SW,
Calgary, AB T2P 3K7
(403) 216-5515 ext:226




------------------------ Yahoo! Groups Sponsor ---------------------~-->
See What You've Been Missing!
Amazing Wireless Video Camera.
Click here
http://us.click.yahoo.com/75YKVC/7.PDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 


canada.sql (630K) Download Attachment
main.cpp (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Why is there the limitation of PGLIB to get the cursor?

David Blasby-3
Tony,
        I looked at your code and it looks like it should work.   I suggest you
open a "psql" session to the DB and type in all the commands that your
program sends to the database.  When you execute the "FETCH ALL"
command, you should get an error message.

You could also call the "char *PQerrorMessage(const PGconn* conn);"
function after the "FETCH ALL" command fails.

Hopefully the error message will guide you as to the problem.  

The binary cursor is only limited by your machine's memory.

dave

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Break free. Great
American Smokeout
http://us.click.yahoo.com/3vN8tD/.pSDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Why is there the limitation of PGLIB to get the cursor?

TONY J.Y.
Dave,

          I went to the linux server where the database was located. Ran the
psql session to declare
          the big cursor and fetch all in the cursor. there was no error
there. I could declare the cursor,
          and fetched all rows from the cursor.  So PostgreSQL server-side
is OK.  The asbinary function
          is OK too.

          I ran my program at client side (Win2K Pro with libpq).  There
came the error message when the
          geom data is bigger:

         "char *PQerrorMessage(const PGconn* conn);"
          ********************************************************
                Error = pqReadData() --  read() failed: errno=0

          ********************************************************
          So there is some limit in the pqReadData() --  read() of the C
library libpq, isn't there?

../Tony

----- Original Message -----
From: "Dave Blasby" <[hidden email]>
To: <[hidden email]>
Sent: Thursday, December 06, 2001 10:42 AM
Subject: Re: [postgis] Why is there the limitation of PGLIB to get the
cursor?


> Tony,
> I looked at your code and it looks like it should work.   I suggest you
> open a "psql" session to the DB and type in all the commands that your
> program sends to the database.  When you execute the "FETCH ALL"
> command, you should get an error message.
>
> You could also call the "char *PQerrorMessage(const PGconn* conn);"
> function after the "FETCH ALL" command fails.
>
> Hopefully the error message will guide you as to the problem.
>
> The binary cursor is only limited by your machine's memory.
>
> dave
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Stop Smoking Now
Nicotrol will help
http://us.click.yahoo.com/2vN8tD/_pSDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Why is there the limitation of PGLIB to get the cursor?

Paul Ramsey-2
There is no limit in libpq in general, because psql *uses* libpq, and
you demonstrated on the linux box that it worked for you. The problem is
with your *particular* copy/version of libpq on the win2000 box. Time to
check out the source code you are working with. If you are using a
precompiled libpq, maybe now is the time to compile your own...

"TONY J.Y." wrote:

>
> Dave,
>
>           I went to the linux server where the database was located. Ran the
> psql session to declare
>           the big cursor and fetch all in the cursor. there was no error
> there. I could declare the cursor,
>           and fetched all rows from the cursor.  So PostgreSQL server-side
> is OK.  The asbinary function
>           is OK too.
>
>           I ran my program at client side (Win2K Pro with libpq).  There
> came the error message when the
>           geom data is bigger:
>
>          "char *PQerrorMessage(const PGconn* conn);"
>           ********************************************************
>                 Error = pqReadData() --  read() failed: errno=0
>
>           ********************************************************
>           So there is some limit in the pqReadData() --  read() of the C
> library libpq, isn't there?
>
> ../Tony
>
> ----- Original Message -----
> From: "Dave Blasby" <[hidden email]>
> To: <[hidden email]>
> Sent: Thursday, December 06, 2001 10:42 AM
> Subject: Re: [postgis] Why is there the limitation of PGLIB to get the
> cursor?
>
> > Tony,
> > I looked at your code and it looks like it should work.   I suggest you
> > open a "psql" session to the DB and type in all the commands that your
> > program sends to the database.  When you execute the "FETCH ALL"
> > command, you should get an error message.
> >
> > You could also call the "char *PQerrorMessage(const PGconn* conn);"
> > function after the "FETCH ALL" command fails.
> >
> > Hopefully the error message will guide you as to the problem.
> >
> > The binary cursor is only limited by your machine's memory.
> >
> > dave
> >
> >
> > To unsubscribe from this group, send an email to:
> > [hidden email]
> >
> >
> >
> > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
> >
> >
> >
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Why is there the limitation of PGLIB to get the cursor?

TONY J.Y.
Paul,

     I have never used the precompiled library libpq.

     I installed the postgressql 7.1.3. with the source-code
postgressql7.1.3source-tar.gz on my linux box.
     After that,  I expanded the  postgressql7.1.3source-tar.gz file in my
client win2k box.
     Using the      "nmake -f win32.mak"  get the win2k box Lib and DLL.

    What is your meaning for your this sentence?
        "The problem is with your *particular* copy/version of libpq on the
win2000 box."

    What I need to do to get the correct copy/version of libpq in the win2k
box?
    Looking forward to your suggestion.


.. /tony


----- Original Message -----
From: "Paul Ramsey" <[hidden email]>
To: <[hidden email]>
Sent: Friday, December 07, 2001 10:15 AM
Subject: Re: [postgis] Why is there the limitation of PGLIB to get the
cursor?


> There is no limit in libpq in general, because psql *uses* libpq, and
> you demonstrated on the linux box that it worked for you. The problem is
> with your *particular* copy/version of libpq on the win2000 box. Time to
> check out the source code you are working with. If you are using a
> precompiled libpq, maybe now is the time to compile your own...
>
> "TONY J.Y." wrote:
> >
> > Dave,
> >
> >           I went to the linux server where the database was located. Ran
the
> > psql session to declare
> >           the big cursor and fetch all in the cursor. there was no error
> > there. I could declare the cursor,
> >           and fetched all rows from the cursor.  So PostgreSQL
server-side

> > is OK.  The asbinary function
> >           is OK too.
> >
> >           I ran my program at client side (Win2K Pro with libpq).  There
> > came the error message when the
> >           geom data is bigger:
> >
> >          "char *PQerrorMessage(const PGconn* conn);"
> >           ********************************************************
> >                 Error = pqReadData() --  read() failed: errno=0
> >
> >           ********************************************************
> >           So there is some limit in the pqReadData() --  read() of the C
> > library libpq, isn't there?
> >
> > ../Tony
> >
> > ----- Original Message -----
> > From: "Dave Blasby" <[hidden email]>
> > To: <[hidden email]>
> > Sent: Thursday, December 06, 2001 10:42 AM
> > Subject: Re: [postgis] Why is there the limitation of PGLIB to get the
> > cursor?
> >
> > > Tony,
> > > I looked at your code and it looks like it should work.   I suggest
you

> > > open a "psql" session to the DB and type in all the commands that your
> > > program sends to the database.  When you execute the "FETCH ALL"
> > > command, you should get an error message.
> > >
> > > You could also call the "char *PQerrorMessage(const PGconn* conn);"
> > > function after the "FETCH ALL" command fails.
> > >
> > > Hopefully the error message will guide you as to the problem.
> > >
> > > The binary cursor is only limited by your machine's memory.
> > >
> > > dave
> > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > [hidden email]
> > >
> > >
> > >
> > > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/

> > >
> > >
> > >
> >
> >
> > To unsubscribe from this group, send an email to:
> > [hidden email]
> >
> >
> >
> > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/

>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Why is there the limitation of PGLIB to get the cursor?

TONY J.Y.
Problem solved. I just used the newest version 7.2.b2 of postgres libpq to
get connection.
Then. It works.


Thanks Dave and Paul.


Tony.

----- Original Message -----
From: "TONY J.Y." <[hidden email]>
To: <[hidden email]>
Sent: Friday, December 07, 2001 11:21 AM
Subject: Re: [postgis] Why is there the limitation of PGLIB to get the
cursor?


> Paul,
>
>      I have never used the precompiled library libpq.
>
>      I installed the postgressql 7.1.3. with the source-code
> postgressql7.1.3source-tar.gz on my linux box.
>      After that,  I expanded the  postgressql7.1.3source-tar.gz file in my
> client win2k box.
>      Using the      "nmake -f win32.mak"  get the win2k box Lib and DLL.
>
>     What is your meaning for your this sentence?
>         "The problem is with your *particular* copy/version of libpq on
the
> win2000 box."
>
>     What I need to do to get the correct copy/version of libpq in the
win2k

> box?
>     Looking forward to your suggestion.
>
>
> .. /tony
>
>
> ----- Original Message -----
> From: "Paul Ramsey" <[hidden email]>
> To: <[hidden email]>
> Sent: Friday, December 07, 2001 10:15 AM
> Subject: Re: [postgis] Why is there the limitation of PGLIB to get the
> cursor?
>
>
> > There is no limit in libpq in general, because psql *uses* libpq, and
> > you demonstrated on the linux box that it worked for you. The problem is
> > with your *particular* copy/version of libpq on the win2000 box. Time to
> > check out the source code you are working with. If you are using a
> > precompiled libpq, maybe now is the time to compile your own...
> >
> > "TONY J.Y." wrote:
> > >
> > > Dave,
> > >
> > >           I went to the linux server where the database was located.
Ran
> the
> > > psql session to declare
> > >           the big cursor and fetch all in the cursor. there was no
error
> > > there. I could declare the cursor,
> > >           and fetched all rows from the cursor.  So PostgreSQL
> server-side
> > > is OK.  The asbinary function
> > >           is OK too.
> > >
> > >           I ran my program at client side (Win2K Pro with libpq).
There
> > > came the error message when the
> > >           geom data is bigger:
> > >
> > >          "char *PQerrorMessage(const PGconn* conn);"
> > >           ********************************************************
> > >                 Error = pqReadData() --  read() failed: errno=0
> > >
> > >           ********************************************************
> > >           So there is some limit in the pqReadData() --  read() of the
C

> > > library libpq, isn't there?
> > >
> > > ../Tony
> > >
> > > ----- Original Message -----
> > > From: "Dave Blasby" <[hidden email]>
> > > To: <[hidden email]>
> > > Sent: Thursday, December 06, 2001 10:42 AM
> > > Subject: Re: [postgis] Why is there the limitation of PGLIB to get the
> > > cursor?
> > >
> > > > Tony,
> > > > I looked at your code and it looks like it should work.   I suggest
> you
> > > > open a "psql" session to the DB and type in all the commands that
your

> > > > program sends to the database.  When you execute the "FETCH ALL"
> > > > command, you should get an error message.
> > > >
> > > > You could also call the "char *PQerrorMessage(const PGconn* conn);"
> > > > function after the "FETCH ALL" command fails.
> > > >
> > > > Hopefully the error message will guide you as to the problem.
> > > >
> > > > The binary cursor is only limited by your machine's memory.
> > > >
> > > > dave
> > > >
> > > >
> > > > To unsubscribe from this group, send an email to:
> > > > [hidden email]
> > > >
> > > >
> > > >
> > > > Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
> > > >
> > > >
> > > >
> > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > [hidden email]
> > >
> > >
> > >
> > > Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
> >
> > To unsubscribe from this group, send an email to:
> > [hidden email]
> >
> >
> >
> > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/

> >
> >
> >
>
>
> To unsubscribe from this group, send an email to:
> [hidden email]
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>


To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Why is there the limitation of PGLIB to get the cursor?

David Blasby-3
"TONY J.Y." wrote:
>
> Problem solved. I just used the newest version 7.2.b2 of postgres libpq to
> get connection.
> Then. It works.


DONT USE version 7.2 of the postgresql server with postgis.  The GiST
index has changed from 7.1 to 7.2 and it will cause problems.



dave

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Quit now for Great
American Smokeout
http://us.click.yahoo.com/0vN8tD/9pSDAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/