Garmin overlays of GNIS names

I've just been reminded of something I did last summer during the Pakistan Flooding: creating a garmin overlay of names from GNIS.

I'd been trying to enter names from old US DMA (military) maps and was getting frustrated by the difficulty of being sure of names. Plus with landsat imagery it was possible to identify villages, but I wanted to put names to them. I downloaded the GNIS names for Pakistan, originally with the aim of creating a separate layer in JOSM for assigning names. This required converting the names into OSM format (I have no skills suitable for building a tile or other service). In the end it was still difficult to reconcile names to residential areas (mainly GNIS quality issues, I suspect), but I did create a transparent 'names' overlay for garmins from the data using mkgmap.

As it might be useful again, I thought I'd quickly describe the process. Essentially there were seven steps, each simple on its own, but involving a bit of trial and error before I got there:

1. Create an image table of GNIS format in a PostGIS database. By image table I mean one that copies the columns of the source data one-for-one, often using character columns even if the data is numeric to avoid losing data. type conversion can be carried out as a post-processing step.
2. Load the downloaded GNIS data with a simple COPY statement.
3. Add a POINT geometry column to the table. Populate this from the lat/lon in the data
   
4. Create a 'simple' OSM schema in the same database.
5. Populate the nodes table of the schema, either directly from the image table or from a table created to post-process the data.
6. Extract the data to an OSM XML file using Osmosis.
7. Build a garmin file using mkgmap.

I'll go through each of these steps in more detail below.

The starting point is to have a PostgreSQL db server running, with PostGIS installed. I have a template database which has not only PostGIS, but also hstore, and the OSM simple schema already present. I can then immediately create a new database specifically for a given OSM task. What I describe works for Postgres DB 8.4, PostGIS 1.5 on Windows with Osmosis 0.38 and mkgmap r1443. Small modifications may be needed for other software versions.

Image Table:

-- Table: pakistan_gnis_names

DROP TABLE pakistan_gnis_names;

CREATE TABLE pakistan_gnis_names
(
region_font_code numeric(1),
unique_feature_id numeric(38),
unique_name_id numeric(38),
latitude numeric(10,8),
longitude numeric(11,8),
latitude_ddmmss character(6),
longitude_ddmmss character(7),
military_grid_ref character(15),
jog_ref character(7),
feature_classification character(1),
feature_fesignation_code character(5),
populated_Place_Class numeric(1),
primary_country_code character(2),
first_order_admin_code character(2),
population numeric(38),
elevation numeric(38),
secondary_country_code character varying(128),
name_type character(2),
language_code character(3),
short_form character varying(128),
sort_name_ro character varying(255),
full_name_ro character varying(255),
full_name_nd_ro character varying(255),
sort_name_rg character varying(255),
full_name_rg character varying(255),
full_name_nd_rg character varying(255),
note text,
modified_date date
)
WITH (
OIDS=FALSE
);
ALTER TABLE pakistan_gnis_names OWNER TO jrc;

Add & Populate Geometry Columns
(in this case in a table just containing a subset of names):

drop table pk_gnis_p_name;
create table pk_gnis_p_name as
select * from pakistan_gnis_names
where feature_classification ='P';
alter table pk_gnis_p_name drop wgs_geom;
select AddGeometryColumn('pk_gnis_p_name','wgs_geom',4326,'POINT',2);
select populate_geometry_columns()
;
update pk_gnis_p_name
set wgs_geom = ST_geometryFROMTEXT(
'POINT('|| longitude || ' ' || latitude || ')'
,4326);

Create OSM 'Simple Schema':
This needs to be compatible with the version of Osmosis being used. Normally the DDL are in one of the osmosis distribution sub-directories. If doing in a dedicated database just use the default names, otherwise stick a prefix on the standard table & index names. I used 'pk' for Pakistan.

Populate the NODE table

SQL to be added. This is the only table needing populating so a single simple statement is needed, but you do need to use hstore syntax these days. This is a single insert into the nodes table, but as several columns are NOT NULL we need to ensure that we create suitable values. Fortunately, referential integrity is not enforced across tables so we don't have to worry about ensuring the reference values are populated (although it's not a bad idea). Furthermore, the data is not being merged with any OSM data. For gnis data there is already a unique identifier column so we can use this to identify the node. Version, changeset and user can all be given a standard default value (0, -1 are most commonly used).

INSERT INTO nodes (id, "version", user_id, changeset_id, tstamp, tags, geom)
SELECT unique_feature_id as id
, 0 as "version"
, 0 as user_id
, 0 as changeset_id
, clock_timestamp as tstamp
, 'name' => full_name_ro as tags
, wgs_geom as geom
FROM pk_gnis_names

Extract with osmosis:
This is a standard extract using --rp and --wx flags. See Detailed usage.

Create a specific style for mkgmap:
I created a special style for mkmap called gnis_names. A zip of the subdirectory is here.

I'll add a bit more about the logic later.

Build the Garmin IMG file using mkgmap:
This is the command I used to build the overlay. Additional work is required to get this into MapSource or integrate it into a gmapsupp.img file. I used MapSourceToolkit to change the registry settings on windows to see it in mapsource and thereby transfer it to my garmin. Usual stuff applies about family/product & file names.

java -ea -Xmx1536M -jar mkgmap.jar --mapname=88031021
--preserve-element-order
--style-file=resources\styles\gnis_points --transparent --description="GNIS Pop Places PK"
--series-name="GNIS Pop Places PK" --input-file=pk_gnis_names.osm --product-id=88 --family-id=1 --product-name="PK GNIS" --overview-mapname=88031020 --tdbfile

I'll add a bit more to this later & tidy it up too.