Food banks run by charitable organizations are a public service where private entities fill in where government runs short. Picking up, storing, and sharing food throws lifelines to those in need. I volunteered to create updated maps for Scouting America and decided to use QGis and related software tools.
(1) OSM
Going from the lowest level up, I added OpenStreetMaps (OSM). This is an easy drag-and-drop from the base QGIS sources into the current project. Depending on the desired result, I change the transparency from none to 50% more or less.
The standard OSM layer sources from openstreetmap.org. I've found, using tools like Viking, that variations on the main source can be used, and I prefer to try the Humanitarian one. Eventually I found the wiki page which let me set the feed, like this:
https://wiki.openstreetmap.org/wiki/Raster_tile_providers
https://a.tile.openstreetmap.fr/hot/{z}/{x}/{y}.png
(2) Org
The second layer is the organization table. These have street addresses in the source, so I geo-located them with a Census Bureau tool that works via command line.
Once located, the QGIS data are converted to geometry. In a PostgreSQL database, I imported organization details, which had only street addresses not geo-data. To convert from the address to a lat/lon point I found a Census page that I could script.
$ lynx -dump "https://geocoding.geo.census.gov/geocoder/locations/address?street=10001%20Bird%20River%20Rd&state=md&zip=21220&benchmark=2020" | grep Interpolate
Interpolated Longitude (X) Coordinates: -76.432431362395
Interpolated Latitude (Y) Coordinates: 39.356117523642
Ran the conversion steps and viewed points and polygons. Once the latitude and longitude are set, the PostGIS function to make this into valid point(s) I used is:
UPDATE org.org SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
(3) Tract shapes
Next level layer shows US Census tracts. I found a couple sources of these shape files, and used the Maryland State data.
https://www.census.gov/cgi-bin/geo/shapefiles/index.php?year=2020&layergroup=Census+Tracts
[omitting how to add this layer from ZIP or shape files]
To match the boundaries of the local district I wrote a filter, first including only Baltimore County tracts and next excluding specific tracts not in scope.
QGIS lets me save and load a filter definition, more or less a SQL code snippet.
<Query>"COUNTYFP"='005' and "tractce" NOT IN (
'400100',
'400200',
'400400',
[...]
'494201',
'494202',
'980000',
'980100',
'980200',
'9999999999'
)
</Query>
The blank line prior to the query end lets me run a unique sort to more quickly find gaps and add or subtract tracts.
(4) Unit Tracts Coverage
The coverage mapping uses a simple table with tract and unit/organizations. In order to connect these to the tracts I created a view.
create view
food.v_unit_tract
as
select
t.id,
t.geom,
t.geoid,
t.name,
c.unit_id,
c.updated
from
food.tl_2020_24_tract t,
food.coverage c
where
c.tract = t.geoid
;
(5) Zipcode Centroids
The top layer adds US zip code references, with centroids (points) rather than perimeters as the former is easier to include, thanks to online contributors.
Download the CSV file with roughly 30,000 zipcodes along with a latitude/longitude pair, add to the PostGIS database, then pull into QGIS.
create table zip_centroid (
zip char(5), -- double 0s
lat float,
lng float,
primary key (zip)
)
;
$ COPY zip.zip_centroid from '/tmp/US20Codes20201320Data.txt' csv header;
$ ALTER TABLE zip.zip_centroid add column geom geometry(Point, 4326);
$ UPDATE zip.zip_centroid SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
Other tweaks
After a layer from a local shape file is tweaked, I can "drag and drop" that from the Layers view into the data browser view, effectively building the table and data definitions on the fly. Once in a local database, the shape can be accessed from other QGIS apps locally. Otherwise, if you load the saved project in a different system the source may not be found. The lesson I learned here is the filter query does not move to the database, only the resulting rows, so if the filter needs to be updated you need to drop and reload.
~zoom~
Technical details
The above map was done primarily on a Raspberry Pi 500, with PostgreSQL version 15 and PostGIS extensions. This page was helpful with the basic install sequence:
Doing a screenshot on a Pi seems to be a mental block for me, and the image above was taken on a FreeBSD system accessing the Pi over ssh. Wayland is not the way.
QGIS on the Pi is a bit stale compared to other OS; I have:
https://github.com/qgis/QGIS/tree/release-3_22
Wish list items include (a) masking the zip code layer more; as of this writing the filtered range includes:
"zip" >= '20000' and "zip" < '22000'
(b) Easier user interface to add/drop coverage than SQL table access; at least I can have a spreadsheet-like look-and-feel with a LibreOffice form. Around 150 tracts in scope, larger volumes would preclude manual row maintenance.
(c) picking georeferenced and minimum 300 dots per inch on every PDF export so Avenza can use it for on-device mapping
No comments:
Post a Comment