What is the proper type for columns holding GeoJSON?

Question

What data type do you prefer for columns holding GeoJSON information, in the major RDBMS families (i.e. Oracle DB, PostgreSQL, MySQL, SQL Server, SQLite, DB2..?

In general, do you prefer / is better using large object columns (e.g. CLOB of Oracle DB) or do you prefer / is better using data types from the character / text family (e.g. text in PostgreSQL or character varying (with unspecified length) in PostgreSQL too)?

Can you be so clever, experienced or knowledgeable even to assert whether the answer depends on the max GeoJSON character length in the table, or not? or maybe the median value or something else?

Spoiler for X/Y problem fans follows!

For the X/Y problem fans, my X question to this Y question would be: Which is the proper way of migrating data held in Oracle spatial in production to PostGIS?, but I guess the answer to my X question is kind of a time evolving one, while I guess the answer to my Y question is more of a constant (even almost axiomatic) one.

I would have created the geojson tag if I already could, but I can not yet so I tag as JSON.

Answer 1

What is the proper type for columns holding GeoJSON?

In PostgreSQL, as JSONB (ideally), or JSON.

Which is the proper way of migrating data held in Oracle spatial in production to PostGIS?

As "Well-Known Binary", (WKB) using Get_WKB. Or simply using oracle_fdw which solves most of these problems and uses WKB behind the scenes.

The only supported geometry types are POINT, LINE, POLYGON, MULTIPOINT, MULTILINE and MULTIPOLYGON in two and three dimensions. Empty PostGIS geometries are not supported because they have no equivalent in Oracle Spatial.

Moreover on the subject of GeoJSON, PostGIS provides ST_GeomFromGeoJSON, and ST_AsGeoJSON if you want to use GeoJSON.