Use of PostgreSQLPostGIS as DBMS of the French Topographic Database

1
Use of PostgreSQL/PostGIS as DBMS of the French
Topographic Database
2
Plan of the presentation

• Context why PostgreSQL/PostGIS?
• Client / Server functionalities
• Results / Conclusions
• Other applications of PostgreSQL/PostGIS in IGN
  France

3
Contextwhy PostgreSQL/PostGIS ?

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

4
Context databases before 2002

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• BDTopo
• Aims high precision 3D reference geographic
  frame
• Themes Road / rail / water network, buildings,
  vegetation, administrative boundaries
• Sources stereo plotting on aerial photos
• Constitution from 1990 to 2006 2007
• Typical scale gt 1/10 000
• Accuracy ? 2 m

5
Context databases before 2002

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Géoroute
• Aims itinerary computation, on-board navigation,
  geocoding
• Themes Road network, road restrictions,
  particular equipment (parks, hospitals, police
  ),
• Covers urban areas, with low scale links between
  these regions
• Heterogeneous sources
• BDTopo
• Cadastre
• BDCarto
• Constitution in 1995 2000.
• Typical scale gt 1/20 000
• Accuracy ? 10 m

Urban areas
6
Context databases before 2002

• BDCarto
• Aims medium scale reference frame
• Themes Road network, water network, land cover,
  administrative boundaries
• Sources digitalisation of maps (scale 1/50 000)
• Constitution in 1986 - 1993
• Typical scale 1/100 000
• Accuracy ? 30 m

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

7
Context technical elements

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

8
Next step continuous updating

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• New objectives in IGN continuous updating for BD
  TOPO, GEOROUTE and BD CARTO
• Principle
• Collect update information once. Main sources
• Partners (network managers, local governments,
  firemen, ...)
• Field survey
• Disseminate it among the products
• Decentralised updating
• 7 production units (5 outside of Paris)
• 120 update collectors

9
2002 a new project

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Objectives
• Unification
• Make update easier
• Take the advantages of the 2 products
• Implementation of a new system to store, manage
  and update the IGN vector databases
• Seamless databases on whole country
• Decentralised updating
• Enabling full disconnected work in the field

10
2002 a new project

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

11
First approach

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• First state of the art
• Several technologies (Oracle, DB2, ArcGIS,
  PostgreSQL)
• Various costs (from free to very expensive)
• Current practice
• Wide use of GIS Geoconcept by the IGN staff
  (production, updating, development)
• Another IGN team uses PostgreSQL
• Requirement definition
• Difficult step because of the few experience of
  the project team and the production unit
• ? We were not mature enough to prepare the call
  for tenders

12
2004 Prototyping

• Objectives
• Increase the technical skills of the team
• Define more detailed requirements
• Find a simple solution
• Starting point
• Client GIS Géoconcept
• Server PostgreSQL/PostGIS (free, easy to learn
  and technical skills in IGN)
• Client Server communication To be developed
  (using the ODBC driver)

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

13
2004 Prototyping

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

Géoconcept
Géoconcept
Géoconcept
Géoconcept

• Development of data synchroniser
• Tests of PostgreSQL (transaction)
• Better understanding of the requirements

14
2005 - 2006 industrialisation and production

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Prototype gave good results (and low cost)
• ? Prototype Industrialisation
• Processing of difficult cases
• Test of the system (massive database,
  simultaneous transactions)
• Production test by update collectors validation
  of the system
• Production
• Spring 2006 Loading Paris and its surroundings
• 03/2008 whole France is loaded

15
Client server functionalities

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

16
GCVS principles
BDUni whole France PostgreSQL / PostGIS

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

GCVS
Collector GIS system (Géoconcept)

• GCVS Geographic Concurrent Versioning System
• Development with visual C (uses the ODBC
  driver)
• 3 mains functions available on Géoconcept
• Data Loader
• Data Extractor
• Data Synchroniser

17
GCVS Data Loader
? Takes data from GeoConcept and loads data into
PostGIS database

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

BDUni PostgreSQL / PostGIS
Data Loader

• Used once only for each area
• Transactional

18
GCVS Data Extractor
? Pulls a specific working unit of data out of
the database and into Geoconcept for editing

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

BDUni whole France PostgreSQL / PostGIS
Data Extractor
Géoconcept

• Several update collectors can extract the same
  working unit simultaneously

19
GCVS Data Synchroniser
? Pushes updates from the client to database and
pulls changes in the database down to the client

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• If two collectors update the same feature, a
  conflict is raised and one collector has to solve
  it
• Disconnected work
• Synchronisation on request

20
Data updating how does it work ?

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• T1 the collector A updates a road in Geoconcept

1
Highway
Single road
2

• T2 the collector A creates an synchronisation
  area with updating metadata

Creation of access road

• T3 (on request) synchronisation

21
Data Synchroniser elements of implementation

• GCVS must be able to retrieve quickly the objects
  which have been deleted, created and modified on
  the clients and on the server. To do this
• On the server, each object has
• An identifier
• A number of synchronisation
• On the client
• Each object has a client identifier
• An MS Access file contains
• The correspondence between client identifier and
  server identifier
• An md5 print of each object client (updated after
  each synchronisation)

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

22
Data Synchroniser elements of implementation

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Example of a modification on the client

Highway
Single road

• The data synchroniser
• Detects a modification on the client by
  comparison of md5 keys
• Calculated on the GIS object
• Stored in the Access file
• Searches if the object has not been already
  modified on the server

23
GCVS many possibilities

• Customization with xml files
• Correspondence between feature class on client
  and table on the server
• Feature class may be for updating or for reading
  only
• Various custom clients in production
• Reading only (cartographic use, quality control)
• updating of administrative units by one person on
  the whole country
• Research of not synchronised objects
• Processing of the links

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

24
Structure of the data on the server

• Table of road segments

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Historic table of road segments

This structure allows replication and computation
of the differences occurred between two dates
(differential data)
25
Home-made replication
Enables to have a copy of the production database
in a few minutes master / slave replication.

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

Database at t0
copy at t0
Dump/Restore
Updating
Replication
Database at t1
copy at t1
Evolutions between t0 and t1 are pulled into the
copy of database at t0
26
Results - Conclusions

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

27
The unified database today (january 2009)

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Production server
• 1 database for metropolitan France
• 118 Gb
• 124 tables et 124 historic tables
• 160 millions of objects
• 19 millions of vegetation objects (17 Gb)
• 16 millions of road segments (13 Gb)
• 23 millions of buildings (12 Gb)
• 32 millions of objects in the historical tables
  
• Some indexes over 1 Gb (identifier, geometric
  indexes)
• 6 databases on the overseas territories
  (Martinique, Réunion, Guyane )

28
The unified database today (january 2009)

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Production server
• Server DELL, PowerEdge 6850, 4 processors
• Storage 5 x 300 Gb 10 000 rpm SCSI disks,
  including 4 in RAID 1
• RAM 12 Gb
• Backup server
• 1 database restored each week (backup copy)
• 1 database for read-only applications quality
  control, demos,
• replication every half an hour

29
Feedback from PostgreSQL/PostGIS use

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• Can be used easily for prototyping
• Free and open-source
• Many ways to access and develop (ODBC, libpq )
• Capacity to handle more than 100 millions spatial
  objects
• Ability to answer queries quickly
• Full transactional integrity

30
Comparison with commercial solutions

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

31
Other applications PostgreSQL/PostGIS in IGN
France

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

32
Altimetry database (2006)

• 80 Gb
• Vector curves, altimetry points
• Raster DTM
• Metadata

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

BASALT
PostGreSQL/PostGIS LINUX
lt 5 clients

• Home made soft
• Visual c on ODBC

ExploZOE Windows
Consultation
Integration
Products edition
Modification
33
Géoportail (2007)

• Online access to geographic data by means of
  geographic
• interfaces for public at large

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• PostgreSQL is only used to store vector data (300
  Go) and to pre-compute vector tiles
• Soon WMS and WFS (Geoserver)

34
Geodesic database
Database PostgreSQL

• 70 Gb
• Vector (bench-mark )
• Raster (Picture)
• Several schemas

35
Diffusion extraction
Database BDTopo Whole France PostgreSQL

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

Data extractor
Delivery to the customers in various formats and
areas
36
Conclusion

• Context
• Client / Server functionalities
• Results / Conclusions
• Other applications

• PostgreSQL/PostGIS is the main DBMS in IGN
  France.
• It is used to
• Store massive and various databases
• Consult and update data
• Wide use in IGN
• Attractive solution

Thanks for your attention