Moving to SpatiaLite: best practice

[Jose Gomez-Dans]

Hi,
I'm doing some very simple PostGIS-related work: I store data in a table, and from time to time, I do simple stuff with the stored data, sometimes even actually using the geometry column!!! Anyway, PostGIS has been very useful so far, but I now have to move away from my work computer, and I would like to keep on working with my laptop. One way would be to install PostGIS on my laptop, but I am also thinking of giving my program to other people, who might not necessarily need or want to install PostGIS. To this end, spatialite is very handy. It's just a file, and I can use the same PostGIS stuff I've been using up to now. However, I need to move my tables to SpatialLite. In essence, I do have a number of tables, most of which relate a field to a geographical table. It should be trivial to dump schema and SQL and run it pass sqlite/spatialite. I was just wondering whether anyone had any recommendations or ideas on "best practice".

Thanks!
Jose

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paragon Corporation-2]

Jose,

From our experience -- the SQLite SQL syntax compatibility with PostgreSQL is pretty good, so that your dump idea should more or less work with minor massaging to load into SQLite.  Well it will probably work just fine for the regular data if you use the -D insert format of PostgreSQL of pg_dump

http://www.postgresonline.com/downloads/special_feature/postgresql83_pg_dumprestore_cheatsheet.pdf

 

So the only issue is the geometry output.  These will probably not be compatible.  You could use pgsql2shp and output using the -w switch which will output to WKT.

 

http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg

 

That may just work.  Haven't tried it myself so good luck and let us know how it turns out.

 

Leo

---

From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of Jose Gomez-Dans
Sent: Saturday, June 13, 2009 3:44 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Moving to SpatiaLite: best practice

Hi,
I'm doing some very simple PostGIS-related work: I store data in a table, and from time to time, I do simple stuff with the stored data, sometimes even actually using the geometry column!!! Anyway, PostGIS has been very useful so far, but I now have to move away from my work computer, and I would like to keep on working with my laptop. One way would be to install PostGIS on my laptop, but I am also thinking of giving my program to other people, who might not necessarily need or want to install PostGIS. To this end, spatialite is very handy. It's just a file, and I can use the same PostGIS stuff I've been using up to now. However, I need to move my tables to SpatialLite. In essence, I do have a number of tables, most of which relate a field to a geographical table. It should be trivial to dump schema and SQL and run it pass sqlite/spatialite. I was just wondering whether anyone had any recommendations or ideas on "best practice".

Thanks!
Jose

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paragon Corporation-2]

Oops slight correction.  Forget my comment about pgsql2shp.  I just realized what I was thinking doesn't work, so you may have to dump the geometry tables to shp and then load into SpatiaLite with the numerous loaders out there.

 

I suppose you can chain them like pgsql2shp | shp2pgsql -w but haven't tried that.

It would be nice if OGR2OGR supports this.  I assume that is in the works, but I think it only supports SQLite not SpatiaLite yet.

 

Leo

---

From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of Paragon Corporation
Sent: Saturday, June 13, 2009 10:22 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Moving to SpatiaLite: best practice

Jose,

From our experience -- the SQLite SQL syntax compatibility with PostgreSQL is pretty good, so that your dump idea should more or less work with minor massaging to load into SQLite.  Well it will probably work just fine for the regular data if you use the -D insert format of PostgreSQL of pg_dump

http://www.postgresonline.com/downloads/special_feature/postgresql83_pg_dumprestore_cheatsheet.pdf

 

So the only issue is the geometry output.  These will probably not be compatible.  You could use pgsql2shp and output using the -w switch which will output to WKT.

 

http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg

 

That may just work.  Haven't tried it myself so good luck and let us know how it turns out.

 

Leo

---

From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of Jose Gomez-Dans
Sent: Saturday, June 13, 2009 3:44 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Moving to SpatiaLite: best practice

Hi,
I'm doing some very simple PostGIS-related work: I store data in a table, and from time to time, I do simple stuff with the stored data, sometimes even actually using the geometry column!!! Anyway, PostGIS has been very useful so far, but I now have to move away from my work computer, and I would like to keep on working with my laptop. One way would be to install PostGIS on my laptop, but I am also thinking of giving my program to other people, who might not necessarily need or want to install PostGIS. To this end, spatialite is very handy. It's just a file, and I can use the same PostGIS stuff I've been using up to now. However, I need to move my tables to SpatialLite. In essence, I do have a number of tables, most of which relate a field to a geographical table. It should be trivial to dump schema and SQL and run it pass sqlite/spatialite. I was just wondering whether anyone had any recommendations or ideas on "best practice".

Thanks!
Jose

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paragon Corporation-2]

Jose,

 

Just occurred to me that Schuyler Erle had submitted a patch for this very thing that Leo was alluding to that would make chaining a little easier.

 

Though would be nice to have direct output compatibility without need for chaining

 

http://trac.osgeo.org/postgis/ticket/123

 

 Hope that helps,

Regina  

---

From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of Jose Gomez-Dans
Sent: Saturday, June 13, 2009 3:44 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Moving to SpatiaLite: best practice

Hi,
I'm doing some very simple PostGIS-related work: I store data in a table, and from time to time, I do simple stuff with the stored data, sometimes even actually using the geometry column!!! Anyway, PostGIS has been very useful so far, but I now have to move away from my work computer, and I would like to keep on working with my laptop. One way would be to install PostGIS on my laptop, but I am also thinking of giving my program to other people, who might not necessarily need or want to install PostGIS. To this end, spatialite is very handy. It's just a file, and I can use the same PostGIS stuff I've been using up to now. However, I need to move my tables to SpatialLite. In essence, I do have a number of tables, most of which relate a field to a geographical table. It should be trivial to dump schema and SQL and run it pass sqlite/spatialite. I was just wondering whether anyone had any recommendations or ideas on "best practice".

Thanks!
Jose

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Armin Burger]

On 14/06/2009 04:28, Paragon Corporation wrote:
> It would be nice if OGR2OGR supports this.  I assume that is in the
> works, but I think it only supports SQLite not SpatiaLite yet.

This should be supported in GDAL 1.7 release and is included in the
current development version. Compiling the GDAL svn trunk with support
for spatialite (on Unix with adding something like
--with-spatialite=/usr/local to the configure call) adds this
functionality. Export is done like

ogr2ogr -f "SQLite" -dsco "SPATIALITE=yes" ...

Armin


_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Mark Cave-Ayland-3]

Paragon Corporation wrote:

> Jose,
>  
> Just occurred to me that Schuyler Erle had submitted a patch for this
> very thing that Leo was alluding to that would make chaining a little
> easier.
>  
> Though would be nice to have direct output compatibility without need
> for chaining
>  
> http://trac.osgeo.org/postgis/ticket/123

Note that this patch won't apply "as-is" since the string-escaping parts
have already been applied to SVN trunk. Shouldn't be too far off though...


HTH,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: [phone]
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users