SQLite output format support for shp2pgsql

[schuyler.erle]

Please find attached a patch that adds a -L option to shp2pgsql provide special-case data typing and string formatting for SQLite-compatible output.

I have tested the output with SQLite 3.5.2 and it correctly creates and populates a table. The geometries are correctly stored as WKB in an SQLite BLOB column.

Granted there are other ways to import spatial data into an SQLite table, but shp2pgsql is the fastest way that I've found to convert both Shapefiles *and* DBF files into SQL suitable for quick import into a database, which is very handy for piping to SQLite's in-memory databases.

[schuyler.erle]

Please note that the patch was generated against the SVN trunk revision 3813.

[mcayland]

Hi Schuyler,

I'm not sure about the SQLite changes, however it shows the valid point that we shouldn't be using E'....' escaping in shp2pgsql. We should change shp2pgsql so that it uses proper SQL escaping (we'd lose pre-8.1 compatibility in the process, but that is no longer an issue in trunk) which should help a little.

I'll go ahead and make the changes in trunk.

ATB,

Mark.

[schuyler.erle]

 http://postgis.googlecode.com/issues/attachment?aid=-8922292038306053755&name=shp2pgsql-sqlite-mode.patch

[jadams]

Supporting other databases is a little outside the realm of a loader for PostGIS, which is an extension for PostgreSQL.

I looked into having an option to generate pure SQL-99 output, using a BLOB or similar, which at least would not be specific to a different database. Unfortunately BLOB support varies among databases too much for it to be useful (PostgreSQL doesn't use standard syntax, among others).

Closing as invalid.