Modeling Guidelines¶

Example use case¶

You want to store the history of edits to the geometry of a pipe-feature. For each edit, you want to keep track of when that modification was made.

The last edit is the current geometry of the feature. This is the main pipe geometry that you want to present to your users by default.

Proposed Solution: Store the geometries of the feature in a table separate from the table that contains the pipe-metadata. These are the historical. Link these to the pipe-table.

Use a view to links a pipe to its most recent geometry. Add this view to the gpkg_contents table.

Table Design¶

View Design¶

CREATE VIEW pipes_current AS
SELECT shape, max(date_created), type
FROM pipe_history as A JOIN pipes as B
ON A.pipe_id=B.pipe_id
GROUP BY B.pipe_id;

Table Entries¶

Example use case¶

You need to model a feature that has multiple geometries. Associated with this main feature, are ancillary features, each which its own properties and shape. For example, the main feature is a park with a polygon boundary. Associated with this park are entrances, which are modeled as points.

Proposed Solution¶

Store the main features separately from the ancillary features. This allows you to associate different metadata with each feature/subfeature.

The park – boundary + entrances – can be tied together by using a view. This provides the “overview” picture, which remodels the park as a collection of geometries.

Table Design¶

View Design¶

Note: This statement uses the ST_* functions from the GPKG SQLite extension (cf. https://bitbucket.org/luciad/libgpkg)

CREATE VIEW park_complete AS
SELECT A.park_id, ST_GeomFromText('GEOMETRYCOLLECTION(' || ST_AsText(A.shape) || ',' || group_concat(ST_AsText(B.shape)) ||')')  as shape
FROM park_json as A
JOIN park_entrances_json as B
ON A.park_id = B.park_id
GROUP BY A.park_id;

Table Entries¶

Example use case¶

For efficiency reasons, it is often desirable to have the same feature represented by different geometries of varying resolution. This is typically used for features with large extents, such as roads, rivers, municipal boundaries, etc …. The viewer application shows the coarser version of geometries at smaller scales (ie. zoomed out), while at larger scales (ie. zoomed in) it shows the most detailed version. In general, you would define a handful of scale-breaks upfront.

Proposed Solution¶

Suppose we have multiple resolutions for roads in a road dataset:

• Store all geometries in a table, separate from the properties of the road. Associate with each geometry the scale-break for which it applies. In our example, we are using the [min|max]scale_denominator columns to store the scale-break values.

• A view joins together the properties with the geometries. This view will in effect contain many duplicates and should not be rendered as-is.

This solution requires the viewer-application to apply the correct filtering, relying on the min-max scale associated with the geometry. That is, the application only renders the geometries which are appropriate for each scale-break. This is an application-level requirement, unrelated to the GeoPackage format, and the implementation depends on the particular product you would be using. For products that support SLD, this scale-based filtering is fairly straightforward. In your FeatureTypeStyle, create multiple rules, one for each scale-break on the map. In each rule, filter all the geometries that do not correspond to the desired scalebreak.

For example, in SLD, a rule to only show the roads with a coarse resolution (zoomed out) would look something like this:

<Rule>
  <Name>small scale</Name>
  <Description>
    <Title>small scaled roads</Title>
    <Abstract>shows only the low resolution roads</Abstract>
  </Description>
  <ogc:Filter>
    <ogc:PropertyIsGreaterThan>
      <ogc:PropertyName>min_scale_denominator</ogc:PropertyName>
      <ogc:Literal>2.0E8</ogc:Literal>
    </ogc:PropertyIsGreaterThan>
  </ogc:Filter>
  <MinScaleDenominator>2.0E8</MinScaleDenominator>
  <LineSymbolizer>
    <Stroke>
      <SvgParameter name="stroke">#0000ff</SvgParameter>
      <SvgParameter name="stroke-width">2</SvgParameter>
    </Stroke>
  </LineSymbolizer>
</Rule>

Table Design¶

View Design¶

CREATE VIEW  roads_all AS
SELECT  *
FROM road as A
JOIN road_geometries as B
ON A.road_id = B.road_id

Table Entries¶