schemaless database storage (postgis) with fme hans gunnar steen, gis engineer – fme certified...
TRANSCRIPT
Schemaless database storage (PostGIS) with FMEHans Gunnar Steen, GIS engineer – FME Certified Professional – Norkart as
Schemaless database storage (PostGIS) with FME
Schemas in FME Used on “all” readers/writers regardless of format Defines the structure of the dataset such as:
Feature Types Attributes Data types
More or less strict – dependent on needs Very important when using databases. So, why do we try to avoid it in some cases?
Schemaless database storage (PostGIS) with FME
Background: DOK( Det Offentlige Kartgrunnlaget) Tematic datasets – required by law Critical in zoning and building planning Supports decision making in accordance with
norwegian Planning and Building Act Contains data such as flooding, landslides,
avalances, proteced areas and buildings, ground conditions, minerals…
92 datasets - and growing.
Schemaless database storage (PostGIS) with FME
Quality, structure and distribution Variable quality. Most datasets still not approved. Different structure and schemas. Different ways of distribution(download, service,
phone calls…..) Many different formats – and coordinate systems.
A hazzle to deal with Want to collect, store, analyze and distribute from
a consistent PostGIS database.
Schemaless database storage (PostGIS) with FME
(Relational)databases requires strich schemas. Example flooding:
Example habitats:
Schemaless database storage (PostGIS) with FME
First try……
Schemaless database storage (PostGIS) with FME
Or….
Schemaless database storage (PostGIS) with FME
Problem: A lot of tables – or a lot of attributes Need common storeage of different schemas. MongoDB and some other DB’s allows this, but:
Lack SQL query capabilities, as data is stored in json (javascript object notation) Very limited spatial data capabilities.
PostGIS has excellent spatial data capabilities But need clearly defined schemas.
We need the best of both worlds, so…..
Schemaless database storage (PostGIS) with FME
Use Postgis for spatial storeage and json(b) for attribute storage!
1 table – 1 attribute(!) – not 1000+++… Example:Table vs json:
{"GJENTAKINT": "10", "KVALITET.MÅLEMETODE": "61", "KVALITET.NØYAKTIGHET": "36"}{"GJENTAKINT": "10", "KVALITET.MÅLEMETODE": "61", "KVALITET.NØYAKTIGHET": "36"}{"GJENTAKINT": "10", "KVALITET.MÅLEMETODE": "61", "KVALITET.NØYAKTIGHET": "36"} {"OPPDATERINGSDATO": "201309112206", "BMVERDI": "B", "BM_TAKSON_TAKSONVITNAVN": "Alces alces"}
Schemaless database storage (PostGIS) with FME
FME implementation Great json – handling in FME! Use AttributeJSONPacker to compress to json Use AttributeJSONUnpacker to restore from json Extract single elements using JSONExtractor Write to POSTGIS as regular text (improvements?) Filter on reader side by using regular sql-syntax
select datasetid from dok.datastore where(attribs->>'GJENTAKINT'= '20')
Schemaless database storage (PostGIS) with FME
Wrap-up and benefits: No attribute renaming and schema mapping No null or empty value storage Supports sql queries and flexible GIN – indexing Json is well known among programmers Excellent json – handling in FME Simpler SQL syntax No errors on import due to data type mismatch. Very flexible spatial queries.
