LIVE Services

BRERC Query Tool

Tables Available
The main table for species queries in BQT is mapinfo_fast_access. It is used exclusively in the Query Builder. The others have more specific uses. For example if you 'select * from bad_grid_ref' you will get no more than 100 returned. To locate them all you can instead 'select * from from mapinfo_fast_access where no_fig_grid is null'.

Quick Tips

Use the builder Columns... button to see the columns available

Use the builder Values... button to see the domain of a column

Use 'select * from foo limit 1' to see columns available for other tables

Use the Query Runner for most queries

Cut-and-paste queries from here, or Load... from files

Use limits to avoid nasty accidents

Use Ctrl-Enter for line breaks

Uppercase columns must be double-quoted (PostgreSQL assumes lowercase)

Use Table/Export... to write out a file (eg csv) from MapInfo

Examples Using Record Summaries
The 'gmap_' tables are pre-built record summaries generated specifically for Interactive Maps. They are quite expensive to produce. Please let me know if you have ideas for extending this schema.

Total records
select sum("RECORDS") as "RECORDS" from gmap_taxa
Records per 1km square
select "RECORDS", "GRID_REF" from gmap_grid order by 1,2

Using sub-queries and/or grouping anyone can produce more detailed stats directly from the raw data. Have a go, or get help for this sort of thing.

Year date vs date entered
select md_year_date, extract(year from md_date_enter) as md_year_enter, count(*) as records from mapinfo_fast_access group by 1,2 order by 1,2
Records (and %) in best years
select records, 100records/total as percent, year from (select count() as total from mapinfo_fast_access) foo, (select md_year_date as year, count(*) as records from mapinfo_fast_access group by 1) bar order by 1 desc limit 10

Data Enquiry Examples
There is a separate folder of sql scripts for data enquiries. The examples included in the BQT Help are now obsolete.

Up