PostGIS – Running a PostGIS Query on a SpatiaLite Database

ogr2ogrpostgisspatialite

I like using SpatiaLite because, for me, it's easier to manage files on my computer instead of having tables inside a PostgreSQL database. However, there are sometimes I need PostGIS functions that I can't find equivalents on SpatiaLite (like the ST_Dump function for example).

So, even though I know I can import my SpatiaLite database to a PostGIS database for using a specific function, I've started to wonder if there's any alternative way of doing that, perhaps a tool like ogr2ogr that uses a PostGIS dialect in a SpatiaLite file (apparently not possible with ogr2ogr as discussed on this GitHub thread)… Is it possible? Can I make PostGIS queries on SpatiaLite files? Without importing my SpatiaLite file to PostGIS?

Additional Information

I understand that I can run queries on PostgreSQL from the command line using psql like the following:

psql postgresql://user:passwds@host:port/database -c "SELECT * FROM table"

On this GitHub thread, it's described a way of using the following command to do something similar to what I want:

ogr2ogr -f PostgreSQL PG: source && psql -c 'sql' && ogr2ogr dest PG: && psql -c 'SELECT * FROM table'

I think there's a solution mixing these two command lines (ogr2ogr and psql), but I didn't manage to make it work by trying to tweak them together so far.

Best Answer

You can use SpatiaLite tables as if they were PostGIS tables but without importing them physically by using the OGR Foreign Data Wrapper https://github.com/pramsey/pgsql-ogr-fdw.

Once the virtual FDW table is created you can query the SpatiaLite database with a PostgreSQL client. Also ogrinfo and ogr2ogr can automatically see the FDW tables.

A complete workflow with a test:

  1. Use ogr_fdw_info and get the SQL to use

    ogr_fdw_info -s c:\test\airports.sqlite -l airports

  2. Run the SQL with PostgreSQL client

CREATE SERVER myserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'c:\test\airports.sqlite',
format 'SQLite');
    
CREATE FOREIGN TABLE airports (
fid bigint,
geom Geometry(Point,4326),
name varchar,
country varchar,
timezone varchar
) SERVER myserver
OPTIONS (layer 'airports');

  1. Test with ogrinfo. ST_Dump is supported.

    ogrinfo PG:"[connection_string]" -sql "select st_dump(geom) from airports limit 1" INFO: Open of 'PG:...' using driver 'PostgreSQL' successful.

    Layer name: sql_statement
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
st_dump: String (0.0)
OGRFeature(sql_statement):0
  st_dump (String) = ({},0101000020E6100000A1A17F828BCD4840E88711C2A3A94240)
Related Question