Figuring out a way to import GDB format (Geo database) to Omni Sci

Hi Team,

Just wanted to ask if I’ve a GDB format file on my local desktop and intend to directly import the file to Omni Sci, is there a way to directly import it?

Would be great to get answers on this!

Hi @Shyam,

You can import these files by uploading them to the server and running the copy command from the heavysql CLI (or omnisql, depending on the version of the platform you are using) or Immerse’s SQL Editor.
it’s needed to specify that the source type file is a geo one, and the file has to be placed in a path the server si allowed to read from (as default, the /var/lib/omnisci/data/mapd_import/ or /var/lib/omnisci/data/import)

So after you move the file/files to the server, connect and run this command.

omnisql> copy ef_countries_2020_01m  from '/var/lib/omnisci/data/mapd_import/ref-countries-2020-01m.gdb.tar.gz' with(source_type='geo_file');
Result
Creating table 'ef_countries_2020_01m' and importing geo...
heavysql> \t
heavyai_us_states
heavyai_us_counties
heavyai_countries
ef_countries_2020_01m_CNTR_AT_2020
ef_countries_2020_01m_CNTR_LB_2020_3035
ef_countries_2020_01m_CNTR_LB_2020_3857
ef_countries_2020_01m_CNTR_LB_2020_4326
ef_countries_2020_01m_CNTR_MULT_LING_AT_2020
ef_countries_2020_01m_CNTR_RG_01M_2020_3035
ef_countries_2020_01m_CNTR_RG_01M_2020_3857
ef_countries_2020_01m_CNTR_RG_01M_2020_4326

The copy command will import each layer into a different table named using the table name you specify in the copy command as a prefix.

omnisql> select count(*) from ef_countries_2020_01m_CNTR_LB_2020_3035;
EXPR$0
338
heavysql> slect * from ef_countries_2020_01m_CNTR_LB_2020_3035 limit 10;
SQL Error: Non-query expression encountered in illegal context
heavysql> select * from ef_countries_2020_01m_CNTR_LB_2020_3035 limit 10;
CNTR_ID|CNTR_NAME|NAME_ENGL|NAME_FREN|ISO3_CODE|SVRG_UN|CAPT|STAT_CODE|EU_STAT|NAME_GERM|geom
TK|Tokelau-Tokelau|Tokelau|Tokélaou|TKL|NZ Non-Self-Governing Territory|Atafu|OA|F|Tokelau|POINT (-171.853912450305 -9.19296097904116)
MA|المغرب|Morocco|Maroc|MAR|UN Member State|Rabat|OA|F|Marokko|POINT (-6.29808023865245 31.8380631328738)
MC|Monaco|Monaco|Monaco|MCO|UN Member State|Monaco|OA|F|Monaco|POINT (7.41876718002314 43.7341590941577)
MD|Moldova|Moldova|Moldavie|MDA|UN Member State|Chisinau|OA|F|Moldau|POINT (28.4592601696305 47.2109022071636)
ME|Црна Гора|Montenegro|Monténégro|MNE|UN Member State|Podgorica|OA|F|Montenegro|POINT (19.2689660188132 42.772139777789)
MG|Madagascar-Madagasikara|Madagascar|Madagascar|MDG|UN Member State|Antananarivo|OA|F|Madagaskar|POINT (46.6954830971991 -19.4080033523068)
MK|Северна Македонија|North Macedonia|Macédoine du Nord|MKD|UN Member State|Skopje|OA|F|Nordmazedonien|POINT (21.6966094270798 41.5981219716361)
ML|Mali|Mali|Mali|MLI|UN Member State|Bamako|OA|F|Mali|POINT (-3.50430037989481 17.3709099448151)
MM|Myanma|Myanmar/Burma|Myanmar/Birmanie|MMR|UN Member State|Yangon|OA|F|Myanmar/Birma|POINT (96.5009771550544 21.2369395286017)
MN|Монгол Улс|Mongolia|Mongolie|MNG|UN Member State|Ulaanbaatar|OA|F|Mongolei|POINT (103.048865600977 46.8315419633088)

You cannot happen data to an existing table, so if you have data split in multiples GDB archives, you have to load on a different table name and then add to your table with an IAS operation like that.

insert into ef_countries_allyears_CNTR_RG_01M_2020_4326 select * from ef_countries_2020_01m_CNTR_RG_01M_2020_4326;

I hope this helps.

Regards,
Candido