[postgis] JDBC and WKB; shp2postgresql

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

[postgis] JDBC and WKB; shp2postgresql

Doug Van Auken
I four questions regarding WKB and JDBC:

First question:
Does anyone have some examples on how to insert and retreive geometry
using WKB and JDBC:
-------------------------------------------------------
Second question:
According to the OpenGIS SQL specicification an insert using WKB
would use the following format:
INSERT INTO Countries (Name, Location) VALUES ('Kenya', PolygonFromWKB
(:wnb, :srid)).  

Using this format, how do you insert an WKB object using JDBC?

As an example, if I have this:

Polygon polygon = new Polygon(...);
....
PreparedStatement pstmt = cnn.prepareStatement("INSERT INTO Countries
(Name, Location) VALUES ('Kenya', ?)
pstmt.setOject(1, polygon)

How would I implement PolgyonFromWKB(:wkb, :srid)?
----------------------------------------------------

Second question:
Is the following the correct way to retreive an object using JDBC and
WKB?

ResultSet rst = cnn.executeQuery("SELECT Name, AsBinary(Location)
FROM Countries WHERE Name like 'k%');
while(r.next()){
  String name = r.getString(1)
  PGgeometry geometry = (PGgeometry)r.getObject(2);
}    

------------------------------------------------------
Third question:
If I insert an object as text, can I retreive it as binary, and vice-
versa -- If I insert it as binary, can I retreive it as text?


------------------------ 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/ltH6zA/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] JDBC and WKB; shp2postgresql

Paul Ramsey-2
Your numbering is a little funny (1,2,2,3) :) but I'll take a try
anyways:

> First question:
> Does anyone have some examples on how to insert and retreive geometry
> using WKB and JDBC:

No, because as far as I can tell, it cannot be done. I have gone through
the postgresql jdbc driver, and it looks to me like it is restricted to
text cursors. If someone knows or has done different I would be
interested to see the results.

> -------------------------------------------------------
> Second question:
> According to the OpenGIS SQL specicification an insert using WKB
> would use the following format:
> INSERT INTO Countries (Name, Location) VALUES ('Kenya', PolygonFromWKB
> (:wnb, :srid)).
>
> Using this format, how do you insert an WKB object using JDBC?

Again, unfortunately WKB just is not on the agenda using the JDBC
driver.

> As an example, if I have this:
>
> Polygon polygon = new Polygon(...);
> ....
> PreparedStatement pstmt = cnn.prepareStatement("INSERT INTO Countries
> (Name, Location) VALUES ('Kenya', ?)
> pstmt.setOject(1, polygon)
> How would I implement PolgyonFromWKB(:wkb, :srid)?

Not entirely sure what is being asked here, but see the comments above,
as they might have a bearing.

> ----------------------------------------------------
>
> Second question:
> Is the following the correct way to retreive an object using JDBC and
> WKB?
>
> ResultSet rst = cnn.executeQuery("SELECT Name, AsBinary(Location)
> FROM Countries WHERE Name like 'k%');
> while(r.next()){
>   String name = r.getString(1)
>   PGgeometry geometry = (PGgeometry)r.getObject(2);
> }

What is actually happening behind the scenes when you do this is that
the proxy object PGgeometry is parsing the WKT representation of the
object. It is nice because you do not have to worry about parsing, but
it is not WKB, it is just hiding the WKT for you.

> ------------------------------------------------------
> Third question:
> If I insert an object as text, can I retreive it as binary, and vice-
> versa -- If I insert it as binary, can I retreive it as text?

Yes, absolutely. In the database, there is just one geometry
representation, a C struct. The WKT and WKB representations are
generated on the fly when requested. You can make a geometry from either
representation and you can retrieve a geometry as either representation.
In fact, the idea of an AsGML() function is Yet Another Way to retrieve
geometries.
(If anyone wants to do AsGML() it would be a great simple function for
someone to add as a learning project...)

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Need new boots for winter? Looking for a perfect gift for your shoe loving friends?
Zappos.com is the perfect fit for all your shoe needs!
http://us.click.yahoo.com/3wM6yD/QrSDAA/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
|

[postgis] Shared library error...

spoedniek
Hi,

I'm a bit lost here after actually managing to install postgres and then
postgis on my linux box. postgresql seems to be working and the postmaster
is running. After I installed postgis and the loader according to the
documentation, I get the following error on running either shp2pgsql or
pgsql2shp: error while loading shared libraries: libpq.so.2: cannot open
shared object file: No such file or directory.

Of course the file is in pgsql/lib directory. So what did I do wrong? I'm
running RedHat Linux 7.0, postgresql7.1.3 and postgis0.6.2.

Thanks for any help.
Henry




------------------------ Yahoo! Groups Sponsor ---------------------~-->
Need new boots for winter? Looking for a perfect gift for your shoe loving friends?
Zappos.com is the perfect fit for all your shoe needs!
http://us.click.yahoo.com/3wM6yD/QrSDAA/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] Shared library error...

Eric Faurot
Hi,

pgsql/lib must be in your ld path when you run the scripts.
You can either add the complete path in /etc/ld.so.config (and run ldconfig)
or add it to your LD_LIBRARY_PATH environement variable.

Eric.

------------------------ 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/XwUZwC/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
|

[postgis] Re: JDBC and WKB; shp2postgresql

Doug Van Auken
In reply to this post by Paul Ramsey-2
Thanks for the info.  Right after I sent the note, I realized my
numbering was off (the subject line was off a little bit, too.)  Oh
well!?

Anyway, If anyone knows about how to get WKB and JDBC working
together, I'd really like to see how it works.

Thanks again.

--- In postgis@y..., Paul Ramsey <pramsey@r...> wrote:
> Your numbering is a little funny (1,2,2,3) :) but I'll take a try
> anyways:
>
> > First question:
> > Does anyone have some examples on how to insert and retreive
geometry
> > using WKB and JDBC:
>
> No, because as far as I can tell, it cannot be done. I have gone
through
> the postgresql jdbc driver, and it looks to me like it is
restricted to
> text cursors. If someone knows or has done different I would be
> interested to see the results.
>
> > -------------------------------------------------------
> > Second question:
> > According to the OpenGIS SQL specicification an insert using WKB
> > would use the following format:
> > INSERT INTO Countries (Name, Location) VALUES ('Kenya',
PolygonFromWKB

> > (:wnb, :srid)).
> >
> > Using this format, how do you insert an WKB object using JDBC?
>
> Again, unfortunately WKB just is not on the agenda using the JDBC
> driver.
>
> > As an example, if I have this:
> >
> > Polygon polygon = new Polygon(...);
> > ....
> > PreparedStatement pstmt = cnn.prepareStatement("INSERT INTO
Countries
> > (Name, Location) VALUES ('Kenya', ?)
> > pstmt.setOject(1, polygon)
> > How would I implement PolgyonFromWKB(:wkb, :srid)?
>
> Not entirely sure what is being asked here, but see the comments
above,
> as they might have a bearing.
>
> > ----------------------------------------------------
> >
> > Second question:
> > Is the following the correct way to retreive an object using JDBC
and

> > WKB?
> >
> > ResultSet rst = cnn.executeQuery("SELECT Name, AsBinary(Location)
> > FROM Countries WHERE Name like 'k%');
> > while(r.next()){
> >   String name = r.getString(1)
> >   PGgeometry geometry = (PGgeometry)r.getObject(2);
> > }
>
> What is actually happening behind the scenes when you do this is
that
> the proxy object PGgeometry is parsing the WKT representation of the
> object. It is nice because you do not have to worry about parsing,
but
> it is not WKB, it is just hiding the WKT for you.
>
> > ------------------------------------------------------
> > Third question:
> > If I insert an object as text, can I retreive it as binary, and
vice-
> > versa -- If I insert it as binary, can I retreive it as text?
>
> Yes, absolutely. In the database, there is just one geometry
> representation, a C struct. The WKT and WKB representations are
> generated on the fly when requested. You can make a geometry from
either
> representation and you can retrieve a geometry as either
representation.
> In fact, the idea of an AsGML() function is Yet Another Way to
retrieve
> geometries.
> (If anyone wants to do AsGML() it would be a great simple function
for
> someone to add as a learning project...)


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Free unlimited calling with CrystalVoice!
Download your FREE 30 day trial. Click Here.
http://us.click.yahoo.com/2YBmVC/f.SDAA/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/