Is it possible to run a PostGIS query on a SpatiaLite database?

Question

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.

Answer 1

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');

3. 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)
   

Answer 2

Can I make PostGIS queries on SpatiaLite files? Without importing my SpatiaLite file to PostGIS?

Well, you essentially answered the question yourself by referencing that GitHub thread: "Foreign Data Wrappers (FDW)" are the way to run PostGIS or standard SQL queries against data not imported into a PostgreSQL database:

https://github.com/pramsey/pgsql-ogr-fdw

With a foreign data wrapper, your SQLite data essentially becomes a kind of virtual table in PostgreSQL, that simply participates alongside the rest of your schema.

That said, I personally do not have any experience with FDWs, but it is a mechanism well established in PostgreSQL for quite some time AFAIK .

And of course, it does also mean you need to have a PostgreSQL database running, even if you don't import any other data in it, as PostgreSQL will ultimately be processing your data.