Preparing Source Feature data from a Local Biotics for use in RARECAT

1. Run a query in a Local Biotics that returns SF centroids. Two possible examples below were provided by Frank Price of FNAI.
2. Prepare the query.
   1. Edit the SName.
   2. Edit the codes in the last line tell it what to exclude. It is highly recommended that extirpated (X and X?) are excluded. Others should be excluded based on the knowledge of the element in review. Note: RARECAT includes an EO rank filter, so these ranks could be included for comparison purposes.

15           F

16           H

17           X

18           F?

19           H?

20           X?

3a. Run this query in Biotics Query Builder which uses the sci_name function, which not all programs may have (see #3b for alternative).

select

SOURCE_FEATURE_ID

, gv.x LONGITUDE

, gv.y LATITUDE

from

SOURCE_FEATURE_REP_PROJ

, table(sdo_util.getvertices(

     mdsys.sdo_cs.transform(

     SDO_GEOM.SDO_CENTROID(SOURCE_FEATURE_REP_PROJ.shape,0.005)

    , 0.005, 4326)

    )) gv

WHERE source_feature_id IN

(SELECT source_feature_id FROM source_feature WHERE sci_name(element_subnational_id) = 'Najas filifolia')

AND  source_feature_id NOT IN

(SELECT source_feature_id FROM

eo LEFT OUTER JOIN eo_source_feature eo_sf

ON eo.eo_id = eo_sf.eo_id

WHERE d_basic_eo_rank_id IN (16,17,20))

3b.    Run this alternative query in Biotics Query Builder which does not use the sci_name function.

select

SOURCE_FEATURE_ID

, gv.x LONGITUDE

, gv.y LATITUDE

from

SOURCE_FEATURE_REP_PROJ

, table(sdo_util.getvertices(

    mdsys.sdo_cs.transform(

    SDO_GEOM.SDO_CENTROID(SOURCE_FEATURE_REP_PROJ.shape,0.005)

    , 0.005, 4326)

    )) gv

WHERE source_feature_id IN

    (

    SELECT source_feature_id

    FROM source_feature SF

    LEFT OUTER JOIN ELEMENT_SUBNATIONAL EST

    ON SF.ELEMENT_SUBNATIONAL_ID = EST.ELEMENT_SUBNATIONAL_ID

    LEFT OUTER JOIN SCIENTIFIC_NAME SN

    ON EST.SNAME_ID = SN.SCIENTIFIC_NAME_ID

    WHERE SN.SCIENTIFIC_NAME = 'Najas filifolia'

    )

AND  source_feature_id NOT IN

(SELECT source_feature_id FROM

eo LEFT OUTER JOIN eo_source_feature eo_sf

ON eo.eo_id = eo_sf.eo_id

WHERE d_basic_eo_rank_id IN (16,17,20))

4.     Save your file as .csv.