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? 

Turning "Meats and Bounds" into a Map

I came across a section of a scanned municipal blueprint, finding an inset map labelled with "Meats & Bounds", an obvious misspelling of the surveying phrase "Meets & Bounds", the term for property boundary descriptions in a specific style.

As I wanted to learn more about the town boundaries for equity reasons I also wanted to continue my journey learning how to use QGIS and related tools. In the QGIS tutorials, I found ways to bring shape files into a PostGIS database, and export shape files from the database. This might be elementary to some, and obscure to others. My notes that follow are the steps I took to go from a relatively simple property boundary (a rectangle) into digital formats, with comments on accuracy or lack thereof.

It turns out the description of the original town boundaries are encapsulated in state law or regulations, https://mgaleg.maryland.gov/Pubs/LegisLegal/Muni-Charters/2019-municipal-charter-ridgely.pdf 

Beginning at a point where the center of Central Avenue intersects the center of Pennsylvania Railroad tracks and running with the center of said tracks in a northeasterly direction one–half mile to point in center of said tracks, which said point shall be the beginning for the outlines of said corporation;

The Pennsylvania Railroad has not existed as a corporation for decades, and the tracks through the town are pulled up in places and turned into hiking and biking trails, making the "center of said tracks" less obvious now than it was in 1937.

I began by running Viking on NetBSD 10 (RC3) to create points and lines. I tried both kilometer and mile markers, wishing for feet and yards given passages such as "parallel with Central Avenue forty–eight hundred and seventy–five feet." 

Eyeballing the distances and bearings, I came out close to the starting rectangle edge, though clearly not a professional surveying effort.

I exported the waypoint (Origin) and the tracks as lines 1 through 6. With these files loaded into Google Earth Pro via a KML format, I found the shapes where I expected them, off a bit from OpenStreetMap boundaries, but not too bad for a first try. To simplify the shape, I drew a rectangle/polygon, then loaded that into QGis as a vector layer.

You can see the "miss" on the lower left corner of the municipal boundary. Half a city block, or less...

To go further, I exported the shape from QGis into the 5-sided Esri shape folder, then brought the result into a PostGIS database following the tutorial steps: https://docs.qgis.org/3.28/en/docs/training_manual/spatial_databases/import_export.html

The shape looks like the one above (as it should); the database columns include the renamed "description" as "descriptio".

Warning 1: Field Name of width 255 truncated to 254.

Warning 6: Normalized/laundered field name: 'Description' to 'Descriptio'

Warning 1: Field Descriptio of width 255 truncated to 254.

Now I can load the same shapes into different QGs versions (Windows/BSD/Linux) and not worry about "version too old/new" messages.