geometry queries not correctly represented

[GoogleCodeExporter]

What steps will reproduce the problem?
1. use postgis-jdbc 1.1.6 or any other gis-enabed jdbc ( in my case in
combination with hibernatespatial)
2. issue any query contianing geometry (point in polygon)
3. the resulting sql string logged is invalid, although the query works.

What is the expected output? What do you see instead?
wrong output: 
select count(*) as y0_ from public.b_hotel this_ where (this_.coordinate &&
SRID=4326;POLYGON((65.1 10.75,64.79256448645168
7.628554847741948,63.882072520180586 4.6270650821585635,62.40351379684073
1.8608762716863652,60.41370849898476 -0.5637084989847594,57.989123728313636
-2.5535137968407238,55.22293491784144 -4.032072520180588,52.221445152258056
-4.942564486451687,49.1 -5.25,45.97855484774195
-4.942564486451687,42.97706508215857 -4.032072520180588,40.210876271686374
-2.5535137968407255,37.78629150101524
-0.5637084989847612,35.796486203159276 1.8608762716863652,34.31792747981941
4.627065082158569,33.407435513548315 7.628554847741956,33.1
10.750000000000012,33.40743551354832 13.871445152258067,34.31792747981942
16.872934917841455,35.79648620315929 19.639123728313656,37.786291501015256
22.06370849898478,40.21087627168639 24.05351379684074,42.977065082158596
25.5320725201806,45.97855484774198 26.442564486451694,49.100000000000044
26.75,52.2214451522581 26.44256448645168,55.22293491784148
25.53207252018057,57.98912372831368 24.053513796840697,60.4137084989848
22.063708498984724,62.403513796840755 19.63912372831359,63.882072520180614
16.87293491784138,64.7925644864517 13.87144515225799,65.1 10.75))  AND  
within(this_.coordinate, SRID=4326;POLYGON((65.1 10.75,64.79256448645168
7.628554847741948,63.882072520180586 4.6270650821585635,62.40351379684073
1.8608762716863652,60.41370849898476 -0.5637084989847594,57.989123728313636
-2.5535137968407238,55.22293491784144 -4.032072520180588,52.221445152258056
-4.942564486451687,49.1 -5.25,45.97855484774195
-4.942564486451687,42.97706508215857 -4.032072520180588,40.210876271686374
-2.5535137968407255,37.78629150101524
-0.5637084989847612,35.796486203159276 1.8608762716863652,34.31792747981941
4.627065082158569,33.407435513548315 7.628554847741956,33.1
10.750000000000012,33.40743551354832 13.871445152258067,34.31792747981942
16.872934917841455,35.79648620315929 19.639123728313656,37.786291501015256
22.06370849898478,40.21087627168639 24.05351379684074,42.977065082158596
25.5320725201806,45.97855484774198 26.442564486451694,49.100000000000044
26.75,52.2214451522581 26.44256448645168,55.22293491784148
25.53207252018057,57.98912372831368 24.053513796840697,60.4137084989848
22.063708498984724,62.403513796840755 19.63912372831359,63.882072520180614
16.87293491784138,64.7925644864517 13.87144515225799,65.1 10.75)))) limit 1

expected: a string, that i can copy-paste into postgresql that will work
like any other string produced by log4jdbc
this may for example work:

select count(*) as y0_ from public.b_hotel this_ where
(within(this_.coordinate, setsrid(GeometryFromText('POLYGON((65.1
10.75,64.79256448645168 7.628554847741948,63.882072520180586
4.6270650821585635,62.40351379684073 1.8608762716863652,60.41370849898476
-0.5637084989847594,57.989123728313636
-2.5535137968407238,55.22293491784144 -4.032072520180588,52.221445152258056
-4.942564486451687,49.1 -5.25,45.97855484774195
-4.942564486451687,42.97706508215857 -4.032072520180588,40.210876271686374
-2.5535137968407255,37.78629150101524
-0.5637084989847612,35.796486203159276 1.8608762716863652,34.31792747981941
4.627065082158569,33.407435513548315 7.628554847741956,33.1
10.750000000000012,33.40743551354832 13.871445152258067,34.31792747981942
16.872934917841455,35.79648620315929 19.639123728313656,37.786291501015256
22.06370849898478,40.21087627168639 24.05351379684074,42.977065082158596
25.5320725201806,45.97855484774198 26.442564486451694,49.100000000000044
26.75,52.2214451522581 26.44256448645168,55.22293491784148
25.53207252018057,57.98912372831368 24.053513796840697,60.4137084989848
22.063708498984724,62.403513796840755 19.63912372831359,63.882072520180614
16.87293491784138,64.7925644864517 13.87144515225799,65.1 10.75))'),4326)))
limit 1

What version of the product are you using? On what operating system?
1.2alpha1, postgis-jdbc 1.1.6, slfj 1.5.3, log4j 1.2.14, jdbc: postgresql
8.1-407.jdbc3.jar

Original issue reported on code.google.com by [email protected] on 25 Feb 2009 at 10:40

[GoogleCodeExporter]

more precisely:

select count(*) as y0_ from public.b_hotel this_ where this_.coordinate &&
setsrid(GeometryFromText('POLYGON((65.1 10.75,64.79256448645168
7.628554847741948,63.882072520180586 4.6270650821585635,62.40351379684073
1.8608762716863652,60.41370849898476 -0.5637084989847594,57.989123728313636
-2.5535137968407238,55.22293491784144 -4.032072520180588,52.221445152258056
-4.942564486451687,49.1 -5.25,45.97855484774195 
-4.942564486451687,42.97706508215857
-4.032072520180588,40.210876271686374 -2.5535137968407255,37.78629150101524
-0.5637084989847612,35.796486203159276 1.8608762716863652,34.31792747981941
4.627065082158569,33.407435513548315 7.628554847741956,33.1
10.750000000000012,33.40743551354832 13.871445152258067,34.31792747981942
16.872934917841455,35.79648620315929 19.639123728313656,37.786291501015256
22.06370849898478,40.21087627168639 24.05351379684074,42.977065082158596
25.5320725201806,45.97855484774198 26.442564486451694,49.100000000000044
26.75,52.2214451522581 26.44256448645168,55.22293491784148
25.53207252018057,57.98912372831368 24.053513796840697,60.4137084989848
22.063708498984724,62.403513796840755 19.63912372831359,63.882072520180614
16.87293491784138,64.7925644864517 13.87144515225799,65.1 10.75))'),4326) AND
(within(this_.coordinate, setsrid(GeometryFromText('POLYGON((65.1
10.75,64.79256448645168 7.628554847741948,63.882072520180586
4.6270650821585635,62.40351379684073 1.8608762716863652,60.41370849898476
-0.5637084989847594,57.989123728313636 -2.5535137968407238,55.22293491784144
-4.032072520180588,52.221445152258056 -4.942564486451687,49.1 
-5.25,45.97855484774195
-4.942564486451687,42.97706508215857 -4.032072520180588,40.210876271686374
-2.5535137968407255,37.78629150101524 -0.5637084989847612,35.796486203159276
1.8608762716863652,34.31792747981941 4.627065082158569,33.407435513548315
7.628554847741956,33.1 10.750000000000012,33.40743551354832
13.871445152258067,34.31792747981942 16.872934917841455,35.79648620315929
19.639123728313656,37.786291501015256 22.06370849898478,40.21087627168639
24.05351379684074,42.977065082158596 25.5320725201806,45.97855484774198
26.442564486451694,49.100000000000044 26.75,52.2214451522581
26.44256448645168,55.22293491784148 25.53207252018057,57.98912372831368
24.053513796840697,60.4137084989848 22.063708498984724,62.403513796840755
19.63912372831359,63.882072520180614 16.87293491784138,64.7925644864517
13.87144515225799,65.1 10.75))'),4326))) limit 1

Original comment by [email protected] on 26 Feb 2009 at 12:07

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Can you explain more concisely as to why the query is invalid?
I am not familiar with queries containing "geometry"...

Thanks

Original comment by [email protected] on 26 Feb 2009 at 2:36

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

SRID=4326;POLYGON((..)) is not a boolean expression that can be used in a where 
clause.
the parameter geometry used is given, but not the function surrounding it.
(within(COLUMN_NAME, setsrid(GeometryFromText('POLYGON((..))'),4326))) is the 
correct way.
what is missing in the log4jdbc output:
1) which function was used in the query to evaluate the where clause (within,
contains, filter, adjacent....)
2) inside the function: to which column does the geometry relate
3) to be a valid geometry datatype, it must be surrounded by GeometryFromText
4) the setsrid function must surround the GeometryFromText, in order to 
identifyand
match the reference geomtry used in the column

Original comment by [email protected] on 26 Feb 2009 at 3:47

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

note that these comments are postgis specific.

Original comment by [email protected] on 26 Feb 2009 at 3:49

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

any comment if this could be fixed? this is slowing me down nearly every day.

Original comment by [email protected] on 9 May 2009 at 6:52

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

I was hoping you would submit a fix :)
I barely understand the issue and I've never used geometry queries.

Original comment by [email protected] on 9 May 2009 at 11:43

• Added labels: ****
• Removed labels: ****

[arthurblake]

Stale.