Food Service Mapping

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

https://data.imap.maryland.gov/datasets/maryland::maryland-census-data-census-tracts/explore

[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.

The tertiary phase: 3 Maps in One Vehicle

osm-and-auto-map

New to me EV has built-in GPS and navigation system. Android phone in car has GPS, and google map navigation routing system. Phone cannot access car GPS, to avoid duplication of effort. Car navigation map data might be aging, no updates without manual effort. Google maps keep personal placemarks, intentional or not.

Aim

Create a list of nearby charging stations with ratings, cost, owner/operator.

1. Just use Google
2. Use Google as little as possible (on an Android phone with GPS switched on)

For Option 2 I got 2 suggestions:

1. - OSMand
2. - @jspath55 Tried `Organic Maps`? @organicmaps 
1. Uses OpenStreetMaps: https://organicmaps.app/

I could not get  Organic Maps to do Android Auto, so OSMand was left to try.

https://osmand.net/docs/technical/osmand-file-formats/osmand-kml/ shows the general path I took using Google Earth and QGIS as alternate ways to produce a set of points.

Web site said $40US to connect to Android Auto. 3 tiers: free, one-time, subscription. Free version could only load a limited number of "offline" local maps (which has not been my experience so far).

Details

Use Viking and/or QGIS to record my points of interest (charging spots).

• Keep data in PostgreSQL database.
• Edit/view via LibreOffice.
• Export KML from QGIS, import to OSMand.

It has been a (rough) year since I worked on a town map, pulling in layers of county land info. Points and lines in a SQL structured database with QGIS access (R/W). First table create showed up a "non-geo". Icon was a table/sheet instead of dots, lines, or blobby GIS areas.

Second try was no better success than the first.

On the third try, I copied a table definition from an exported table that had geo-goo.

                        Table "public.chargers"

    Column    |         Type          | Collation | Nullable | Default

--------------+-----------------------+-----------+----------+---------

 station_id   | integer               |           | not null |

 address      | character varying(40) |           |          |

 zipcode      | integer               |           |          |

 brand        | character varying(40) |           |          |

 vendor_id    | integer               |           |          |

 grade        | character varying(12) |           |          |

 price        | double precision      |           |          |

 visited      | date                  |           |          |

 updated      | date                  |           |          |

 lat          | double precision      |           |          |

 lon          | double precision      |           |          |

 the_geometry | geometry(Point,4326)  |           |          |

 kwh          | double precision      |           |          |

 name         | character(40)         |           |          |

 note         | character(80)         |           |          |

Indexes:

    "chargers_pkey" PRIMARY KEY, btree (station_id)

Check constraints:

    "chargers_geometry_point_chk" CHECK (st_geometrytype(the_geometry) = 'ST_Point'::text OR the_geometry IS NULL)

Finally able to add points and see them on the map. So far, no dice adding data points from LO that show on the map.

Next step: export map layer of points as a KML file.

First import to test on Google Earth showed every location called "noname".

Some data fields peeked through as table-like sheets in Google Earth on a PC, but less structured on a tablet:

Load into OSMand from a network drive, and yes, places show up under my favorites. Added a "name" column to the underlying table fixed the "noname" issue.

Using the OSMand app as a driving map is slightly different than the car map or the Google map. The colors and details are clearer. Startup is more complex, but does work.

Oddities:

1. Route going through driveways
2. Non-connected road shows as "take the left fork"
3. Stream flow direction

The above stream shows 2 directions, as if at the top of a hill, or mound (blue arrows on blue dashed line).

Next question(s)

Use OSM data format instead of KML.

Is location data still going off-device? Will this option change that (limit or redirect)? Seems a lot of on-device processing based on battery drain (or charge slipperiness) and heat.