[postgis-users] problem using spatial queries with JDBC

[Just van den Broecke]

Hi,

I've setup PostGIS with JDBC (see info below). Standard inserts/queries 
and adding spatial indexes work and I'm using the PGgeometryLW class. 
Only when I start using spatial queries/operators/functions that some 
issues arise that I'm puzzled about. I've also tried the same queries 
from the psql commandline. The table "spatialone" has columns "name" and 
"point" (POINT 2D)

Example 1:
// find points within boxed 100m distance
SELECT name,point FROM spatialone WHERE point && 
Expand(GeomFromText('POINT(2 3)',4326), 100);

this works as expected, both using JDBC (points returning 
PGgeometryLW's) and psql.

Example 2:
// find points in radius of 100m
SELECT name,AsText(point) AS point FROM spatialone WHERE point && 
Expand(GeomFromText('POINT(2 3)',4326), 100) AND 
Distance(GeomFromText('POINT(2 3)',4326),point) < 100;

this works using psql but gives a parse error in JDBC:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "2"
Here's where the puzzlement begins.

Example 3:
// Find features in bounding box
SELECT name,point FROM spatialone WHERE point && GeomFromText(’BOX3D(1 
2, 5 6)’::box3d,4326);

this gives the same parse error as ex 2 in JDBC but also an error in psql:
ERROR:  function geomfromtext(box3d, integer) does not exist
HINT:  No function matches the given name and argument types. You may 
need to add explicit type casts.

I tried some variants of the latter query but to no avail. The queries 
come from the PostGIS manual/FAQ and seem ordinary. I may be overlooking 
something (registering types with the JDBC connection?) or am dealing 
with incompatibilities or should upgrade. Any help appreciated. best,

--Just

Just van den Broecke
http://www.justobjects.nl

JDBC
driver: org.postgis.DriverWrapperLW
protocol: jdbc:postgresql_lwgis

---------- Version info --------------
** PostGIS jdbc client code **
     getFullVersion: PostGIS JDBC V1.1.0

** PGJDBC Driver **
     getVersion: PostgreSQL 8.1 JDBC3 with SSL (build 407)
     getMajorVersion: 8
     getMinorVersion: 1

** PostgreSQL Server **
     version: PostgreSQL 8.1.3 on i686-apple-darwin8.7.1, compiled by 
GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 
5363)

** PostGIS Server **
     postgis_version: 1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
     postgis_proj_version: Rel. 4.4.9, 29 Oct 2004
     postgis_scripts_installed: 1.1.1
     postgis_lib_version: 1.1.1
     postgis_scripts_released: 1.1.1
     postgis_uses_stats: t
     postgis_geos_version: 2.2.1-CAPI-1.0.1
     postgis_scripts_build_date: 2006-10-04 08:22:06
     postgis_lib_build_date: 2006-10-04 08:22:06
     postgis_full_version: POSTGIS="1.1.1" GEOS="2.2.1-CAPI-1.0.1" 
PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS