Because the system front-end uses the OpenLayers framework, the background database uses oracle spatial. We all know that the SDO_GEOMETRY of Oracle spatial is very complicated. If you use java APIs for a long time, you will have to deal with a lot of logic and data types. Today I have such a requirement. I used to perform polygon query. I thought of a good way to construct a wkt space string by openlayers, and then call the SDO_UTIL.FROM_WKTGEOMETRY function to convert wkt to SDO_GEOMETRY, then call SDO_ANYINTERACT to query the space. This method seems to be a problem. The SQL statement is as follows: select *
From TBSVRC_RESPUBLISHITEMS t
Where SDO_ANYINTERACT (F_SPATIALEXTENT, SDO_UTIL.from_wktgeometry ('polygon (80.83422302246095)
20.518481140136714,
120.4135076904297
20.518481140136714,
120.4135076904297
50.314989929199214,
80.83422302246095
50.314989929199214,
80.83422302246095
20.518481140136714) ') = 'true'
Run the SQL statement in the database. The result is an error: the input sdo_geometry is different from the srid of the database table sdo_geometry and cannot be queried in space. Suddenly I think that WKT can express spatial data information, but wood has SRID information. What should I do if I am dumb? Take a look at the functions of the SDO_UTIL package. You can also use the SDO_GEOMETRY function to set SRID. So I searched for the official oracle spatial documents online for half a day without any solution. I thought I 'd better analyze SDO_GEOMETRY. Hey, I suddenly found out that SDO_GEOMETRY is a type and itself is also a function. Take a closer look at the construction parameters. Oh, the problem is solved. The SQL is as follows:
Select *
From TBSVRC_RESPUBLISHITEMS t
Where SDO_ANYINTERACT (F_SPATIALEXTENT, sdo_geometry ('polygon (80.83422302246095)
20.518481140136714,
120.4135076904297
20.518481140136714,
120.4135076904297
50.314989929199214,
80.83422302246095
50.314989929199214,
80.83422302246095
20.518481140136714) ', 8307) = 'true'
Oracle Spatial loves you!