It is possible to perform custom queries on the database using classical SQL queries.
The database use Postgis as a RDBMS and the labels are within a JSONB columns. Therefore, SQL queries use the classical postgres syntax and any operators from postgis and jsonb are available.
Please refer to data model page for informations about the database schema.
-- Select the zones ID, geometry (as postgis internal representation) and labels (as JSON) columns.
SELECT musee.*
-- Select and join from the zone table (for the communes and the museums)
FROM iedb_zone AS musee
JOIN iedb_zone AS commune
-- We need only the museums within the selected communes
ON ST_CONTAINS(commune.geometry, musee.geometry)
-- We like the musee table to contains only the museum dataset
WHERE musee.source_geom_id = 'musees_musees'
-- We like the commune table to contains only the commune dataset
AND commune.source_geom_id = 'ign_admin_express_commune'
-- We like the commune table to contains only the city of Rennes
AND commune.labels ->> 'nom' = 'Rennes';
Don’t forget to limit the number of elements returned (using the LIMIT). Your query might get killed because we think it is too heavy on the server.
The Zone table is partitioned by source_geom_id.
Queries with strict filters on this column will be significantly faster.
The web interface automatically fetch the full Zone from a Zone ID. If you are not exporting the result and only viewing them in the web interface, it might be faster to only query a Zone ID and let the interface handle the rest.
You can display a Zone geometry column as human friendly WKT or GeoJSON using the ST_AsText(geometry) and ST_AsGeoJSON(geometry) PostGis functions respectively.
They are easier to work with than the default PostGIS geometry string representation.