Spatial indexes and Unable to execute a query with a subquery using a join on spatial data with another table. - Source

Revision: 137078
User: Pascal Boulesteix
Date: 2024-03-21 14:28

When I query my database using spatial functions, queries are sanctioned by an error message
Unable to execute a query with a subquery using a join on spatial data with another table.
Having read the various posts on the subject, I have come to delete one of the spatial indexes even though it is a Point type column.
Without this index, queries work slowly but they don't crash.

Below is a query used; after dropping the index on wnat_gps.p_coordonnees_spacial, the query works.

SELECT SITE.set_name, SITE.site_name, wnat_gps.unik_lieudit AS unik_lieudit, wnat_gps.unik AS unik_gps, 'G' AS Type_Localite, point(0,0) AS lieudit_p_coordonnees_spacial, wnat_gps.p_coordonnees_spacial AS gps_p_coordonnees_spacial, ST_ASTEXT(ST_CENTROID(SITE.g_coordinates_spacial)) AS Centroid FROM ( SELECT wnat_geometries_objets.unik AS unik_geometrie_objet, wnat_geometries_ensembles.unik_organism, wnat_geometries_ensembles.unik_territoire, organisms.Organism, wnat_geometries_sets.set_name, wnat_geometries_sites.site_name, wnat_geometries_objets.g_coordonnees_spacial, wnat_geometries_sites.unik_geometrie_ensemble, wnat_geometries_objets.unik_geometrie_site FROM wnat_geometries_sites INNER JOIN wnat_geometries_ensembles ON wnat_geometries_sites.unik_geometrie_ensemble = wnat_geometries_ensembles.unik INNER JOIN wnat_geometries_objets ON wnat_geometries_objets.unik_geometrie_site = wnat_geometries_sites.unik INNER JOIN organisms ON wnat_geometries_ensembles.unik_organism = organisms.unik WHERE wnat_geometries_ensembles.ensemble_name = 'CDL Corse (PI)' ) SITE, wnat_gps WHERE ST_CONTAINS(SITE.g_coordonnees_spacial, wnat_gps.p_coordonnees_spacial)

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Spatial indexes and Unable to execute a query with a subquery using a join on spatial data with another table. - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB