Importing Geospatial Data

Importing Well-known Text

You can import spatial representations in Well-known Text (WKT) format. WKT is a text markup language for representing vector geometry objects on a map, spatial reference systems of spatial objects and transformations between spatial reference systems.

Note	When representing longitude and latitude in OmniSci geospatial primitives, the first coordinate is assumed to be longitude by default.

WKT Data Supported in Geospatial Columns

You can use omnisql to define tables with columns that store WKT geospatial objects.

omnisql> \d geo
CREATE TABLE geo (
p POINT,
l LINESTRING,
poly POLYGON)

Insert

You can use omnisql to insert data as WKT string values.

omnisql> INSERT INTO geo values('POINT(20 20)', 'LINESTRING(40 0, 40 40)', 
'POLYGON(( 0 0, 40 0, 40 40, 0 40, 0 0 ))');

Importing Delimited Files

You can insert data from CSV/TSV files containing WKT strings. OmniSci supports Latin-1 ASCII format and UTF-8. If you want to load data with another encoding (for example, UTF-16), convert the data to UTF-8 before loading it to OmniSci.

> cat geo.csv
"p", "l", "poly"
"POINT(1 1)", "LINESTRING( 2 0,  2  2)", "POLYGON(( 1 0,  0 1, 1 1 ))"
"POINT(2 2)", "LINESTRING( 4 0,  4  4)", "POLYGON(( 2 0,  0 2, 2 2 ))"
"POINT(3 3)", "LINESTRING( 6 0,  6  6)", "POLYGON(( 3 0,  0 3, 3 3 ))"
"POINT(4 4)", "LINESTRING( 8 0,  8  8)", "POLYGON(( 4 0,  0 4, 4 4 ))"
omnisql> COPY geo FROM 'geo.csv';
Result
Loaded: 4 recs, Rejected: 0 recs in 0.356000 secs

You can use your own custom delimiter in your data files.

> cat geo1.csv
"p", "l", "poly"
POINT(5 5); LINESTRING(10 0, 10 10); POLYGON(( 5 0, 0 5, 5 5 ))
omnisql> COPY geo FROM 'geo1.csv' WITH (delimiter=';', quoted=’false’);
Result
Loaded: 1 recs, Rejected: 0 recs in 0.148000 secs

Importing Legacy CSV/TSV Files

Storing Geo Data

You can import CSV and TSV files for tables that store longitude and latitude as either:

• Separate consecutive scalar columns
• A POINT field.

If the data is stored as a POINT, you can use spatial functions like ST_Distance and ST_Contains. When location data are stored as a POINT column, they are displayed as such when querying the table:

select * from destination_points;
name|pt
Just Fishing Around|POINT (-85.499999999727588 44.6929999755849)
Moonlight Cove Waterfront|POINT (-85.5046011346879 44.6758447935227)

OmniSci accepts data with any SRID, or with no SRID. OmniSci supports SRID 4326 (WGS 84), and allows projections from SRID 4326 to SRID 900913 (Google Web Mercator). Geometries declared with SRID 4326 are compressed by default, and can be rendered and used to calculate geodesic distance. Geometries declared with any other SRID, or no SRID, are treated as planar geometries, the SRIDs are ignored.

Note	If two geometries are used in one operation (for example, in ST_Distance), the SRID values need to match.

Importing the Data

If you are using omnisql, create the table in OmniSci with the POINT field defined as below:

CREATE TABLE new_geo (p GEOMETRY(POINT,4326))

Then, import the file using COPY FROM in omnisql. By default, the two columns as consumed as longitude x and then latitude y. If the order of the coordinates in the CSV file is reversed, load the data using the WITH option lonlat='false':

omnisql> COPY new_geo FROM 'legacy_geo.csv' WITH (lonlat='false');

Columns can exist on either side of the point field; the lon/lat in the source file does not have to be at the beginning or end of the target table. Fields can exist on either side of the lon/lat pair.

Importing CSV, TSV, and TXT Files in Immerse

In Immerse, you define the table when loading the data instead of predefining it before import. Immerse does not currently support appending data to a table by loading more than one file to a table.

Longitude and latitude can be imported as separate columns.

Importing Geospatial Shape Files

You can create Geo tables by importing specific Geo file formats. OmniSci supports the following types:

• ESRI Shapefile (.shp and associated files)
• GeoJSON (.geojson or .json)
• KML (.kml or .kmz)

The geo file import process automatically creates the table by detecting the column names and types explicitly described in the geo file header. It then creates a single geo column (always called omnisci_geo) which is of one of the supported types (POINT, LINESTRING, POLYGON or MULTIPOLYGON).

Note	Due to the prevalence of mixed POLYGON/MULTIPOLYGON geo files (and CSVs), if OmniSci detects a POLYGON type geo file, OmniSci creates a MULTIPOLYGON column and imports the data as single polygons.

You import geo files using the COPY FROM commmand using the ‘geo’ option.

omnisql> COPY states FROM 'states.shp' WITH (geo=’true’);
omnisql> COPY zipcodes FROM 'zipcodes.geojson' WITH (geo=’true’);
omnisql> COPY cell_towers FROM 'cell_towers.kml' WITH (geo=’true’);

If the table does not already exist, it is created automatically.

If the table already exists, and the data in the geo file has exactly the same column structure, the new file is appended to the existing table. This enables the import of large geo data sets that are split across multiple files. The new file is rejected if it does not have the same column structure.

Note

When appending geo files containing POLYGON or MULTIPOLYGON data, the spatial distribution of the polygons in the existing rows must be evaluated before the new data is imported. This might take some time with large tables and can cause a geometrical slowdown if a large number of files are appended. To mitigate this slowdown, combine data into the minimum possible number of files.

By default, geo data is stored as GEOMETRY.

You can also create tables with coordinates in SRID 3857 or SRID 900913 (Google Web Mercator). Importing data from shapefiles using SRID 3857 or 900913 is supported; importing data from delimited files into tables with these SRIDs is not supported at this time. To explicitly store in other formats, use the following WITH options in addition to geo='true'.

Option	Description
geo_coords_encoding	Compression used: COMPRESSED(32) - 50% compression (default) None - No compression
geo_coords_srid	Spatial reference identifier (SRID) type: 4326 - EPSG:4326 (default) 900913 - Google Web Mercator 3857 - EPSG:3857

For example, the following explcitly sets the default values for encoding and SRID:

geo_coords_encoding='COMPRESSED(32)'
geo_coords_srid=4326

Note

Rendering of geo LINESTRING, POLYGON and MULTIPOLYGON is possible only with data stored in the default lon/lat WGS84 (SRID 4326) format, although the type and encoding are flexible. Unless compression is explictly disabled (NONE), all SRID 4326 geometries are compressed. For more information, see WGS84 Coordinate Compression.

Importing Geo Files from Archives or Non-Local Storage

You can import geo files directly from archive files (for example, .zip .tar .tgz .tar.gz) without unpacking the archive. You can directly import individual geo files compressed with Zip or GZip (GeoJSON and KML only). The server opens the archive header and loads the first candidate file it finds (.shp .geojson .json .kml), along with any associated files (in the case of an ESRI Shapefile, the associated files must be siblings of the first).

$ unzip -l states.zip
Archive:  states.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
        0  2018-02-13 11:09   states/
   446116  2017-11-06 12:15   states/cb_2014_us_state_20m.shp
     8434  2017-11-06 12:15   states/cb_2014_us_state_20m.dbf
        9  2017-11-06 12:15   states/cb_2014_us_state_20m.cpg
      165  2017-11-06 12:15   states/cb_2014_us_state_20m.prj
      516  2017-11-06 12:15   states/cb_2014_us_state_20m.shx
---------                     -------
   491525                     6 files

omnisql> COPY states FROM 'states.zip' with (geo=’true’);
omnisql> COPY zipcodes FROM 'zipcodes.geojson.gz' with (geo=’true’);
omnisql> COPY zipcodes FROM 'zipcodes.geojson.zip' with (geo=’true’);
omnisql> COPY cell_towers FROM 'cell_towers.kml.gz' with (geo=’true’);

You can import geo files or archives directly from an Amazon S3 bucket.

omnisql> COPY states FROM 's3://mybucket/myfolder/states.shp' with (geo=’true’);
omnisql> COPY states FROM 's3://mybucket/myfolder/states.zip' with (geo=’true’);
omnisql> COPY zipcodes FROM 's3://mybucket/myfolder/zipcodes.geojson.gz' with (geo=’true’);
omnisql> COPY zipcodes FROM 's3://mybucket/myfolder/zipcodes.geojson.zip' with (geo=’true’);

You can provide Amazon S3 credentials, if required, by setting variables in the environment of the omnisql process…

AWS_REGION=us-west-1
AWS_ACCESS_KEY_ID=********************
AWS_SECRET_ACCESS_KEY=****************************************

You can also provide your credentials explicitly in the COPY FROM command.

omnisql> COPY states FROM 's3://mybucket/myfolder/states.zip' WITH (geo=’true’, s3_region=’us-west-1’, s3_access_key=’********************’, s3_secret_key=’****************************************’);  

You can import geo files or archives directly from an HTTP/HTTPS website.

omnisql> COPY states FROM 'http://www.mysite.com/myfolder/states.zip' with (geo=’true’);

CREATE TABLE geo2 (
p2 GEOMETRY(POINT, 4326) ENCODING NONE,
l2 GEOMETRY(LINESTRING, 900913),
poly2 GEOMETRY(POLYGON, 4326),
mpoly2 GEOMETRY(MULTIPOLYGON, 4326) ENCODING COMPRESSED(32));