Storing Coordinates in a Database

Coordinates look trivial, yet small decisions in the database cause bugs that surface months later: swapped columns, rounding drift, or values that fail validation. This page covers the basics.

Type and precision

Latitude ranges from -90 to 90 and longitude from -180 to 180. In SQL, DECIMAL(9,6) holds both: six digits after the decimal point and up to three before it.

latitude  DECIMAL(9,6) NOT NULL,
longitude DECIMAL(9,6) NOT NULL

Six decimals is roughly 0.1 m of precision at the equator, finer than a UPCS cell, which is 3 m by 3 m. There is no benefit in storing fifteen digits that your source device could not measure anyway.

Be consistent about order

Many bugs come from swapped values. Conventions differ between systems: most APIs say "latitude, longitude", while GeoJSON and many spatial functions use longitude first. Name your columns latitude and longitude explicitly and avoid anonymous coords or point strings.

Validate on the way in

Reject values outside the valid ranges and reject 0, 0 unless you truly mean it, since it is a common placeholder for "missing". See validating coordinates before API calls.

Keep the code too

Store the UPCS code alongside the coordinates: upcs_code VARCHAR(16). The code is human-friendly and the coordinates are machine-friendly. The same coordinates always produce the same code, so either can be used to cross-check the other. See storing a UPCS code in your database.

When to use spatial types

If you only store and display points, two decimal columns are fine. If you need to query "within 5 km" or "inside this polygon", a spatial type with an index is far better; see PostGIS and geospatial basics.

Privacy

A precise coordinate can identify a person's home. Store only what you need and for as long as you need it; see location data and GDPR concepts.