SELECT
  o.data_provider_id AS 'data_provider_id',
  o.data_resource_id AS 'data_resource_id', 
  /* dp.name AS 'Data_provider', */
  /* dr.name AS 'Dataset', */
  /* replace(IFNULL(r.collector_name, ""),  "\n", " ")  AS 'Collector_name',  */
  /* '' AS 'Collector_number',  */
  /* '' AS 'Field_number',  */
  /* '' AS 'GUID',  */
  /* o.occurrence_date AS 'Date_collected',   */
  /* r.institution_code AS 'Institution_code',  */
  /* r.collection_code AS 'Collection_code',  */
  /* r.catalogue_number AS 'Catalogue_number',   */
  lub.br_value AS 'Basis_of_record', 
  /* ir.url AS 'Image_url',   */
  /* o.modified AS 'last_indexed',    */
  /* r.identifier_name AS 'Identifier',   */
  /* r.identification_date AS 'Identification_date',   */
  /* replace(IFNULL(r.scientific_name, ""),  "\n", " ")  AS 'Scientific_name',     */
  /* replace(IFNULL(r.author, ""),  "\n", " ")  AS 'Author',   */
  nn.canonical AS 'Scientific_name_interpreted', 
  r.kingdom AS 'Kingdom_original',  
  /* kn.canonical AS 'Kingdom',   */
  r.phylum AS 'Phylum_original',  
  /* pn.canonical AS 'Phylum',   */
  r.class AS 'Class_original',  
  /* cn.canonical AS 'Class',   */
  r.order_rank AS 'Order_rank_original',   
  /* orn.canonical AS 'Order_rank',  */ 
  r.family AS 'Family_original',  
  /* fn.canonical AS 'Family',  */
  /* r.genus AS 'Genus_original',   */
  /* gn.canonical AS 'Genus',   */
  /* replace(IFNULL(r.country, ""),  "\n", " ")  AS 'Country',   */
  /* replace(IFNULL(o.iso_country_code, ""),  "\n", " ")  AS 'Country_interpreted',   */
  /* replace(IFNULL(r.locality, ""),  "\n", " ")  AS 'Locality',    */
  /* r.county AS 'County',   */
  /* r.continent_ocean AS 'Continent_or_Ocean',   */
  /* r.state_province AS 'State_Province',    */
  /* c.region AS 'Region',    */
  /* dp.iso_country_code AS 'Provider_country',   */
  r.latitude AS 'Latitude', 
  /* o.latitude AS 'Latitude_interpreted',  */
  r.longitude AS 'Longitude', 
  /* o.longitude AS 'Longitude_interpreted',  */
  r.lat_long_precision AS 'Coordinate_precision',  
  o.geospatial_issue AS 'geospatial_issue',
  /* o.cell_id AS 'cell_id',   */
  /* o.centi_cell_id AS 'centi_cell_id',   */
  /* r.min_depth AS 'Min_depth',   */
  /* r.max_depth AS 'Max_depth',   */
  /* r.min_altitude AS 'Min_altitude',  */  
  /* r.max_altitude AS 'Max_altitude',   */ 
  /* r.altitude_precision AS 'Altitude_precision',   */
  /* concat('http://data.gbif.org/occurrences/',o.id) AS 'gbif_portal_url',  */
  /* concat('http://data.gbif.org/ws/rest/occurrence/get/',o.id) AS 'gbif_webservice_url',  */
  o.id AS 'occurrence_id'  
  /* o.nub_concept_id AS 'nub_concept_id'  */
FROM
  occurrence_record o
    JOIN data_resource dr ON dr.id = o.data_resource_id 
    JOIN data_provider dp ON o.data_provider_id = dp.id 
    LEFT JOIN country c ON o.iso_country_code = c.iso_country_code 
    LEFT JOIN lookup_basis_of_record lub ON o.basis_of_record=lub.br_key
    LEFT JOIN image_record ir ON o.id = ir.occurrence_id  
    INNER JOIN raw_occurrence_record r ON o.id=r.id 
    INNER JOIN taxon_concept nc ON o.nub_concept_id=nc.id 
      INNER JOIN taxon_name nn ON nc.taxon_name_id=nn.id 
      LEFT JOIN taxon_concept kc ON nc.kingdom_concept_id=kc.id 
        LEFT JOIN taxon_name kn ON kc.taxon_name_id=kn.id 
      LEFT JOIN taxon_concept pc ON nc.phylum_concept_id=pc.id 
        LEFT JOIN taxon_name pn ON pc.taxon_name_id=pn.id 
      LEFT JOIN taxon_concept cc ON nc.class_concept_id=cc.id 
        LEFT JOIN taxon_name cn ON cc.taxon_name_id=cn.id 
      LEFT JOIN taxon_concept oc ON nc.order_concept_id=oc.id 
        LEFT JOIN taxon_name orn ON oc.taxon_name_id=orn.id 
      LEFT JOIN taxon_concept fc ON nc.family_concept_id=fc.id 
        LEFT JOIN taxon_name fn ON fc.taxon_name_id=fn.id 
      LEFT JOIN taxon_concept gc ON nc.genus_concept_id=gc.id 
        LEFT JOIN taxon_name gn ON gc.taxon_name_id=gn.id   
WHERE
    o.phylum_concept_id = 49
  AND
    o.latitude IS NOT NULL
  AND
    o.longitude IS NOT NULL
INTO OUTFILE '/var/lib/mysql/Magnoliophyta_20120514.txt';