'How to query array of coordinate objects in MySQL
I am storing OCR text detected on images using their coordinate data, where the 'bounds' field of type JSON:
id: 89
image_id: 36
text: ITALIAN
bounds: [{"x": 160, "y": 51}, {"x": 225, "y": 50}, {"x": 225, "y": 63}, {"x": 160, "y": 64}]
The 'bounds' represent the coordinates of the box surrounding the string so that I can identify the string location on the image. How would I be able to query for words that are within a user defined bounding box? For example:
[{"x": 0, "y": 0}, {"x": 200, "y": 0}, {"x": 0, "y": 200}, {"x": 200, "y": 200}]
So far, what I have is:
SELECT * FROM bounding_boxes
where image_id = 36
and (JSON_EXTRACT(`bounds` , '$**.x') >= 0 or JSON_EXTRACT(`bounds` , '$**.x') <= 200)
and (JSON_EXTRACT(`bounds` , '$**.y') >= 0 or JSON_EXTRACT(`boundss` , '$**.y') <= 200);
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
