Geographic Information Systems Asked on April 27, 2021
Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using a Docker image kartoza/postgis:13.1
.
A dump of the database was taken from the first machine using
pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc --file=/tmp/foo.dump
and then restored on the database on the second machine using
pg_restore --clean --dbname=foo /tmp/foo.dump
When we view a query result using a GUI software TablePlus, we noticed that the column named coordinates
of type Geography
contains values that look very different after restoring.
Query Result on 1st Machine (pg_dump source):
SELECT coordinates FROM locations LIMIT 5;
Query Result on 2nd Machine (after pg_restore):
SELECT coordinates FROM locations LIMIT 5;
However, our app that queries this database for coordinate data appears to be plotting the data correctly on a map. This makes me think that the data on both machines are the same, but stored using different representations.
Question: Why did the Geography
data values in the column coordinates
changed, and how can we restore from the dump while keeping the original data values?
Update: Tried using -b
when performing pg_dump
, but the problem persist.
pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc -b --file=/tmp/foo.dump
Update 2: d+ locations
on both databases show the same type for coordinates
1st Machine (pg_dump source):
Table "public.locations"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------+-----------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('locations_id_seq'::regclass) | plain | |
coordinates | geography(Point,4326) | | |
2nd Machine (after pg_restore):
Table "public.locations"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------+-----------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('locations_id_seq'::regclass) | plain | |
coordinates | geography(Point,4326) | | | | main | |
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP