How to relate Non-Spatial table to Spatial table in two different Postgis DBs?

Question

I need to relate a Non-spatial table to Spatial table, but both are in different database.

Spatial_table(dbname:dist)
 gid
 district_name
 district_code
 geom

Non_Spatial(dbname:census)
 ID
 district_code
 population
 male_popu
 female_popu

Can anyone please suggest me, How to relate the above to tables to get the query result for the population of specific district?

Also can anyone tell me about the difference between Joining and Relating of two tables.

Thanks in advance..

Answer 1

Fist about joins and relates. The former term is to "join" one table to another using some criteria, such as data common to the two tables. The later term is more a GIS term for how one spatial data relates to another, e.g. see ST_Relate and DE-9IM. One might join a table to another using a relate. So for your situation, "how to join non-spatial table to spatial table" is more correct. Relate implies some other spatial operator between two geometry columns/tables, such as ST_Contains, which you are not doing here.

About joining across PostgreSQL databases. Although you can do it with dblink, you will have terrible performance, since indexes are not used. If your two databases are in any sort of way related, they should actually be in the same database, but organized in different schemas (if needed).

Now, you should have two tables set up. This example is when they are in the same database and schema (if they are in different schemas, just add the schema name before the table, e.g. dist.spatial_table and census.non_spatial). So for example:

CREATE TABLE spatial_table(
  gid serial PRIMARY KEY,
  district_name text,
  district_code integer NOT NULL,
  CONSTRAINT district_code_unique UNIQUE (district_code)
);
SELECT AddGeometryColumn('spatial_table', 'geom', 4326, 'POLYGON', 2);
CREATE INDEX spatial_table_idx ON spatial_table USING gist (geom);

CREATE TABLE non_spatial(
  id serial PRIMARY KEY,
  district_code integer NOT NULL,
  population numeric,
  male_popu numeric,
  female_popu numeric,
  CONSTRAINT non_spatial_district_code_fkey FOREIGN KEY (district_code)
      REFERENCES spatial_table(district_code)
);
CREATE INDEX fki_non_spatial_district_code_fkey ON non_spatial USING btree (district_code);

Finally, select information from both tables, using a JOIN:

SELECT *
FROM spatial_table
JOIN non_spatial ON spatial_table.district_code=non_spatial.district_code
WHERE spatial_table.district_code=123;

---

To join across databases, first install dblink on the database that you need to query from, then use a similar query, modified to subselect from the dblink connection:

SELECT *
FROM spatial_table
JOIN (select * from dblink('dbname=census password=secret', 'select * from non_spatial')
    AS non_spatial(id integer, district_code integer, population numeric,
                   male_popu numeric, female_popu numeric)) AS non_spatial
 ON spatial_table.district_code=non_spatial.district_code
WHERE spatial_table.district_code=123;