6

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.

2 Answers 2

9

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)
    
5

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.

1
  • good info thanks.
    – nr_aus
    Aug 11, 2021 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.