3

I am designing a spatial database using PostGIS. My main aim of this spatial database is to organize Administrative (250 polygon layer) and postal layers (154 layers) in a database. These layers covers different countries of the world. Each country has difference administrative/ postal code levels. At last this model should provide a way to update these layers using QGIS.

I have studied difference techniques of organizing spatial data in postgis. Till now inheritance model (PostGIS in Action/chapter 3) impress me but I am not sure how to implement it.

Can inheritance used for geometrical inheritance as well? Like Administrative level 01 is always subset of Administrative level 02 means from the geometry of Administrative level 02 we can extract the geometry of Administrative level 01 using dissolve.

If we update any geometry in Administrative level 02 then geometry of Administrative level 01 should be automatically updated. How can I achieve this?

3

We have begun using PostgreSQL Materialized Views to create Elementary, Middle, and High school boundaries, which are all based on an 'All School Boundary' file.

So to try and relate this to your problem, the 'all bounds' file has columns for elem_num, mid_num, and high_num.

When I want to create the Elem bounds, I write a

CREATE MATERIALIZED VIEW public.SchoolBoundaries_Elem AS 

 SELECT sch.elem_num,
                sch.elem_name,
                st_unaryunion(sch.geom) AS geom
               FROM "SchoolBoundaries_All" sch
              WHERE sch.elem_num > 0::smallint
              GROUP BY sch.geom, sch.elem_num, sch.elem_name;

(documentation)

And when you want to refresh each of the materialized views, because the source data has changed, you can use:

REFRESH MATERIALIZED VIEW public.SchoolBoundaries_Elem;

(documentation)

And there are commands like ALTER MATERIALIZED VIEW, etc.

2

A solution that owes nothing to the table inheritance concept you refer: Administrative level 02 layer can be a database view where the geometry column is ST_Union of Administrative level 01 geometries:

CREATE OR REPLACE VIEW Administrative_level_02 AS
select gid, field_a, field_b, st_union(the_geom) as the_geom
FROM Administrative_level_01_TABLE
GROUP BY Admin_level_01_IDFIELD;

There are different strategies to attempt that unioned polygons are not overlapping neighbour polygons.

I think it is important to use ST_SimplifyPreserveTopology function in order that level 01 polygons don't carry excessive and useless detail from level 02 boundaries.

  • Thanks for your answer, I think you are suggesting saving Administrative and postal code finest geometry in tables and then extract all other levels (administrative or postal ) using views. But correct me if I am wrong, we can not use views to update data – iRfAn Nov 4 '15 at 16:42
  • 1
    If you are using ArcGIS, no, you can't graphically edit views. If you are using QGIS, yes you can edit views. You can apply INSTEAD OF triggers or PostgreSQL RULEs to your views and make your views editable. – rpcavaco Nov 4 '15 at 17:00
  • Editing views in QGIS? – DPSSpatial Nov 4 '15 at 21:46
  • Yes, I've done it with QGIS and Oracle Spatial geodatabase, I see no reason why shouldn't work with PostGIS – rpcavaco Nov 4 '15 at 22:38
  • If I can edit my views, then I can organize my data any way I want. Thanks for this tip. – iRfAn Nov 5 '15 at 7:47

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.