# Native Replication of Postgres Geospatial Data

[PostGIS](https://postgis.net/documentation/getting_started/) extension enables you to efficiently store and query geospatial data in Postgres. It introduces spatial data types such as [GEOMETRY](https://postgis.net/workshops/postgis-intro/geometries.html) and [GEOGRAPHY](https://postgis.net/workshops/postgis-intro/geography.html), which lets you store `point`s, `polygon`s, `line`s and so on. PostGIS also supports a large set of [SQL functions](https://postgis.net/docs/manual-1.5/ch08.html) and [spatial indexes](https://www.crunchydata.com/blog/the-many-spatial-indexes-of-postgis) to effectively manipulate spatial data.

The reason you chose PostGIS is that you want your geospatial workload to be treated as a first-class citizen. You should expect no less when it comes to replicating your geospatial database, instead of geospatial being an afterthought.

In this blog, we will explore what it takes to natively replicate PostGIS data from Postgres to other data stores.

## Status Quo

We've overwhelmingly heard from customers that existing data movement tools are not reliable in replicating geospatial data from Postgres. Common issues include -

1. **Suboptimal Data Type Conversion** - converting spatial types to STRING or JSON rather than using native spatial types supported by the target.
    
2. **Data Loss** - skipping whole rows when encountering incompatible types leading to loss of data.
    

## Replicating Geospatial data from Postgres

At PeerDB, we are building a specialized data-movement tool for Postgres. With that spirit, we took a [**step forward**](https://github.com/PeerDB-io/peerdb/pull/516) to add extensive geospatial support while replicating data from Postgres to Snowflake. This includes -

%[https://www.loom.com/share/771ef42e09854fcfabec71f4fb1a849c] 

Demo of replicating Postgres Geospatial data incl. POINT, LINE, POLYGON etc of GEOMETRY and GEOGRAPHY types to Snowflake.

1. **Support for GEOMETRY and GEOGRAPHY data types:** These types are stored as [GEOMETRY](https://docs.snowflake.com/en/sql-reference/data-types-geospatial#label-data-types-geography) and [GEOGRAPHY](https://docs.snowflake.com/en/sql-reference/data-types-geospatial#label-data-types-geometry) types on Snowflake.
    
2. **Replicating Points, Lines and Polygons:** A GEOMETRY or GEOGRAPHY in Postgres can be any of a Point, Line or Polygon. PeerDB handles replicating all these data types to Snowflake.  
    Let's see how to ingest `Point`,`Line` and `Polygon` to a Postgres table:
    
    ```sql
         CREATE TABLE locations (
             id serial PRIMARY KEY,
             location_name VARCHAR(255),
             location_geometry GEOMETRY,
             location_geography GEOGRAPHY
         );
         -- Inserting a point with generic geometry and geography
         INSERT INTO locations VALUES (1,'Point A', 
             ST_GeomFromText('POINT(1 2)'), 
             ST_GeogFromText('POINT(1 2)'));
         -- Inserting a line with generic geometry and geography
         INSERT INTO locations VALUES (2, 'Line AB', 
             ST_GeomFromText('LINESTRING(1 2, 3 4, 5 6)'), 
             ST_GeogFromText('LINESTRING(1 2, 3 4, 5 6)'));
         -- Inserting a polygon with generic geometry and geography
         INSERT INTO locations VALUES (3, 'Polygon ABCD', 
             ST_GeomFromText('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'), 
             ST_GeogFromText('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'));
        
    ```
    
3. **Replicating GEOMETRYCOLLECTION**: A geometry can represent a collection of Lines, Points and Polygons. PeerDB handles replicating `GEOMETRYCOLLECTION` from Postgres to Snowflake.  
    Let's see how to insert a `GEOMETRYCOLLECTION` to a Postgres table:
    
    ```sql
         /*
         Inserting a GEOMETRYCOLLECTION into the generic 
         geometry column
         */
         INSERT INTO locations (location_name, 
         location_geometry, 
         location_geography)
         VALUES (
             'GeometryCollection',
             ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 2), 
                 LINESTRING(1 2, 3 4))'),
             ST_GeogFromText('GEOMETRYCOLLECTION(POINT(1 2), 
                 LINESTRING(1 2, 3 4))')
         );
            
    ```
    
4. **Error handling for invalid Geometries:** A difference between Postgres and Snowflake is that Postgres lets you store invalid Geometries and Snowflake doesn't. For example, a line with a single point can be stored in Postgres but not in Snowflake. In such a scenario, while replicating data from Postgres, PeerDB captures the invalid data in logs and stores `null` for that column value on Snowflake.  
    Let's see how an invalid `GEOMETRY` looks like in Postgres
    
    ```sql
         source=> SELECT ST_IsValid(ST_MakeLine('POINT(1 2)'::geometry));
         NOTICE:  Too few points in geometry component 
         at or near point 1 2
          st_isvalid
         ------------
          f
         (1 row)
        
    ```
    

## Conclusion

We added extensive support to replicate geospatial data from Postgres to Snowflake. We will be expanding this to other data stores such as BigQuery and Clickhouse. Please [reach out](https://www.peerdb.io/sign-up) to us if you want this support for other data stores.

If you want to give PeerDB a try for replicating geospatial data from Postgres, these links should prove useful: :)

1. [**Quickstart**](https://docs.peerdb.io/quickstart)
    
2. [**PeerDB's Github repo**](https://github.com/PeerDB-io/peerdb)
    
3. [**Join PeerDB's Slack community**](https://join.slack.com/t/peerdb-public/shared_invite/zt-1wo9jydev-EXInbMtCtpAKFFWdi7QvLQ)
    
4. [**PeerDB docs**](https://docs.peerdb.io/introduction)