Designing a geodatabase schema for GNSS point observations

Model the objects before choosing columns

A GNSS point database can represent physical features, individual observations or accepted positions. These are related but different concepts. Decide which tables or layers describe each one before building the schema. One asset may have many observations and one approved coordinate at a given release. A single flat table can conceal those relationships and make updates difficult to interpret.

Write a short description of the intended lifecycle: creation, review, acceptance, revisit and retirement. Identify who can change an accepted position and how the evidence is retained. The schema should support that process rather than only the first import. A technically valid point layer is not yet a maintained survey database.

Continue learning Exporting GNSS data to GIS without losing its meaning

Use identifiers that do not depend on geometry

Assign stable feature and observation identifiers. Do not use the coordinate itself as the identity of an object, because a later measurement or transformation may change the values. Keep external asset identifiers in separate fields with their source. Preserve leading zeros and character case according to the project’s rules rather than allowing a spreadsheet to reinterpret them as numbers.

Define uniqueness constraints where the chosen database supports them and test how imports handle duplicates. A repeated observation identifier should trigger review, while several observations linked to one feature may be legitimate. Keep a cross-reference for retired or merged identifiers. This prevents future datasets from losing their connection to earlier field records and photographs.

Continue learning Handing GNSS survey coordinates to CAD teams without losing meaning

Separate geometry from coordinate provenance

Store the layer’s coordinate reference explicitly and preserve source reference information for imported observations. Include datum realisation, coordinate epoch and height type where relevant to the project. A geometry column may carry a CRS identifier without documenting every transformation or local calibration that produced its values. The provenance fields should fill that information gap.

If coordinates are transformed for delivery, retain the original observation or a traceable source record. Record the operation and model versions used. Avoid storing several unexplained coordinate pairs in one row. Each pair should have a defined purpose, reference and relationship to the accepted geometry so a reviewer can identify which values are authoritative for a particular release.

Continue learning Designing GIS field forms for GNSS surveys

Define measurement and quality fields carefully

Include observation method, correction source, antenna height convention and relevant quality evidence. Document units and the meaning of receiver-provided indicators. Do not label an internal precision estimate as an independently demonstrated accuracy. The schema should allow independent check results and review status to be recorded separately from the instrument’s own output.

Provide fields for target definition and local limitations such as obstruction, soft ground or offset derivation. Keep unknown values distinguishable from zero. A zero offset, a missing offset and an offset that was not measured are different states. Thoughtful field definitions reduce the need for later users to infer meaning from a generic note or an empty cell.

Continue learning Validating CSV coordinate imports from GNSS surveys

Preserve observation history and review decisions

Use dates and status fields to distinguish raw, reviewed, accepted, superseded and rejected observations. Keep the reason for a rejection or coordinate correction. A later accepted position should not require deletion of the evidence that came before it. This history helps explain whether a changed coordinate reflects a better measurement, a reference update or a physical change in the feature.

Link review decisions to the responsible role and release version according to the project process. Avoid making acceptance status depend only on whether a point falls within a map boundary. Spatial plausibility is one check, but identity, reference compatibility and observation quality also matter. The database should support these different assessments without collapsing them into one undocumented flag.

Continue learning Exporting GNSS data to GIS without losing its meaning

Connect attachments through explicit relationships

Photographs, sketches and raw files should link through stable keys or a documented attachment mechanism. Do not rely on the order of files in a folder. Include attachment type and relevant dates where useful. A point with several observations may have different photos for each visit, and that distinction should remain recoverable.

Test attachment behaviour through backup, export and transfer to the receiving system. Relative paths can break when folders are moved, while copied files can lose their relationship to database records. A small end-to-end test is more useful than assuming that an attachment visible in the authoring application will automatically appear for every recipient.

Continue learning Handing GNSS survey coordinates to CAD teams without losing meaning

Validate imports and exports as data transformations

Create a sample with long identifiers, unknown values, special characters, dates, offsets and multiple observations. Move it through the intended formats and applications. Compare field names, types, values and geometry after the round trip. Some formats impose limitations that require an explicit export mapping rather than a blind save-as operation.

Keep the authoritative schema and a data dictionary under version control or another controlled release process. Document derived delivery fields and any lost information. If a simplified export is required, preserve the complete database separately. A recipient should know whether a delivered point layer is the full observation archive or a reduced accepted-position view.

Continue learning Designing GIS field forms for GNSS surveys

Deliver a schema another team can maintain

Provide field definitions, units, coded values, relationships, reference metadata and update rules. Include examples of a new feature, a revisit and a superseded observation. State which geometry is authoritative for the release and where supporting evidence is stored. This makes the database useful beyond the original surveyor’s personal knowledge.

Review the schema after the first operational update, not only after initial collection. A model that handles new points well may still fail when assets are merged, moved or retired. A dependable GNSS database preserves identity and provenance through these changes. Its purpose is to keep coordinates understandable as evidence, rather than merely store a large number of point geometries.

Continue learning Validating CSV coordinate imports from GNSS surveys

Handle an improved coordinate without losing history

Suppose a provisional asset position is later replaced by a checked survey observation. Keep the original observation with its method and status, add the new observation and update the accepted-position relationship through the approved review process. The asset identifier remains stable. This allows existing maintenance records and photographs to continue referring to the same physical object.

Record why the new position was adopted and which release includes it. If the difference is unexpected, investigate reference compatibility and identity before declaring that the asset moved. A coordinate update and a physical relocation are different events. The schema should make both possible to represent clearly so later analysis does not interpret an improvement in measurement as a change in infrastructure.

The accepted-position view can then select the approved observation while the historical observation table remains unchanged. Document that selection rule and test it after a revisit. A map user may only need the current position, but a survey reviewer needs the earlier evidence as well. Providing both views through a clear schema allows ordinary use to remain simple without sacrificing traceability. It also makes future coordinate transformations or asset relocations easier to represent because the database already separates identity, measurement history and the current adopted spatial state.

Continue learning Exporting GNSS data to GIS without losing its meaning