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..
Best Answer
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
andcensus.non_spatial
). So for example:Finally, select information from both tables, using a JOIN:
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: