3

I have data in two locations:

  • geometry data (with attributes) on a PostGIS server (read only)
  • a geometryless table on a local SpatiaLite DB (read+write)

I need to select features based on data from the geometryless table. My current solution is to clone the complete geometry data from PostGIS to the SpatiaLite DB (using QGIS) and executing the (simplified) query below but that's not exactly optimal.

SELECT t.ID, t.Attribute1, t.Attribute2, s.Geometry
FROM TabularData t, ShapeData s
WHERE s.Attribute1 = t.Attribute1 AND s.Attribute2 = t.Attribute2

Any idea how to do this across the databases?

  • 1
    I can't provide a specific answer, but the thing you are looking for is called "Foreign Data Wrapper".I've only read about it and not used it so far. – nickves Mar 24 '15 at 9:43
3

You can use Foreign Data Wrappers, as of Postgres 9.2 (I think), from within Postgres to register a connection to another data source, either a Postgres server on another machine, or a completely separate data source.

CREATE EXTENSION postgres_fdw;

CREATE SERVER foreign_spatial_lite 
   FOREIGN DATA WRAPPER postgres_fdw 
   OPTIONS (dbname 'some_dbname', host 'śome_host);

CREATE FOREIGN TABLE tabular_data
(
  id integer,
  attribute1 text,
  attribute2 text
)
SERVER foreign_spatial_lite OPTIONS (table_name 'tabular_data');

You can now query the table tabular_data locally. The docs have more information. There is a decent walk-through in this presentation.

  • I don't think I can do that on the postGIS server (read only..) but it looks like there are solutions for SpatiaLite as well (github.com/gleu/sqlite_fdw, or multicorn). – MapEngine Mar 24 '15 at 12:11
  • Good, same principle, then. – John Powell Mar 24 '15 at 12:20
0

You can only connected to one (and only one) database in the DB Manager to run the SQL window. So this is not possibly. Either your way of cloning the data, or making the join inside QGIS in the Properties > Joins tab of one of the layers.

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.