I have a table named fields with a column named geoJSON. The geoJSON column is of type JSON which stores an actual GeoJSON object. I am trying to take a list of coordinates which are POINTS (single latitude and longitude) and then select the rows for which the POINTS are in. If a row is return multiple times, a count needs to be returned as well within that row of how many points were contained within the rows geoJSON column.
Unfortunately, I cannot even get the GeoJSON column to return as Well-Known-Text (WKT) which can then be used with ST_Contains to see if the points are within the geoJSON column. Any help would be greatly appreciated.
The following query provides the following error: Invalid GIS data provided to function st_geomfromtext.
SELECT ST_AsGeoJSON(ST_GeomFromText(JSON_EXTRACT(`fields`.`geoJSON`, "$.geometry"))) FROM `fields`;
The following query produces this error: Geometry byte string must be little endian.
SELECT ST_AsWKT(`fields`.`geoJSON`->>"$.geometry") AS `polygon` FROM `fields`;
The following query does successfully return the geometry object inside the fields column that is a key within the object.
SELECT `fields`.`geoJSON`->>"$.geometry" AS `polygon` FROM `fields`;
Thank you