Well Sample Data and GIS Development

 I looked at water quality tests for a few private wells in Maryland to be able to better use GIS tools. After reviewing public information I took note of key fields such as well depth. In order to show connections among the various aspects I started an entity relationship diagram, going from basic SQL to end up with 8 tables.

In order to keep consistent a few constraints were included, such as:

ALTER TABLE

  well

ADD CONSTRAINT

  well_fkey_property

FOREIGN KEY

  (property_fk)

REFERENCES

  property(name)

;

  I decided to connect wells to a property, and buildings likewise. Normally one well is allowed per property, though while a replacement is being drilled one property might have 2 wells, and if a well runs dry you might say the property has no well.

 The old forms (pre-2000?) have Maryland grid coordinates, with accuracy to 1,000 feet, while the newer forms have a place for latitude/longitude. Water quality tests done by commercial lab contain values of constituents ranging from coliform bacteria to lead and arsenic.

 Sometimes the applications have typos.

Oops, mixed up east and north. I did the same when I first tried to load point data into GIS; once I noticed the drift and switched the values the wellhead came online where it should be.

 After the table definitions and test data load I used the QGis example tutorials to include the bits required for my hand-curated data to be mapped correctly.

See: 

https://docs.qgis.org/3.28/en/docs/training_manual/spatial_databases/simple_feature_model.html

https://docs.qgis.org/3.28/en/docs/training_manual/spatial_databases/geometry.html

> insert into geometry_columns values ('','public','well','the_geometry',2,4326,'POINT');

> update well set the_geometry = 'SRID=4326;POINT(-75.882996 38.938629)' where name = 'CO-15-0020' ;

 

An 8-way join, or is 7-way?

select

  r.value ,                             -- value

  r.uom,                                -- units

  p.name as name_p,                     -- param

  substr(r.name,1,12) as name_r,        -- result

  s.well_fk as well,                    -- well

  s.name as name_s,                     -- sample

  substr(s.sample_place,1,4) as place,  --

  substr(p.descr,1,10) as param_desc,   --

  substr(c.name,1,8) as name_c,         -- corp

  w.depth,                              -- well

  w.pump_installed as pump,             -- well

  substr(o.lot,1,10) as lot,            -- prop

  e.name as person,                     -- people

  s.sample_date

from

  people e,

  building b,

  lab_corp c,

  lab_sample s,

  lab_sample_results r,

  parameter p,

  property o,

  well w

where

  e.building_fk = b.name     -- people in building

and

  b.property_fk = o.name     -- building on property

and

  s.well_fk = w.name         -- sample from well

and

  w.property_fk = o.name     -- well on property

and

  r.parameter_fk = p.name

and

  r.lab_sample_fk = s.name

and

  s.lab_corp_fk = c.name

;

What have I accomplished? Now I have environmental data in a reachable database, which I can edit via command line, or even using LibreOffice forms, and now view and update via QGis.

Next I will figure out the step up from points data (the well heads) to polygons (property lines and building walls). Then, with sample data, set up views with contaminant levels on the map.

Looks a lot like dBASE IV, right?