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


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


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:

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:

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;
  • I am very thankful for your suggestions... can you able to tell me is it possible to JOIN two tables are in different database. – Kishor Jul 30 '12 at 7:58

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.