Hibernate Spatial › Hibernate Spatial - Users

oracle 10g sql error

12 messages

hila

oracle 10g sql error

Reply Threaded

Some javascript/style in this post has been disabled (why?)

I am getting trying to run the tutorial query with oracle10g, but it is giving me “geometry objects are in different coordinate systems” error.

can you help please? I am very new to both spatial db and hibernate.

I am calling this function from the main method with the following input:

String testFilter ="POLYGON((1 1,20 1,20 20, 1 20, 1 1))";

private List find(String wktFilter){

WKTReader fromText = new WKTReader();

Geometry filter = null;

try{

filter = fromText.read(wktFilter);

} catch(ParseException e){

throw new RuntimeException("Not a WKT String:" + wktFilter);

}

System.out.println("Filter is : " + filter);

Criteria testCriteria = session.createCriteria(Target.class);

testCriteria.add(SpatialRestrictions.within("location", null, filter));

List results = testCriteria.list();

session.getTransaction().commit();

return results;

}

Here is the generated HQL and error:

Hibernate: select targetpoin0_.idtarget_points as idtarget1_0_, targetpoin0_.RIGHT_ASCENSION as RIGHT2_0_, targetpoin0_.DECLINATION as DECLINAT3_0_, targetpoin0_.IDTARGET as IDTARGET0_, targetpoin0_.LOCATION as LOCATION0_ from TARGET targetpoin0_ where MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(targetpoin0_.LOCATION),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1

2009-09-17 14:13:04,679 WARN (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 13295, SQLState: 72000

2009-09-17 14:13:04,679 ERROR (org.hibernate.util.JDBCExceptionReporter:78) - ORA-13295: geometry objects are in different coordinate systems

ORA-06512: at "MDSYS.SDO_3GL", line 4

ORA-06512: at "MDSYS.MD2", line 758

ORA-06512: at "MDSYS.SDO_GEOM", line 2622

ORA-06512: at "MDSYS.SDO_GEOM", line 2649

ORA-06512: at "MDSYS.ST_GEOMETRY", line 59

ORA-06512: at "MDSYS.OGC_WITHIN", line 6

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

Karel Maesen

Re: oracle 10g sql error

Reply Threaded

Oracle gives an error when you try to perform a spatial query with a
fiilter geometry which has a different spatial reference system ID
(SRID) than the table you query. In your case, you specify a filter
polygon which has no SRID. You should use the setSRID() method of
Geometry to set the srid to the same value as the table.

Regards,

Karel

On 17 Sep 2009, at 20:27, Olumee, Milan wrote:

> I am getting trying to run the tutorial query with oracle10g, but
> it is giving me “geometry objects are in different coordinate
> systems” error.
>
>
>
> can you help please? I am very new to both spatial db and hibernate.
>
>
>
> I am calling this function from the main method with the following
> input:
>
>
>
> String testFilter ="POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>
>
>
> private List find(String wktFilter){
>
>
>
> WKTReader fromText = new WKTReader();
>
> Geometry filter = null;
>
> try{
>
> filter = fromText.read(wktFilter);
>
> } catch(ParseException e){
>
> throw new RuntimeException("Not a WKT String:" +
> wktFilter);
>
> }
>
>
>
> System.out.println("Filter is : " + filter);
>
> Criteria testCriteria = session.createCriteria(Target.class);
>
>
>
> testCriteria.add(SpatialRestrictions.within("location",
> null, filter));
>
> List results = testCriteria.list();
>
> session.getTransaction().commit();
>
>
>
> return results;
>
> }
>
>
>
> Here is the generated HQL and error:
>
>
>
> Hibernate: select targetpoin0_.idtarget_points as idtarget1_0_,
> targetpoin0_.RIGHT_ASCENSION as RIGHT2_0_, targetpoin0_.DECLINATION
> as DECLINAT3_0_, targetpoin0_.IDTARGET as IDTARGET0_,
> targetpoin0_.LOCATION as LOCATION0_ from TARGET targetpoin0_ where
> MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
> (targetpoin0_.LOCATION),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1
>
> 2009-09-17 14:13:04,679 WARN
> (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 13295,
> SQLState: 72000
>
> 2009-09-17 14:13:04,679 ERROR
> (org.hibernate.util.JDBCExceptionReporter:78) - ORA-13295: geometry
> objects are in different coordinate systems
>
> ORA-06512: at "MDSYS.SDO_3GL", line 4
>
> ORA-06512: at "MDSYS.MD2", line 758
>
> ORA-06512: at "MDSYS.SDO_GEOM", line 2622
>
> ORA-06512: at "MDSYS.SDO_GEOM", line 2649
>
> ORA-06512: at "MDSYS.ST_GEOMETRY", line 59
>
> ORA-06512: at "MDSYS.OGC_WITHIN", line 6
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

hila

Re: oracle 10g sql error

Reply Threaded

Hi Karel,

Thank you. Excuse my ignorant, I am very new to the topic. How would I
setSRID in the code, I setup it up in the database to 8307 (for lat &
lon)? I think my main problem is I don't know how to pass polygon dims
to the query. I use this to setSRID.

SDOGeometryType.SDO_GEOMETRY geom = new SDOGeometryType.SDO_GEOMETRY();
geom.setSRID(polygon.getSRID());

Now I have error on the polygon not defined. Can you please help, or if
you can direct me to some material that can help me get started.

A little background on my project, I have a project that is build on
hibernate and ESRI ArcSD and we are trying to convert that to oracle
geospatial with hibernate.

Best Regards

Hila

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On
Behalf Of Karel Maesen
Sent: Friday, September 18, 2009 6:37 AM
To: Hibernate Spatial Users Discussion
Subject: Re: [hibernatespatial-users] oracle 10g sql error

Oracle gives an error when you try to perform a spatial query with a
fiilter geometry which has a different spatial reference system ID
(SRID) than the table you query. In your case, you specify a filter
polygon which has no SRID. You should use the setSRID() method of
Geometry to set the srid to the same value as the table.

Regards,

Karel

On 17 Sep 2009, at 20:27, Olumee, Milan wrote:

> I am getting trying to run the tutorial query with oracle10g, but
> it is giving me "geometry objects are in different coordinate
> systems" error.
>
>
>
> can you help please? I am very new to both spatial db and hibernate.
>
>
>
> I am calling this function from the main method with the following
> input:
>
>
>
> String testFilter ="POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>
>
>
> private List find(String wktFilter){
>
>
>
> WKTReader fromText = new WKTReader();
>
> Geometry filter = null;
>
> try{
>
> filter = fromText.read(wktFilter);
>
> } catch(ParseException e){
>
> throw new RuntimeException("Not a WKT String:" +
> wktFilter);
>
> }
>
>
>
> System.out.println("Filter is : " + filter);
>
> Criteria testCriteria = session.createCriteria(Target.class);
>
>
>
> testCriteria.add(SpatialRestrictions.within("location",
> null, filter));
>
> List results = testCriteria.list();
>
> session.getTransaction().commit();
>
>
>
> return results;
>
> }
>
>
>
> Here is the generated HQL and error:
>
>
>
> Hibernate: select targetpoin0_.idtarget_points as idtarget1_0_,
> targetpoin0_.RIGHT_ASCENSION as RIGHT2_0_, targetpoin0_.DECLINATION
> as DECLINAT3_0_, targetpoin0_.IDTARGET as IDTARGET0_,
> targetpoin0_.LOCATION as LOCATION0_ from TARGET targetpoin0_ where
> MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
> (targetpoin0_.LOCATION),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1
>
> 2009-09-17 14:13:04,679 WARN
> (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 13295,
> SQLState: 72000
>
> 2009-09-17 14:13:04,679 ERROR
> (org.hibernate.util.JDBCExceptionReporter:78) - ORA-13295: geometry
> objects are in different coordinate systems
>
> ORA-06512: at "MDSYS.SDO_3GL", line 4
>
> ORA-06512: at "MDSYS.MD2", line 758
>
> ORA-06512: at "MDSYS.SDO_GEOM", line 2622
>
> ORA-06512: at "MDSYS.SDO_GEOM", line 2649
>
> ORA-06512: at "MDSYS.ST_GEOMETRY", line 59
>
> ORA-06512: at "MDSYS.OGC_WITHIN", line 6
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatia
l-users
_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

hila

Re: oracle 10g sql error

Reply Threaded

In reply to this post by Karel Maesen

Added to my previous email... I was wondering how to convert this query
into an hibernate or HQL query. It is a valid query in SQL and does
return the correct result.

Select a.*
FROM target a
WHERE SDO_FILTER(a.location, mdsys.sdo_geometry(2003, 8307,NULL,
mdsys.SDO_ELEM_INFO_ARRAY(1,1003,3),
mdsys.SDO_ORDINATE_ARRAY(-3.23, 15.69,43.84,38.70)),
'querytype=WINDOW') = 'TRUE';

Appreciate your help.

Regards

Hila

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On
Behalf Of Karel Maesen
Sent: Friday, September 18, 2009 6:37 AM
To: Hibernate Spatial Users Discussion
Subject: Re: [hibernatespatial-users] oracle 10g sql error

Oracle gives an error when you try to perform a spatial query with a
fiilter geometry which has a different spatial reference system ID
(SRID) than the table you query. In your case, you specify a filter
polygon which has no SRID. You should use the setSRID() method of
Geometry to set the srid to the same value as the table.

Regards,

Karel

On 17 Sep 2009, at 20:27, Olumee, Milan wrote:

Karel Maesen

Re: oracle 10g sql error

Reply Threaded

In reply to this post by hila

Hi,

To set the SRID use the Geometry.setSRID() method on the filter
object. That should be all

Regards,

Karel
On 18 Sep 2009, at 16:58, Olumee, Milan wrote:

> Hi Karel,
>
> Thank you. Excuse my ignorant, I am very new to the topic. How
> would I
> setSRID in the code, I setup it up in the database to 8307 (for lat &
> lon)? I think my main problem is I don't know how to pass polygon
> dims
> to the query. I use this to setSRID.
>
> SDOGeometryType.SDO_GEOMETRY geom = new SDOGeometryType.SDO_GEOMETRY
> ();
> geom.setSRID(polygon.getSRID());
>
> Now I have error on the polygon not defined. Can you please help,
> or if
> you can direct me to some material that can help me get started.
>
> A little background on my project, I have a project that is build on
> hibernate and ESRI ArcSD and we are trying to convert that to oracle
> geospatial with hibernate.
>
> Best Regards
>
> Hila
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Karel Maesen
> Sent: Friday, September 18, 2009 6:37 AM
> To: Hibernate Spatial Users Discussion
> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>
> Oracle gives an error when you try to perform a spatial query with a
> fiilter geometry which has a different spatial reference system ID
> (SRID) than the table you query. In your case, you specify a filter
> polygon which has no SRID. You should use the setSRID() method of
> Geometry to set the srid to the same value as the table.
>
> Regards,
>
> Karel
>
>
> On 17 Sep 2009, at 20:27, Olumee, Milan wrote:
>
>> I am getting trying to run the tutorial query with oracle10g, but
>> it is giving me "geometry objects are in different coordinate
>> systems" error.
>>
>>
>>
>> can you help please? I am very new to both spatial db and hibernate.
>>
>>
>>
>> I am calling this function from the main method with the following
>> input:
>>
>>
>>
>> String testFilter ="POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>>
>>
>>
>> private List find(String wktFilter){
>>
>>
>>
>> WKTReader fromText = new WKTReader();
>>
>> Geometry filter = null;
>>
>> try{
>>
>> filter = fromText.read(wktFilter);
>>
>> } catch(ParseException e){
>>
>> throw new RuntimeException("Not a WKT String:" +
>> wktFilter);
>>
>> }
>>
>>
>>
>> System.out.println("Filter is : " + filter);
>>
>> Criteria testCriteria = session.createCriteria(Target.class);
>>
>>
>>
>> testCriteria.add(SpatialRestrictions.within("location",
>> null, filter));
>>
>> List results = testCriteria.list();
>>
>> session.getTransaction().commit();
>>
>>
>>
>> return results;
>>
>> }
>>
>>
>>
>> Here is the generated HQL and error:
>>
>>
>>
>> Hibernate: select targetpoin0_.idtarget_points as idtarget1_0_,
>> targetpoin0_.RIGHT_ASCENSION as RIGHT2_0_, targetpoin0_.DECLINATION
>> as DECLINAT3_0_, targetpoin0_.IDTARGET as IDTARGET0_,
>> targetpoin0_.LOCATION as LOCATION0_ from TARGET targetpoin0_ where
>> MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
>> (targetpoin0_.LOCATION),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1
>>
>> 2009-09-17 14:13:04,679 WARN
>> (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 13295,
>> SQLState: 72000
>>
>> 2009-09-17 14:13:04,679 ERROR
>> (org.hibernate.util.JDBCExceptionReporter:78) - ORA-13295: geometry
>> objects are in different coordinate systems
>>
>> ORA-06512: at "MDSYS.SDO_3GL", line 4
>>
>> ORA-06512: at "MDSYS.MD2", line 758
>>
>> ORA-06512: at "MDSYS.SDO_GEOM", line 2622
>>
>> ORA-06512: at "MDSYS.SDO_GEOM", line 2649
>>
>> ORA-06512: at "MDSYS.ST_GEOMETRY", line 59
>>
>> ORA-06512: at "MDSYS.OGC_WITHIN", line 6
>>
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>> hibernatespatial-users
>
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatia
> l-users
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

Olumee, Milan

Re: oracle 10g sql error

Reply Threaded

Thank you Karel. Your solution did resolve the issue with SRID, but
didn't return any results although there are records in the database.
Therefore, I try to use the SDO_filter where it does return records in
SQL and give "points must form a closed linestring" exception in
hibernate

here is the query that works in SQL but not in hibernate

Select a.*
FROM target a
WHERE SDO_FILTER(a.location, mdsys.sdo_geometry(2003, 8307,NULL,
mdsys.SDO_ELEM_INFO_ARRAY(1,1003,1),
mdsys.SDO_ORDINATE_ARRAY(-2.3077, 40.6154, 43.8462, 38.7692, 22.6154,
14.3077, -3.2308, 15.692, -2.3077, 40.6154)),
'querytype=WINDOW') = 'TRUE';

This is what I've done using hibernate spatial:

Criteria testCriteria = session.createCriteria(Target.class);
//System.out.println("filter = "+filter);
testCriteria.add(SpatialRestrictions.filter("location",
filter));
List results = testCriteria.list();
if (results.size() > 0) {
System.out.println("after restult = " + results.get(0));
}

with filter being passed to the method as

Geometry testGeom = new WKTReader().read("POLYGON ((-2.3077 40.6154,
43.8462 38.7692, 22.6154 14.3077, -3.2308 15.692, -2.3077 40.6154))");

I get the following exception

Hibernate: select this_.idtarget_points as idtarget1_0_0_,
this_.LOCATION as LOCATION0_0_ from TARGET this_ where
SDO_FILTER(this_.LOCATION,?) = 'TRUE'
Exception in thread "main" java.lang.IllegalArgumentException: points
must form a closed linestring
at
com.vividsolutions.jts.geom.LinearRing.validateConstruction(LinearRing.j
ava:95)
at com.vividsolutions.jts.geom.LinearRing.<init>(LinearRing.java:90)
at
com.vividsolutions.jts.geom.GeometryFactory.createLinearRing(GeometryFac
tory.java:324)
at
org.hibernatespatial.oracle.SDOGeometryType.convertSDOPolygon(SDOGeometr
yType.java:582)
at
org.hibernatespatial.oracle.SDOGeometryType.convert2JTS(SDOGeometryType.
java:452)
at
org.hibernatespatial.oracle.SDOGeometryType.convert2JTS(SDOGeometryType.
java:439)

Any idea?

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On
Behalf Of Karel Maesen
Sent: Monday, September 21, 2009 7:53 AM
To: Hibernate Spatial Users Discussion
Subject: Re: [hibernatespatial-users] oracle 10g sql error

Hi,

To set the SRID use the Geometry.setSRID() method on the filter
object. That should be all

Regards,

Karel
On 18 Sep 2009, at 16:58, Olumee, Milan wrote:

Karel Maesen

Re: oracle 10g sql error

Reply Threaded

You get that error because, apparently the SDO_GEOMETRY contains a
polygon formed by one or more linear rings, but the linear rings are
not closed. Could you post the offending SDO_GEOMTRY.

Regards,

Karel
]
On 21 Sep 2009, at 18:42, Olumee, Milan wrote:

> Thank you Karel. Your solution did resolve the issue with SRID, but
> didn't return any results although there are records in the database.
> Therefore, I try to use the SDO_filter where it does return records in
> SQL and give "points must form a closed linestring" exception in
> hibernate
>
> here is the query that works in SQL but not in hibernate
>
> Select a.*
> FROM target a
> WHERE SDO_FILTER(a.location, mdsys.sdo_geometry(2003, 8307,NULL,
> mdsys.SDO_ELEM_INFO_ARRAY(1,1003,1),
> mdsys.SDO_ORDINATE_ARRAY(-2.3077, 40.6154, 43.8462, 38.7692, 22.6154,
> 14.3077, -3.2308, 15.692, -2.3077, 40.6154)),
> 'querytype=WINDOW') = 'TRUE';
>
> This is what I've done using hibernate spatial:
>
> Criteria testCriteria = session.createCriteria(Target.class);
> //System.out.println("filter = "+filter);
> testCriteria.add(SpatialRestrictions.filter("location",
> filter));
> List results = testCriteria.list();
> if (results.size() > 0) {
> System.out.println("after restult = " + results.get
> (0));
> }
>
> with filter being passed to the method as
>
> Geometry testGeom = new WKTReader().read("POLYGON ((-2.3077 40.6154,
> 43.8462 38.7692, 22.6154 14.3077, -3.2308 15.692, -2.3077 40.6154))");
>
>
> I get the following exception
>
> Hibernate: select this_.idtarget_points as idtarget1_0_0_,
> this_.LOCATION as LOCATION0_0_ from TARGET this_ where
> SDO_FILTER(this_.LOCATION,?) = 'TRUE'
> Exception in thread "main" java.lang.IllegalArgumentException: points
> must form a closed linestring
> at
> com.vividsolutions.jts.geom.LinearRing.validateConstruction
> (LinearRing.j
> ava:95)
> at com.vividsolutions.jts.geom.LinearRing.<init>(LinearRing.java:90)
> at
> com.vividsolutions.jts.geom.GeometryFactory.createLinearRing
> (GeometryFac
> tory.java:324)
> at
> org.hibernatespatial.oracle.SDOGeometryType.convertSDOPolygon
> (SDOGeometr
> yType.java:582)
> at
> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
> (SDOGeometryType.
> java:452)
> at
> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
> (SDOGeometryType.
> java:439)
>
>
> Any idea?
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Karel Maesen
> Sent: Monday, September 21, 2009 7:53 AM
> To: Hibernate Spatial Users Discussion
> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>
> Hi,
>
> To set the SRID use the Geometry.setSRID() method on the filter
> object. That should be all
>
> Regards,
>
> Karel
> On 18 Sep 2009, at 16:58, Olumee, Milan wrote:
>
>> Hi Karel,
>>
>> Thank you. Excuse my ignorant, I am very new to the topic. How
>> would I
>> setSRID in the code, I setup it up in the database to 8307 (for lat &
>> lon)? I think my main problem is I don't know how to pass polygon
>> dims
>> to the query. I use this to setSRID.
>>
>> SDOGeometryType.SDO_GEOMETRY geom = new SDOGeometryType.SDO_GEOMETRY
>> ();
>> geom.setSRID(polygon.getSRID());
>>
>> Now I have error on the polygon not defined. Can you please help,
>> or if
>> you can direct me to some material that can help me get started.
>>
>> A little background on my project, I have a project that is build on
>> hibernate and ESRI ArcSD and we are trying to convert that to oracle
>> geospatial with hibernate.
>>
>> Best Regards
>>
>> Hila
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On
>> Behalf Of Karel Maesen
>> Sent: Friday, September 18, 2009 6:37 AM
>> To: Hibernate Spatial Users Discussion
>> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>>
>> Oracle gives an error when you try to perform a spatial query with a
>> fiilter geometry which has a different spatial reference system ID
>> (SRID) than the table you query. In your case, you specify a filter
>> polygon which has no SRID. You should use the setSRID() method of
>> Geometry to set the srid to the same value as the table.
>>
>> Regards,
>>
>> Karel
>>
>>
>> On 17 Sep 2009, at 20:27, Olumee, Milan wrote:
>>
>>> I am getting trying to run the tutorial query with oracle10g, but
>>> it is giving me "geometry objects are in different coordinate
>>> systems" error.
>>>
>>>
>>>
>>> can you help please? I am very new to both spatial db and
>>> hibernate.
>>>
>>>
>>>
>>> I am calling this function from the main method with the following
>>> input:
>>>
>>>
>>>
>>> String testFilter ="POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>>>
>>>
>>>
>>> private List find(String wktFilter){
>>>
>>>
>>>
>>> WKTReader fromText = new WKTReader();
>>>
>>> Geometry filter = null;
>>>
>>> try{
>>>
>>> filter = fromText.read(wktFilter);
>>>
>>> } catch(ParseException e){
>>>
>>> throw new RuntimeException("Not a WKT String:" +
>>> wktFilter);
>>>
>>> }
>>>
>>>
>>>
>>> System.out.println("Filter is : " + filter);
>>>
>>> Criteria testCriteria = session.createCriteria(Target.class);
>>>
>>>
>>>
>>> testCriteria.add(SpatialRestrictions.within("location",
>>> null, filter));
>>>
>>> List results = testCriteria.list();
>>>
>>> session.getTransaction().commit();
>>>
>>>
>>>
>>> return results;
>>>
>>> }
>>>
>>>
>>>
>>> Here is the generated HQL and error:
>>>
>>>
>>>
>>> Hibernate: select targetpoin0_.idtarget_points as idtarget1_0_,
>>> targetpoin0_.RIGHT_ASCENSION as RIGHT2_0_, targetpoin0_.DECLINATION
>>> as DECLINAT3_0_, targetpoin0_.IDTARGET as IDTARGET0_,
>>> targetpoin0_.LOCATION as LOCATION0_ from TARGET targetpoin0_ where
>>> MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
>>> (targetpoin0_.LOCATION),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1
>>>
>>> 2009-09-17 14:13:04,679 WARN
>>> (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 13295,
>>> SQLState: 72000
>>>
>>> 2009-09-17 14:13:04,679 ERROR
>>> (org.hibernate.util.JDBCExceptionReporter:78) - ORA-13295: geometry
>>> objects are in different coordinate systems
>>>
>>> ORA-06512: at "MDSYS.SDO_3GL", line 4
>>>
>>> ORA-06512: at "MDSYS.MD2", line 758
>>>
>>> ORA-06512: at "MDSYS.SDO_GEOM", line 2622
>>>
>>> ORA-06512: at "MDSYS.SDO_GEOM", line 2649
>>>
>>> ORA-06512: at "MDSYS.ST_GEOMETRY", line 59
>>>
>>> ORA-06512: at "MDSYS.OGC_WITHIN", line 6
>>>
>>> _______________________________________________
>>> hibernatespatial-users mailing list
>>> [hidden email]
>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>> hibernatespatial-users
>>
>>
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>> hibernatespatia
>> l-users
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>> hibernatespatial-users
>
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatia
> l-users
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

Olumee, Milan

Re: oracle 10g sql error

Reply Threaded

I thought if I repeat the first vertices (x,y) at the end it will close
the ring. Not sure what do you mean by offending SO_GEOMTRY, but here
is the sql stmt I used to insert the data to the table with a column
type of SDO_Geometery and the table structure

INSERT INTO target VALUES(
1,
null,
0,
1132,
MDSYS.SDO_GEOMETRY(
2003, -- 2-dimensional polygon
8307,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one rectangle (1003 =
exterior)
MDSYS.SDO_ORDINATE_ARRAY(-2.3077, 40.6154, 43.8462, 38.7692,
22.6154, 14.3077,
-3.2308, 15.692, -2.3077, 40.6154)
)
);

Table structure
( ID NUMBER(19,0),
..
..
LOCATION SDO_GEOMETRY,
PRIMARY KEY (ID);

Hopefully that is what you are asking!

Thanks & regards

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On
Behalf Of Karel Maesen
Sent: Monday, September 21, 2009 2:44 PM
To: Hibernate Spatial Users Discussion
Subject: Re: [hibernatespatial-users] oracle 10g sql error

You get that error because, apparently the SDO_GEOMETRY contains a
polygon formed by one or more linear rings, but the linear rings are
not closed. Could you post the offending SDO_GEOMTRY.

Regards,

Karel
]
On 21 Sep 2009, at 18:42, Olumee, Milan wrote:

Karel Maesen

Re: oracle 10g sql error

Reply Threaded

Hi,

I tested this case with the trunk version of Hibernate Spatial, and I
didn't get any problem retrieving the object and Geometry shown in
the INSERT statement.

The iIllegalArgumentException is thrown because Hibernate Spatial
tries to convert an SDO_GEOMETRY object it retrieved from the
database to a JTS Geometry object, and fails because the SDO_GEOMETRY
is invalid. You can test this by running the
SDO_GEOM.validate_geometry_with_context function (see the excellent
Oracle Spatial documentation for more information).

Regards,

Karel

On 21 Sep 2009, at 20:57, Olumee, Milan wrote:

> I thought if I repeat the first vertices (x,y) at the end it will
> close
> the ring. Not sure what do you mean by offending SO_GEOMTRY, but here
> is the sql stmt I used to insert the data to the table with a column
> type of SDO_Geometery and the table structure
>
> INSERT INTO target VALUES(
> 1,
> null,
> 0,
> 1132,
> MDSYS.SDO_GEOMETRY(
> 2003, -- 2-dimensional polygon
> 8307,
> NULL,
> MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one rectangle (1003 =
> exterior)
> MDSYS.SDO_ORDINATE_ARRAY(-2.3077, 40.6154, 43.8462, 38.7692,
> 22.6154, 14.3077,
> -3.2308, 15.692, -2.3077, 40.6154)
> )
> );
>
>
> Table structure
> ( ID NUMBER(19,0),
> ..
> ..
> LOCATION SDO_GEOMETRY,
> PRIMARY KEY (ID);
>
> Hopefully that is what you are asking!
>
> Thanks & regards
>
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Karel Maesen
> Sent: Monday, September 21, 2009 2:44 PM
> To: Hibernate Spatial Users Discussion
> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>
> You get that error because, apparently the SDO_GEOMETRY contains a
> polygon formed by one or more linear rings, but the linear rings are
> not closed. Could you post the offending SDO_GEOMTRY.
>
> Regards,
>
> Karel
> ]
> On 21 Sep 2009, at 18:42, Olumee, Milan wrote:
>
>> Thank you Karel. Your solution did resolve the issue with SRID, but
>> didn't return any results although there are records in the database.
>> Therefore, I try to use the SDO_filter where it does return
>> records in
>> SQL and give "points must form a closed linestring" exception in
>> hibernate
>>
>> here is the query that works in SQL but not in hibernate
>>
>> Select a.*
>> FROM target a
>> WHERE SDO_FILTER(a.location, mdsys.sdo_geometry(2003, 8307,NULL,
>> mdsys.SDO_ELEM_INFO_ARRAY(1,1003,1),
>> mdsys.SDO_ORDINATE_ARRAY(-2.3077, 40.6154, 43.8462, 38.7692, 22.6154,
>> 14.3077, -3.2308, 15.692, -2.3077, 40.6154)),
>> 'querytype=WINDOW') = 'TRUE';
>>
>> This is what I've done using hibernate spatial:
>>
>> Criteria testCriteria = session.createCriteria(Target.class);
>> //System.out.println("filter = "+filter);
>> testCriteria.add(SpatialRestrictions.filter("location",
>> filter));
>> List results = testCriteria.list();
>> if (results.size() > 0) {
>> System.out.println("after restult = " + results.get
>> (0));
>> }
>>
>> with filter being passed to the method as
>>
>> Geometry testGeom = new WKTReader().read("POLYGON ((-2.3077 40.6154,
>> 43.8462 38.7692, 22.6154 14.3077, -3.2308 15.692, -2.3077
>> 40.6154))");
>>
>>
>> I get the following exception
>>
>> Hibernate: select this_.idtarget_points as idtarget1_0_0_,
>> this_.LOCATION as LOCATION0_0_ from TARGET this_ where
>> SDO_FILTER(this_.LOCATION,?) = 'TRUE'
>> Exception in thread "main" java.lang.IllegalArgumentException: points
>> must form a closed linestring
>> at
>> com.vividsolutions.jts.geom.LinearRing.validateConstruction
>> (LinearRing.j
>> ava:95)
>> at com.vividsolutions.jts.geom.LinearRing.<init>(LinearRing.java:90)
>> at
>> com.vividsolutions.jts.geom.GeometryFactory.createLinearRing
>> (GeometryFac
>> tory.java:324)
>> at
>> org.hibernatespatial.oracle.SDOGeometryType.convertSDOPolygon
>> (SDOGeometr
>> yType.java:582)
>> at
>> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
>> (SDOGeometryType.
>> java:452)
>> at
>> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
>> (SDOGeometryType.
>> java:439)
>>
>>
>> Any idea?
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On
>> Behalf Of Karel Maesen
>> Sent: Monday, September 21, 2009 7:53 AM
>> To: Hibernate Spatial Users Discussion
>> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>>
>> Hi,
>>
>> To set the SRID use the Geometry.setSRID() method on the filter
>> object. That should be all
>>
>> Regards,
>>
>> Karel
>> On 18 Sep 2009, at 16:58, Olumee, Milan wrote:
>>
>>> Hi Karel,
>>>
>>> Thank you. Excuse my ignorant, I am very new to the topic. How
>>> would I
>>> setSRID in the code, I setup it up in the database to 8307 (for
>>> lat &
>>> lon)? I think my main problem is I don't know how to pass polygon
>>> dims
>>> to the query. I use this to setSRID.
>>>
>>> SDOGeometryType.SDO_GEOMETRY geom = new SDOGeometryType.SDO_GEOMETRY
>>> ();
>>> geom.setSRID(polygon.getSRID());
>>>
>>> Now I have error on the polygon not defined. Can you please help,
>>> or if
>>> you can direct me to some material that can help me get started.
>>>
>>> A little background on my project, I have a project that is build on
>>> hibernate and ESRI ArcSD and we are trying to convert that to oracle
>>> geospatial with hibernate.
>>>
>>> Best Regards
>>>
>>> Hila
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:hibernatespatial-users-
>>> [hidden email]] On
>>> Behalf Of Karel Maesen
>>> Sent: Friday, September 18, 2009 6:37 AM
>>> To: Hibernate Spatial Users Discussion
>>> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>>>
>>> Oracle gives an error when you try to perform a spatial query with a
>>> fiilter geometry which has a different spatial reference system ID
>>> (SRID) than the table you query. In your case, you specify a filter
>>> polygon which has no SRID. You should use the setSRID() method of
>>> Geometry to set the srid to the same value as the table.
>>>
>>> Regards,
>>>
>>> Karel
>>>
>>>
>>> On 17 Sep 2009, at 20:27, Olumee, Milan wrote:
>>>
>>>> I am getting trying to run the tutorial query with oracle10g, but
>>>> it is giving me "geometry objects are in different coordinate
>>>> systems" error.
>>>>
>>>>
>>>>
>>>> can you help please? I am very new to both spatial db and
>>>> hibernate.
>>>>
>>>>
>>>>
>>>> I am calling this function from the main method with the following
>>>> input:
>>>>
>>>>
>>>>
>>>> String testFilter ="POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>>>>
>>>>
>>>>
>>>> private List find(String wktFilter){
>>>>
>>>>
>>>>
>>>> WKTReader fromText = new WKTReader();
>>>>
>>>> Geometry filter = null;
>>>>
>>>> try{
>>>>
>>>> filter = fromText.read(wktFilter);
>>>>
>>>> } catch(ParseException e){
>>>>
>>>> throw new RuntimeException("Not a WKT String:" +
>>>> wktFilter);
>>>>
>>>> }
>>>>
>>>>
>>>>
>>>> System.out.println("Filter is : " + filter);
>>>>
>>>> Criteria testCriteria = session.createCriteria
>>>> (Target.class);
>>>>
>>>>
>>>>
>>>> testCriteria.add(SpatialRestrictions.within("location",
>>>> null, filter));
>>>>
>>>> List results = testCriteria.list();
>>>>
>>>> session.getTransaction().commit();
>>>>
>>>>
>>>>
>>>> return results;
>>>>
>>>> }
>>>>
>>>>
>>>>
>>>> Here is the generated HQL and error:
>>>>
>>>>
>>>>
>>>> Hibernate: select targetpoin0_.idtarget_points as idtarget1_0_,
>>>> targetpoin0_.RIGHT_ASCENSION as RIGHT2_0_, targetpoin0_.DECLINATION
>>>> as DECLINAT3_0_, targetpoin0_.IDTARGET as IDTARGET0_,
>>>> targetpoin0_.LOCATION as LOCATION0_ from TARGET targetpoin0_ where
>>>> MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
>>>> (targetpoin0_.LOCATION),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1
>>>>
>>>> 2009-09-17 14:13:04,679 WARN
>>>> (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 13295,
>>>> SQLState: 72000
>>>>
>>>> 2009-09-17 14:13:04,679 ERROR
>>>> (org.hibernate.util.JDBCExceptionReporter:78) - ORA-13295: geometry
>>>> objects are in different coordinate systems
>>>>
>>>> ORA-06512: at "MDSYS.SDO_3GL", line 4
>>>>
>>>> ORA-06512: at "MDSYS.MD2", line 758
>>>>
>>>> ORA-06512: at "MDSYS.SDO_GEOM", line 2622
>>>>
>>>> ORA-06512: at "MDSYS.SDO_GEOM", line 2649
>>>>
>>>> ORA-06512: at "MDSYS.ST_GEOMETRY", line 59
>>>>
>>>> ORA-06512: at "MDSYS.OGC_WITHIN", line 6
>>>>
>>>> _______________________________________________
>>>> hibernatespatial-users mailing list
>>>> [hidden email]
>>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>>> hibernatespatial-users
>>>
>>>
>>> _______________________________________________
>>> hibernatespatial-users mailing list
>>> [hidden email]
>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>> hibernatespatia
>>> l-users
>>> _______________________________________________
>>> hibernatespatial-users mailing list
>>> [hidden email]
>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>> hibernatespatial-users
>>
>>
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>> hibernatespatia
>> l-users
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>> hibernatespatial-users
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatia
> l-users
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

Olumee, Milan

Re: oracle 10g sql error

Reply Threaded

Hi Karel,

Thank you again. I fixed the SDO_GEOMETRY object by using the
SDO_GEOM.validate_geometry_with_context function. Now when I run the
validation query in the sql it return TRUE, which is telling me that
sdo_geom is valid.

select sdo_geom.validate_geometry_with_context(
MDSYS.SDO_GEOMETRY(2003,8307,null,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(-2.3, -40.6, 43, 38.7, 22, 14.3, -3.2, 15,
-2.3, -40.6)),0.05)
from dual;
==
Results
TRUE

Based on your email the query has no problem and because the results is
not a valid SDO_GEOMETRY I get the exception, unfortunately, after I
fixed the sdo_geom I still get the same error, any idea or suggestion?

Here is the exception I get

Exception in thread "main" java.lang.IllegalArgumentException: points
must form a closed linestring

Is this correct? Or do I need to convert the results to JTS Geometry
object?

testCriteria.add(SpatialRestrictions.filter("location", filter));
List results = testCriteria.list();

Here is the full error

linearRing = LINEARRING (-2.3 -40.6, 43 38.7, 22 14.3, -3.2 15, -2.3
-40.6)
Inside find method with Geom object -- Filter is : POLYGON ((-2.3 -40.6,
43 38.7, 22 14.3, -3.2 15, -2.3 -40.6))
Hibernate: select this_.idtarget_points as idtarget1_0_0_,
this_.RIGHT_ASCENSION as RIGHT2_0_0_, this_.DECLINATION as
DECLINAT3_0_0_, this_.LOCATION as LOCATION0_0_ from TARGET this_ where
SDO_FILTER(this_.LOCATION,?) = 'TRUE'
Exception in thread "main" java.lang.IllegalArgumentException: points
must form a closed linestring
at
com.vividsolutions.jts.geom.LinearRing.validateConstruction(LinearRing.j
ava:95)
at
com.vividsolutions.jts.geom.LinearRing.<init>(LinearRing.java:90)
at
com.vividsolutions.jts.geom.GeometryFactory.createLinearRing(GeometryFac
tory.java:324)
at
org.hibernatespatial.oracle.SDOGeometryType.convertSDOPolygon(SDOGeometr
yType.java:582)
at
org.hibernatespatial.oracle.SDOGeometryType.convert2JTS(SDOGeometryType.
java:452)
at
org.hibernatespatial.oracle.SDOGeometryType.convert2JTS(SDOGeometryType.
java:439)
at
org.hibernatespatial.AbstractDBGeometryType.nullSafeGet(AbstractDBGeomet
ryType.java:127)
at
org.hibernatespatial.GeometryUserType.nullSafeGet(GeometryUserType.java:
170)
at
org.hibernate.type.CustomType.nullSafeGet(CustomType.java:105)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
at
org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractE
ntityPersister.java:2092)
at
org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1371)
at
org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1299)
at org.hibernate.loader.Loader.getRow(Loader.java:1197)
at
org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:568)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loade
r.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94
)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.solers.sushi.Main.find(Main.java:207)
at com.solers.sushi.Main.main(Main.java:98)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)

Regards

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On
Behalf Of Karel Maesen
Sent: Tuesday, September 22, 2009 3:32 PM
To: Hibernate Spatial Users Discussion
Subject: Re: [hibernatespatial-users] oracle 10g sql error

Hi,

I tested this case with the trunk version of Hibernate Spatial, and I
didn't get any problem retrieving the object and Geometry shown in
the INSERT statement.

The iIllegalArgumentException is thrown because Hibernate Spatial
tries to convert an SDO_GEOMETRY object it retrieved from the
database to a JTS Geometry object, and fails because the SDO_GEOMETRY
is invalid. You can test this by running the
SDO_GEOM.validate_geometry_with_context function (see the excellent
Oracle Spatial documentation for more information).

Regards,

Karel

On 21 Sep 2009, at 20:57, Olumee, Milan wrote:

Karel Maesen

Re: oracle 10g sql error

Reply Threaded

Try the sdo_geom_validate_with_context against all records in the
database.

select sdo_geom.validate_geometry_with_context(location, 0.05), location
from target;

This will show all invalid geometries which cause problems on retrieval.

Regards,

Karel

On 23 Sep 2009, at 21:02, Olumee, Milan wrote:

> Hi Karel,
>
> Thank you again. I fixed the SDO_GEOMETRY object by using the
> SDO_GEOM.validate_geometry_with_context function. Now when I run the
> validation query in the sql it return TRUE, which is telling me that
> sdo_geom is valid.
>
> select sdo_geom.validate_geometry_with_context(
> MDSYS.SDO_GEOMETRY(2003,8307,null,
> MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
> MDSYS.SDO_ORDINATE_ARRAY(-2.3, -40.6, 43, 38.7, 22, 14.3, -3.2,
> 15,
> -2.3, -40.6)),0.05)
> from dual;
> ==
> Results
> TRUE
>
> Based on your email the query has no problem and because the
> results is
> not a valid SDO_GEOMETRY I get the exception, unfortunately, after I
> fixed the sdo_geom I still get the same error, any idea or suggestion?
>
> Here is the exception I get
>
> Exception in thread "main" java.lang.IllegalArgumentException: points
> must form a closed linestring
>
>
> Is this correct? Or do I need to convert the results to JTS Geometry
> object?
>
> testCriteria.add(SpatialRestrictions.filter("location", filter));
> List results = testCriteria.list();
>
> Here is the full error
>
> linearRing = LINEARRING (-2.3 -40.6, 43 38.7, 22 14.3, -3.2 15, -2.3
> -40.6)
> Inside find method with Geom object -- Filter is : POLYGON ((-2.3
> -40.6,
> 43 38.7, 22 14.3, -3.2 15, -2.3 -40.6))
> Hibernate: select this_.idtarget_points as idtarget1_0_0_,
> this_.RIGHT_ASCENSION as RIGHT2_0_0_, this_.DECLINATION as
> DECLINAT3_0_0_, this_.LOCATION as LOCATION0_0_ from TARGET this_ where
> SDO_FILTER(this_.LOCATION,?) = 'TRUE'
> Exception in thread "main" java.lang.IllegalArgumentException: points
> must form a closed linestring
> at
> com.vividsolutions.jts.geom.LinearRing.validateConstruction
> (LinearRing.j
> ava:95)
> at
> com.vividsolutions.jts.geom.LinearRing.<init>(LinearRing.java:90)
> at
> com.vividsolutions.jts.geom.GeometryFactory.createLinearRing
> (GeometryFac
> tory.java:324)
> at
> org.hibernatespatial.oracle.SDOGeometryType.convertSDOPolygon
> (SDOGeometr
> yType.java:582)
> at
> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
> (SDOGeometryType.
> java:452)
> at
> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
> (SDOGeometryType.
> java:439)
> at
> org.hibernatespatial.AbstractDBGeometryType.nullSafeGet
> (AbstractDBGeomet
> ryType.java:127)
> at
> org.hibernatespatial.GeometryUserType.nullSafeGet
> (GeometryUserType.java:
> 170)
> at
> org.hibernate.type.CustomType.nullSafeGet(CustomType.java:105)
> at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
> at
> org.hibernate.persister.entity.AbstractEntityPersister.hydrate
> (AbstractE
> ntityPersister.java:2092)
> at
> org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1371)
> at
> org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1299)
> at org.hibernate.loader.Loader.getRow(Loader.java:1197)
> at
> org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:568)
> at org.hibernate.loader.Loader.doQuery(Loader.java:689)
> at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
> (Loade
> r.java:224)
> at org.hibernate.loader.Loader.doList(Loader.java:2211)
> at
> org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
> at org.hibernate.loader.Loader.list(Loader.java:2090)
> at
> org.hibernate.loader.criteria.CriteriaLoader.list
> (CriteriaLoader.java:94
> )
> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
> at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
> at com.solers.sushi.Main.find(Main.java:207)
> at com.solers.sushi.Main.main(Main.java:98)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke
> (NativeMethodAccessorImpl.jav
> a:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke
> (DelegatingMethodAccessor
> Impl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at
> com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
>
> Regards
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Karel Maesen
> Sent: Tuesday, September 22, 2009 3:32 PM
> To: Hibernate Spatial Users Discussion
> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>
> Hi,
>
> I tested this case with the trunk version of Hibernate Spatial, and I
> didn't get any problem retrieving the object and Geometry shown in
> the INSERT statement.
>
> The iIllegalArgumentException is thrown because Hibernate Spatial
> tries to convert an SDO_GEOMETRY object it retrieved from the
> database to a JTS Geometry object, and fails because the SDO_GEOMETRY
> is invalid. You can test this by running the
> SDO_GEOM.validate_geometry_with_context function (see the excellent
> Oracle Spatial documentation for more information).
>
> Regards,
>
> Karel
>
> On 21 Sep 2009, at 20:57, Olumee, Milan wrote:
>
>> I thought if I repeat the first vertices (x,y) at the end it will
>> close
>> the ring. Not sure what do you mean by offending SO_GEOMTRY, but
>> here
>> is the sql stmt I used to insert the data to the table with a column
>> type of SDO_Geometery and the table structure
>>
>> INSERT INTO target VALUES(
>> 1,
>> null,
>> 0,
>> 1132,
>> MDSYS.SDO_GEOMETRY(
>> 2003, -- 2-dimensional polygon
>> 8307,
>> NULL,
>> MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one rectangle (1003 =
>> exterior)
>> MDSYS.SDO_ORDINATE_ARRAY(-2.3077, 40.6154, 43.8462, 38.7692,
>> 22.6154, 14.3077,
>> -3.2308, 15.692, -2.3077, 40.6154)
>> )
>> );
>>
>>
>> Table structure
>> ( ID NUMBER(19,0),
>> ..
>> ..
>> LOCATION SDO_GEOMETRY,
>> PRIMARY KEY (ID);
>>
>> Hopefully that is what you are asking!
>>
>> Thanks & regards
>>
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On
>> Behalf Of Karel Maesen
>> Sent: Monday, September 21, 2009 2:44 PM
>> To: Hibernate Spatial Users Discussion
>> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>>
>> You get that error because, apparently the SDO_GEOMETRY contains a
>> polygon formed by one or more linear rings, but the linear rings are
>> not closed. Could you post the offending SDO_GEOMTRY.
>>
>> Regards,
>>
>> Karel
>> ]
>> On 21 Sep 2009, at 18:42, Olumee, Milan wrote:
>>
>>> Thank you Karel. Your solution did resolve the issue with SRID, but
>>> didn't return any results although there are records in the
>>> database.
>>> Therefore, I try to use the SDO_filter where it does return
>>> records in
>>> SQL and give "points must form a closed linestring" exception in
>>> hibernate
>>>
>>> here is the query that works in SQL but not in hibernate
>>>
>>> Select a.*
>>> FROM target a
>>> WHERE SDO_FILTER(a.location, mdsys.sdo_geometry(2003, 8307,NULL,
>>> mdsys.SDO_ELEM_INFO_ARRAY(1,1003,1),
>>> mdsys.SDO_ORDINATE_ARRAY(-2.3077, 40.6154, 43.8462, 38.7692,
>>> 22.6154,
>>> 14.3077, -3.2308, 15.692, -2.3077, 40.6154)),
>>> 'querytype=WINDOW') = 'TRUE';
>>>
>>> This is what I've done using hibernate spatial:
>>>
>>> Criteria testCriteria = session.createCriteria(Target.class);
>>> //System.out.println("filter = "+filter);
>>> testCriteria.add(SpatialRestrictions.filter("location",
>>> filter));
>>> List results = testCriteria.list();
>>> if (results.size() > 0) {
>>> System.out.println("after restult = " + results.get
>>> (0));
>>> }
>>>
>>> with filter being passed to the method as
>>>
>>> Geometry testGeom = new WKTReader().read("POLYGON ((-2.3077 40.6154,
>>> 43.8462 38.7692, 22.6154 14.3077, -3.2308 15.692, -2.3077
>>> 40.6154))");
>>>
>>>
>>> I get the following exception
>>>
>>> Hibernate: select this_.idtarget_points as idtarget1_0_0_,
>>> this_.LOCATION as LOCATION0_0_ from TARGET this_ where
>>> SDO_FILTER(this_.LOCATION,?) = 'TRUE'
>>> Exception in thread "main" java.lang.IllegalArgumentException:
>>> points
>>> must form a closed linestring
>>> at
>>> com.vividsolutions.jts.geom.LinearRing.validateConstruction
>>> (LinearRing.j
>>> ava:95)
>>> at com.vividsolutions.jts.geom.LinearRing.<init>(LinearRing.java:
>>> 90)
>>> at
>>> com.vividsolutions.jts.geom.GeometryFactory.createLinearRing
>>> (GeometryFac
>>> tory.java:324)
>>> at
>>> org.hibernatespatial.oracle.SDOGeometryType.convertSDOPolygon
>>> (SDOGeometr
>>> yType.java:582)
>>> at
>>> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
>>> (SDOGeometryType.
>>> java:452)
>>> at
>>> org.hibernatespatial.oracle.SDOGeometryType.convert2JTS
>>> (SDOGeometryType.
>>> java:439)
>>>
>>>
>>> Any idea?
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:hibernatespatial-users-
>>> [hidden email]] On
>>> Behalf Of Karel Maesen
>>> Sent: Monday, September 21, 2009 7:53 AM
>>> To: Hibernate Spatial Users Discussion
>>> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>>>
>>> Hi,
>>>
>>> To set the SRID use the Geometry.setSRID() method on the filter
>>> object. That should be all
>>>
>>> Regards,
>>>
>>> Karel
>>> On 18 Sep 2009, at 16:58, Olumee, Milan wrote:
>>>
>>>> Hi Karel,
>>>>
>>>> Thank you. Excuse my ignorant, I am very new to the topic. How
>>>> would I
>>>> setSRID in the code, I setup it up in the database to 8307 (for
>>>> lat &
>>>> lon)? I think my main problem is I don't know how to pass polygon
>>>> dims
>>>> to the query. I use this to setSRID.
>>>>
>>>> SDOGeometryType.SDO_GEOMETRY geom = new
>>>> SDOGeometryType.SDO_GEOMETRY
>>>> ();
>>>> geom.setSRID(polygon.getSRID());
>>>>
>>>> Now I have error on the polygon not defined. Can you please help,
>>>> or if
>>>> you can direct me to some material that can help me get started.
>>>>
>>>> A little background on my project, I have a project that is
>>>> build on
>>>> hibernate and ESRI ArcSD and we are trying to convert that to
>>>> oracle
>>>> geospatial with hibernate.
>>>>
>>>> Best Regards
>>>>
>>>> Hila
>>>>
>>>> -----Original Message-----
>>>> From: [hidden email]
>>>> [mailto:hibernatespatial-users-
>>>> [hidden email]] On
>>>> Behalf Of Karel Maesen
>>>> Sent: Friday, September 18, 2009 6:37 AM
>>>> To: Hibernate Spatial Users Discussion
>>>> Subject: Re: [hibernatespatial-users] oracle 10g sql error
>>>>
>>>> Oracle gives an error when you try to perform a spatial query
>>>> with a
>>>> fiilter geometry which has a different spatial reference system ID
>>>> (SRID) than the table you query. In your case, you specify a filter
>>>> polygon which has no SRID. You should use the setSRID() method of
>>>> Geometry to set the srid to the same value as the table.
>>>>
>>>> Regards,
>>>>
>>>> Karel
>>>>
>>>>
>>>> On 17 Sep 2009, at 20:27, Olumee, Milan wrote:
>>>>
>>>>> I am getting trying to run the tutorial query with oracle10g, but
>>>>> it is giving me "geometry objects are in different coordinate
>>>>> systems" error.
>>>>>
>>>>>
>>>>>
>>>>> can you help please? I am very new to both spatial db and
>>>>> hibernate.
>>>>>
>>>>>
>>>>>
>>>>> I am calling this function from the main method with the
>>>>> following
>>>>> input:
>>>>>
>>>>>
>>>>>
>>>>> String testFilter ="POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>>>>>
>>>>>
>>>>>
>>>>> private List find(String wktFilter){
>>>>>
>>>>>
>>>>>
>>>>> WKTReader fromText = new WKTReader();
>>>>>
>>>>> Geometry filter = null;
>>>>>
>>>>> try{
>>>>>
>>>>> filter = fromText.read(wktFilter);
>>>>>
>>>>> } catch(ParseException e){
>>>>>
>>>>> throw new RuntimeException("Not a WKT String:" +
>>>>> wktFilter);
>>>>>
>>>>> }
>>>>>
>>>>>
>>>>>
>>>>> System.out.println("Filter is : " + filter);
>>>>>
>>>>> Criteria testCriteria = session.createCriteria
>>>>> (Target.class);
>>>>>
>>>>>
>>>>>
>>>>> testCriteria.add(SpatialRestrictions.within("location",
>>>>> null, filter));
>>>>>
>>>>> List results = testCriteria.list();
>>>>>
>>>>> session.getTransaction().commit();
>>>>>
>>>>>
>>>>>
>>>>> return results;
>>>>>
>>>>> }
>>>>>
>>>>>
>>>>>
>>>>> Here is the generated HQL and error:
>>>>>
>>>>>
>>>>>
>>>>> Hibernate: select targetpoin0_.idtarget_points as idtarget1_0_,
>>>>> targetpoin0_.RIGHT_ASCENSION as RIGHT2_0_,
>>>>> targetpoin0_.DECLINATION
>>>>> as DECLINAT3_0_, targetpoin0_.IDTARGET as IDTARGET0_,
>>>>> targetpoin0_.LOCATION as LOCATION0_ from TARGET targetpoin0_ where
>>>>> MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
>>>>> (targetpoin0_.LOCATION),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1
>>>>>
>>>>> 2009-09-17 14:13:04,679 WARN
>>>>> (org.hibernate.util.JDBCExceptionReporter:77) - SQL Error: 13295,
>>>>> SQLState: 72000
>>>>>
>>>>> 2009-09-17 14:13:04,679 ERROR
>>>>> (org.hibernate.util.JDBCExceptionReporter:78) - ORA-13295:
>>>>> geometry
>>>>> objects are in different coordinate systems
>>>>>
>>>>> ORA-06512: at "MDSYS.SDO_3GL", line 4
>>>>>
>>>>> ORA-06512: at "MDSYS.MD2", line 758
>>>>>
>>>>> ORA-06512: at "MDSYS.SDO_GEOM", line 2622
>>>>>
>>>>> ORA-06512: at "MDSYS.SDO_GEOM", line 2649
>>>>>
>>>>> ORA-06512: at "MDSYS.ST_GEOMETRY", line 59
>>>>>
>>>>> ORA-06512: at "MDSYS.OGC_WITHIN", line 6
>>>>>
>>>>> _______________________________________________
>>>>> hibernatespatial-users mailing list
>>>>> [hidden email]
>>>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>>>> hibernatespatial-users
>>>>
>>>>
>>>> _______________________________________________
>>>> hibernatespatial-users mailing list
>>>> [hidden email]
>>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>>> hibernatespatia
>>>> l-users
>>>> _______________________________________________
>>>> hibernatespatial-users mailing list
>>>> [hidden email]
>>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>>> hibernatespatial-users
>>>
>>>
>>> _______________________________________________
>>> hibernatespatial-users mailing list
>>> [hidden email]
>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>> hibernatespatia
>>> l-users
>>> _______________________________________________
>>> hibernatespatial-users mailing list
>>> [hidden email]
>>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>>> hibernatespatial-users
>>
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>> hibernatespatia
>> l-users
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
>> hibernatespatial-users
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatia
> l-users
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

Olumee, Milan

Re: oracle 10g sql error

Reply Threaded

Thank you very much. That was it, I had some invalid geometry in the
table, once I delete them it start working.

Appreciate all your helps.

Best Regards

hila

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On
Behalf Of Karel Maesen
Sent: Thursday, September 24, 2009 2:16 PM
To: Hibernate Spatial Users Discussion
Subject: Re: [hibernatespatial-users] oracle 10g sql error

Try the sdo_geom_validate_with_context against all records in the
database.

select sdo_geom.validate_geometry_with_context(location, 0.05), location
from target;

This will show all invalid geometries which cause problems on retrieval.

Regards,

Karel

On 23 Sep 2009, at 21:02, Olumee, Milan wrote: