[postgis-users] problem using spatial queries with JDBC

[Just van den Broecke]

Hi,

All issues resolved! Basically two problems related to my use of the 
PostGIS PDF manual:

- reversed single quotes in the PDF (` i.s.o ') copy/pasted
- PDF version was out of date w.r.t. GeomFromText/BOX3D (should be 
SetSRID/BOX3D as the HTML is stating in section 4.4.1)

Found most in this thread:
http://postgis.refractions.net/pipermail/postgis-users/2006-May/012048.html

Just

Just van den Broecke wrote:
> 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
> 
> 
> 
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
>