Interaction and syncing between PostGIS and SpatiaLite

[Alexander Bruy]

Hi all,

Our company have one large main PostGIS database and use QGIS to work with
it (view and edit). In this database we store information about network
infrastructure (devices and connections between them).Also we have "standalone"
users and "mobile" users with laptops. Mobile users often works in places
with limited internet connection and have troubles when working with main
DB (layers opens too slow and editing is non comfortable in QGIS).

Usually users don't need access to all DB, they works with data for one
city/town or even one district. So we decide to develop plugin for QGIS that
allow users:
1. select small part of main DB (area of interest) and load it in local
SpatiaLite database
2. edit this local database
3. send changes to main PostGIS database.

I've search and find several topics in different cites [1-4] but don't
find any existing solution. Exporting from PostGIS to SpatiaLite can be
done with GDAL [3]. A think inverse action is possible too.

So my question are:
Does anyone working on similar tool or interested in it?
Is this idea real? May be there is some potential problems with realisation?

Also any hints and suggestion are welcome.

Thanks

[1] http://groups.google.com/group/spatialite-users/browse_thread/thread/2604059057a4e488#
[2] http://postgis.refractions.net/pipermail/postgis-users/2009-June/023707.html
[3] http://trac.osgeo.org/postgis/wiki/SpatiaLite
[4] http://answerpot.com/showthread.php?737147-PostGIS+to+SpatiaLite

--
Alexander Bruy

[Andrea P.]

Hi,

Even we are study-ing the use of splite as a db offline for postgres,
and as a format to share Gis data from outs db.

i do some test and the results are very good.

We are many data archive.
Usually to an user we must give many shapefile with a size from few
hundred Mb to some Gb.
I tested the splite v 2.3.1 with some Gbytes of data without any
apparent problem in a standard use (zoom, pan, rendering, etc..)

No test for editing because my goal is forward a viewing system.

(Just now we start to exploring the editing of splite with qgis.)

For viewing , I think the splite is better then shapefile. The only
limit is the poor diffusion of splite.
But its availability on the qgis is a good point of start for it.

Thanks to the goos results for viewing on qgis we decide to develope
some tools to help us for produce the splite from postgres.

The first is with a qgis plugin we are now developing for extract from
postgres some table with geometric field clipped on polygons and dump
on splite dbs.
This plugin use gdal and It will be available early in a qgis
repository for all the community.

The second is for an offline record and will use gdal to load on a
splite all the tables of a postgres schema with a batch file.
To have a more general tool and to avoid to list each single table we
use the config parameter

PG_LIST_ALL_TABLES=YES

and also to avoid to load even the view we funded the add to gdal the
parameter

PG_SKIP_VIEWS=YES

Regards,

On 15 Set, 09:05, Alexander Bruy <alexander.b...@gmail.com> wrote:
> Hi all,
>
> Our company have one large main PostGIS database and use QGIS to work with
> it (view and edit). In this database we store information about network
> infrastructure (devices and connections between them).Also we have "standalone"
> users and "mobile" users with laptops. Mobile users often works in places
> with limited internet connection and have troubles when working with main
> DB (layers opens too slow and editing is non comfortable in QGIS).
>
> Usually users don't need access to all DB, they works with data for one
> city/town or even one district. So we decide to develop plugin for QGIS that
> allow users:
>  1. select small part of main DB (area of interest) and load it in local
>     SpatiaLite database
>  2. edit this local database
>  3. send changes to main PostGIS database.
>
> I've search and find several topics in different cites [1-4] but don't
> find any existing solution. Exporting from PostGIS to SpatiaLite can be
> done with GDAL [3]. A think inverse action is possible too.
>
> So my question are:
>   Does anyone working on similar tool or interested in it?
>   Is this idea real? May be there is some potential problems with realisation?
>
> Also any hints and suggestion are welcome.
>
> Thanks
>

> [1]http://groups.google.com/group/spatialite-users/browse_thread/thread/...
> [2]http://postgis.refractions.net/pipermail/postgis-users/2009-June/0237...