SDE Feature Class
Tags
There are no tags for this item.
SDE feature view of Springs and Seeps data in PROD (database). Request from Groundwater Programs Division to create this GIS view of springs and seeps from Station Header on PROD. The District uses this information for general mapping and cartographic purposes, and for small and medium scale hydrologic and assessment applications. SUPPLEMENTAL INFORMATION Internal SJRWMD NOTE: Springs and seeps locations are derived from lat/longs stored in the Station Header table within the Oracle database. Individual representatives of a spring's group (for example, the many springs that make up the Silver Springs group) are each represented by a point, rather than a single point per group. Any specific questions about the data in this view should be directed to the person who originally created the station in Station Header, listed with the 'Created By' field when doing a station search in the Station Header web app (http://dasprod/station/).
SDE feature view created from Station Header points (GISLIB.STN_ET_GEOM) and attribute data from PROD. Not used in any web application at this time. SDE view requested from Groundwater group.
SQL to generate STN_ET_SPRINGSEEP SDE view and intermediate views is documented in: X:\IR\Documentation\GIS Data Mgmt\SDE View Documentation\STN_ET_SPRINGSEEP_Sql.txt - as well as in the FGDC Metadata section
A spring is defined as a place where groundwater flows naturally onto the land surface or into a body of surface water. A spring's occurrence depends on the nature and relationships among topography, underlying geologic strata, and aquifer water levels. While some springs are isolated features, many are clustered into spring groups of varying size and areal extent. In addition to providing a unique recreational resource, springs provide important base flow to rivers and streams and critical habitat to plants and animals.
Most springs in SJRWMD occur in discharge areas of the Upper Floridan aquifer where limestone is at or near land surface, or where the sediments overlying Floridan limestones have been breached. The majority of the springs in SJRWMD are located along the major river systems of the St. Johns, Ocklawaha, and Wekiva rivers. This data layer also includes surficial aquifer springs, which are now called seeps, under nomenclature recently agreed upon by the Florida Geological Survey.
Springs are classified by magnitude based on the volume of flow, or discharge of water. The amount of water that flows from a spring depends on several factors, including rainfall, size of the caverns within the rocks, the water pressure in the aquifer, and the size of spring recharge basin. Human activities can also influence the volume of water that discharges from a spring; for example, increased groundwater withdrawals can reduce the hydraulic pressure in an aquifer, causing water levels to decline and spring flows to decrease. Since springs have dynamic flows dependent on climate, hydrogeology, and others factors, a spring classified as being a certain magnitude at one time may not continue to flow at that rate at other times.
Springs are classified by magnitude, which is based on the median value of all discharge measurements for the period of record. First magnitude springs discharge the greatest amount of water. The following is a summary of spring classification magnitude by discharge.
Spring Magnitude Discharge
1 100 cfs or more
2 10 to 100 cfs
3 1 to 10 cfs
4 0.223 cfs (100 gpm) to 1 cfs
5 0.0223 cfs (10 gpm) to 0.223 cfs
6 0.0022 cfs (1 gpm) to 0.0223 cfs
7 0.00028 cfs (1 pint/min) to 0.0022 cfs
8 0.00028 cfs or less
Units:
cfs = cubic feet per second
gpm = gallons per minute
pint/min = pints per minute
Conversions:
100 cfs = 64.6 million gallons per day
1 cfs = 646,316.9 gallons per day
1 gallons per minute = 0.0022 cfs
1 pint per minute = 0.00028 cfs
Attribute Descriptions:
OBJECTID- ESRI software unique object id for record.
SHAPE- ESRI software feature geometry type.
STN_ID - A unique feature identifier (numeric) in the SJRWMD's Oracle table, Station Header.
STN_NM- Unique official name (text) of the spring.
LAT_NO- Decimal Degree Location of Latitude.
LONG_NO- Decimal Degree Location of Longitude.
SRCE_OFFCL- Source Official Name of the Aquifer that is the source of the spring.
CNTY_ID- County ID code.
PNT_LOC_DS- Point Location Description. Not unique, not the official name of the spring, this is additional information on the spring/seep entity's location.
STN_CTGRY- Whether it is a Spring or Seep.
HISTORICAL- Historical Spring Magnitude; Pre-2003 median of all measurements.
SPRING_MAG- Magnitude of the spring - it's a value from 1-8, representing median of all discharge measurements for spring.
SPRING_M_1- Spring Magnitude Date; MM/DD/YYYY of last measurement used for the Magnitude median calculation.
The St. Johns River Water Management District, Department of Resource Management, Groundwater Programs Division, asks to be credited in derived products.
There are no access and use limitations for this item.
The St. Johns River Water Management District, Department of Resource Management, Groundwater Programs Division, asks to be credited in derived products.
Internal feature number.
ESRI
Feature geometry.
ESRI
SDE feature view of Spring & Seep stations from Station Header on PROD.Request from Groundwater group (Jill Stokes) to create this GIS view of current spring/seep stations.Process involves setting up views and materialized views of the three data models on PROD, followed by creating attribute and a final SDE feature view on the SDE database (GISLIB account) pointint to these PROD views and the SDE feature class GISLIB.STN_ET_GEOM.
Any specific questions about the data in this view should be directed to the person who originally created the station in Station Header, listed with the "Created By:" field when doing a station search in the Station Header web app:
http://dasprod/station/
Steps used to develop this SDE feature view - see Process Steps section.
..REQUIRED: The basis on which the time period of content information is determined.REQUIRED: Restrictions and legal prerequisites for accessing the data set.REQUIRED: Restrictions and legal prerequisites for using the data set after access is granted.Readme.txt
/* */
/* Steps to create GIS point data from the Spring & Seep database: */
/* (Note: this still includes all spring data records from the stn_et table, including "Group" records) */
/* */
/* Modified 2/17/2009 to add s.stn_ctgry_cd=3223 (Seep) to the initial view creation */
/* Select the data from the stn_et table (Oracle/Production) from RapidSQL: */
Step 1:
create or replace view v_springseep_stn
as select
cast(rownum as number(38)) objectid,
s.stn_id,
s.stn_nm,
s.lat_no,
s.long_no,
s.srce_id,
s.stn_stts_cd,
wt.srce_offcl_nm,
s.cnty_id,
s.pnt_loc_dsc,
s.div_id,
s.stn_ctgry_cd,
def.tp_dsc stn_ctgry,
(select s2.prpty_val from stn_prpty s2 INNER JOIN sjr_abbr_def_et def2 ON (s2.prpty_tp_id = def2.tp_id)
where s2.stn_id = s.stn_id and s2.prpty_tp_id=3220) historical_spring_mag,
(select s3.prpty_val from stn_prpty s3 INNER JOIN sjr_abbr_def_et def2 ON (s3.prpty_tp_id = def2.tp_id)
where s3.stn_id = s.stn_id and s3.prpty_tp_id=3221) spring_mag,
(select s4.prpty_val from stn_prpty s4 INNER JOIN sjr_abbr_def_et def2 ON (s4.prpty_tp_id = def2.tp_id)
where s4.stn_id = s.stn_id and s4.prpty_tp_id=3222) spring_mag_date
from
stn_et s LEFT OUTER JOIN wtr_srce_et wt ON (s.srce_id = wt.srce_id)
INNER JOIN sjr_abbr_def_et def ON (s.stn_ctgry_cd = def.tp_id)
where
(s.stn_tp_cd=913
and s.stn_ctgry_cd in (1513,3223)
and (s.srce_id in (12637,3713) or s.srce_id in (select w.srce_id from wtr_srce_et w where lower(w.srch_nm) like '%surficial%'))
and s.stn_stts_cd in (469,470))
or
lower(s.stn_nm) like '%double run spring%'
/
drop materialized view mv_springseep_stn
/
create materialized view mv_springseep_stn
refresh complete
start with sysdate
next sysdate + 1
as select
cast(objectid as number(38)) objectid,
stn_id,
stn_nm,
lat_no,
long_no,
srce_id,
stn_stts_cd,
srce_offcl_nm,
cnty_id,
pnt_loc_dsc,
div_id,
stn_ctgry_cd,
stn_ctgry,
historical_spring_mag,
spring_mag,
spring_mag_date
from
v_springseep_stn
/
create index idx_mv_springseep_stn_stnid on mv_springseep_stn(stn_id);
create index idx_mv_springseep_stn_objid on mv_springseep_stn(objectid);
grant select on mv_springseep_stn to public;
Step 2:
On sde dbs in gislib account, create a point feature classes (ST_GEOMETRY) based on the above materialized views. (GISLIB.V_SPRINGSEEP_STN)
connect gislib@sde
create or replace view v_mv_springseep_stn as select
cast(objectid as number(38)) objectid,
stn_id,
stn_nm,
lat_no,
long_no,
srce_id,
stn_stts_cd,
srce_offcl_nm,
cnty_id,
pnt_loc_dsc,
div_id,
stn_ctgry_cd,
stn_ctgry,
historical_spring_mag,
spring_mag,
spring_mag_date
from
gislib.mv_springseep_stn@prod;
--> old view name
XXXX sdetable -o create_view -T V_SPRINGS_STN -t "stn_et_geom,v_mv_springs_stn" -c "v_mv_springs_stn.objectid, v_mv_springs_stn.stn_id, v_mv_springs_stn.stn_nm, v_mv_springs_stn.lat_no, v_mv_springs_stn.long_no, v_mv_springs_stn.srce_id, v_mv_springs_stn.stn_stts_cd, v_mv_springs_stn.srce_offcl_nm, v_mv_springs_stn.cnty_id, v_mv_springs_stn.pnt_loc_dsc, v_mv_springs_stn.div_id, stn_et_geom.shape" -w "stn_et_geom.stn_id=v_mv_springs_stn.stn_id" -i esri_sde -s earth -u gislib -p ***** XXXX
--> new view name
sdetable -o create_view -T STN_ET_SPRINGSEEP -t "stn_et_geom,v_mv_springseep_stn" -c "v_mv_springseep_stn.objectid, v_mv_springseep_stn.stn_id, v_mv_springseep_stn.stn_nm, v_mv_springseep_stn.lat_no, v_mv_springseep_stn.long_no, v_mv_springseep_stn.srce_id, v_mv_springseep_stn.stn_stts_cd, v_mv_springseep_stn.srce_offcl_nm, v_mv_springseep_stn.cnty_id, v_mv_springseep_stn.pnt_loc_dsc, v_mv_springseep_stn.div_id, v_mv_springseep_stn.stn_ctgry_cd, v_mv_springseep_stn.stn_ctgry, v_mv_springseep_stn.historical_spring_mag, v_mv_springseep_stn.spring_mag, v_mv_springseep_stn.spring_mag_date, stn_et_geom.shape" -w "stn_et_geom.stn_id=v_mv_springseep_stn.stn_id" -i esri_sde -s earth -u gislib -p ****