Database Features
Tips and Tricks on using Exasol - from SQL and Lua Scripts to transactions and performance
cancel
Showing results for
Did you mean:

## How to import the whole world into Exasol - Geospatial Data, Geo Indexes, Geo Joins and GeoJSON

Background Geospatial data   can be stored and analyzed in the Exasol database using the GEOMETRY datatype. In this solution, we will show you some examples of how to import geo spatial data from a CSV and from a GeoJSON file and use SQL functions to perform analytics and geo joins. Spacial Reference Systems In the system table SYS.EXA_SPATIAL_REF_SYS, you will find more than 3000 different spatial reference systems which can be used for the GEOMETRY datatype. These reference systems are there to define points on earth, but they have different strengths, accuracies, and properties, for example, the SRID 31466 can only reference locations within Germany. We will use the two SRIDs 4326 and 2163 in our example. 4326 is using degrees as a unit, 2163 uses meters. Often, conversions are necessary between different coordinate systems. The ST_TRANSFORM function can be used for this. Prerequisites Working with Spatial Data in Exasol The following example shows how to create a table with a geometry column, how to insert and query data. We are using GEOMETRY(4326) because the provided coordinates are in degrees. Later, in the SELECT query, we transform the geodata into SRID 2163 to see the distance between the two cities in meters: CREATE TABLE cities( name VARCHAR (200), geo GEOMETRY(4326)); INSERT INTO cities VALUES ( 'Berlin' , 'POINT (13.36963 52.52493)' ); INSERT INTO cities VALUES ( 'London' , 'POINT (-0.1233 51.5309)' ); -- this shows the distance in degrees: SELECT a . name , b. name , st_distance( a .geo, b.geo) FROM cities a , cities b; -- this shows the distance in meters: SELECT a . name , b. name , st_distance(ST_Transform( a .geo, 2163), ST_Transform(b.geo, 2163)) FROM cities a , cities b; GEOMETRY columns can be filled with strings using the well-known text representation (WKT), e.g. 'POINT (13.36963 52.52493)' How to process geodata Step 1: Importing geodata Importing geodata from CSV files Often, geodata is present in CSV files or in colunms of tables that are imported from different database systems in form of latitude and longitude values. On   https://openflights.org/data.html#airport, you can download a CSV file containing international airport data. We are using the extended version of this file, which consists of more than 12,000 international airports and train stations. In the first two fields of the file, there is the id and the name of the airport; in columns 7 and 8, we find its latitude and longitude coordinates: CREATE OR REPLACE TABLE airports( airport_id INT , name VARCHAR (500), latitude DECIMAL (9,6), longitude DECIMAL (9,6) ); IMPORT INTO airports FROM LOCAL CSV FILE 'D:\airports-extended.dat' (1, 2, 7, 8); ALTER TABLE airports ADD COLUMN geo GEOMETRY(4326); UPDATE airports SET geo = 'POINT (' ||longitude|| ' ' ||latitude|| ')' ; SELECT * FROM airports; We firstly used DECIMAL(9,6) columns to store the latitude and longitude values, and then we added a GEOMETRY column to store a geodata point for each airport. Similar to the example above, we can now calculate the distance between two airports or train stations in our table using the ST_DISTANCE and the ST_TRANSFORM function: select st_distance(ST_Transform( a .geo, 2163), ST_Transform(b.geo, 2163)) from airports a , airports b where a . name = 'Berlin-Tegel Airport' and b. name = 'Berlin Hauptbahnhof' ; Importing data from GeoJSON GeoJSON   is an often-used format for storing and exchanging geodata. On   https://geojson-maps.ash.ms, you can download countries as geodata. When you generate a custom JSON file on this site, the file consists of one JSON object, a so-called FeatureCollection, which contains multiple polygons and multi-polygons, one for each country. We import the data in three steps: Loading the full GeoJSON string into a VARCHAR(2000000) column in a table. (mind that this does not work if the GeoJSON is larger than two million characters) Parsing the GeoJSON using the json_table UDF script (see querying-and-converting-json-data-with-the-json-table-udf; this UDF script emits a table with one row for each geo-object) Converting each GeoJSON object into a GEOMETRY value using the ST_geomFromGeoJSON UDF script (attached to this solution) After these steps, we have all countries from the GeoJSON file in a GEOMETRY column. An alternative approach is developing a UDF script that loads the GeoJSON from a server (e.g., by using the Python package requests or paramiko) and parsing and iterating over the GeoJSON objects within the script. For each object, a WKT string is emitted, which can later be converted into a GEOMETRY value. This way, there are no limit on the characters. For our approach, the whole GeoJSON must not be larger than 2,000,000 characters: create or replace table geo_import(v varchar (2000000)); import into geo_import from local csv file 'D:\custom.geo.json' column separator = '0x01' column delimiter = '0x02' ; -- dummy separaters / delimiters to import a whole line as one column value -- json_table (can be found in https://community.exasol.com/t5/database-features/querying-and-converting-json-data-with-the-json-table-udf/ta-p/1800) emits a row for each country with two columns name and geojson create or replace view geojson as select json_table(v, '\$.features[*].properties. name ' , '\$.features[*].geometry' ) emits ( name varchar (2000000), geojson varchar (2000000)) from geo_import; -- ST_GeomFromGeoJSON is attached to https://community.exasol.com/t5/database-features/how-to-import-the-whole-world-into-exasol-geospatial-data-geo/ta-p/1669 create or replace table countries as select name , cast (ST_GeomFromGeoJSON(geojson) as geometry(4326)) as geo from geojson; select * from countries; Step 2: Geo-Joins and Geo-Indexes The following query performs a geo-join between the two tables, countries and airports. It finds all airports that are within the country Italy: select a .* from countries c join airports a on st_contains( c .geo, a .geo) where c . name = 'Italy' ; You can use   profiling   to see that an geo-index is created automatically and that this geo-index is used to execute the geo-join: alter session set profile = ' ON ' ; alter session set query_cache = ' OFF ' ; select a .* from countries c join airports a on st_contains( c .geo, a .geo) where c . name = 'Italy' ; flush statistics ; select * from exa_statistics.exa_user_profile_last_day where session_id = current_session and command_name = ' SELECT ' preferring high stmt_id; Like other indexes in Exasol, the geo-index is persisted so that it can be used for future queries, it is maintained automatically when the table data changes, and it is automatically dropped when it is not used for five weeks or longer. Mind that geo-indexes are a new feature since Exasol 6.1. Step 3: Visualizing Geometry Data The SQL client DBeaver is able to show geospatial data on a map. On a result tab, right click on a GEOMETRY column, choose View / Format, View as Geometry. Then click on the Panels button on the right to show the Value panel with the map.

## Analyzing disk I/O and concurrency based on *_last_day data

How to analyze disk I/O and concurrency using the *_last_day table data

## LDAP error: Can't contact LDAP server, use FORCE option to create user

This article describes what you can do if you cannot contact the LDAP server during user creation

## What happens when I JOIN DECIMAL datatypes of various sizes?

This article shows what happens behind the scenes when JOINing on a DECIMAL datatype.

## Is the IDENTITY Column guaranteed to be unique?

Question Is the IDENTITY Column guaranteed to be unique? Answer This ticket deals with IDENTITY columns and if they are guaranteed unique. Even though your use case scenario may not apply here, the uniqueness is NOT guaranteed as you can UPDATE the IDENTITY column. The way around this is to make the IDENTITY column a primary key. Probably not doable for most production database repositories. We discussed the content in   https://docs.exasol.com/sql_references/data_types/identitycolumns.htm specifically, the part, "   Identity columns cannot be considered as a constraint, that is, identity columns do not guarantee unique values. However, the values are unique as long as they are   inserted implicitly   and are not manually changed." This means a series of concurrent inserts where the IDENTITY is not included in the values inserted, then the FIFO rule applies, where the first insert does a commit and generates the IDENTITY, then the next insert is free to insert. The emphasis is on the   insert implicitly   - where the IDENTITY value is not specified, but system generated. If you do an insert with the IDENTITY value coded in the insert statement if will insert and become a duplicate. Remember, the IDENTITY column can't be considered a constraint. There are a couple of suggestions to get closer to guaranteed uniqueness: 1. LOCK the table you are inserting into - which means no one can insert until you either commit or rollback. This can be achieved with: DELETE FROM <tableName> where FALSE; 2. Do the insert 3. Release lock with commit or rollback. Another suggestion is to create a function to insert the max(ROW_NUMBER()) + 1 into the IDENTITY column. It's at the bottom of this simple example SQL. drop schema exa_29444 CASCADE; commit; create schema exa_29444; open schema exa_29444; – -- Build a test table with an IDENTITY column. – create or replace table identity_test (id int identity, name varchar(20)); – -- optional TRUE constraint for IDENTITY COLUMN – --alter table identity_test add constraint it_pk PRIMARY KEY ("ID") enable; – -- Load the table with data using   IMPLICIT insert   on IDENTITY Column "ID" – insert into identity_test (name) values ('Zach'),('Cole'),('Daniel'); commit; – -- Present the results – SELECT * FROM IDENTITY_TEST; – -- Template to generate the next sequential ID - using ROW_NUMBER analytic function. – If you wish to Generate the IDENTITY COLUMN value for the insert (Explicit insert) – WITH ROWZ (RowNumber) AS (select ROW_NUMBER() over (ORDER BY ID ASC) RowNumber FROM IDENTITY_TEST) SELECT MAX(RowNumber) +1 from ROWZ; commit; Additional References  https://docs.exasol.com/sql_references/data_types/identitycolumns.htm https://docs.exasol.com/sql/alter_table(column).htm?Highlight=identity https://docs.exasol.com/sql_references/metadata/metadata_system_tables.htm?Highlight=identity

## Bing maps and QuadTree calculation

This article shows you how you can create functions to work with the Bing Maps Tile System

## Transaction Conflicts for Mixed Read/Write Transactions

This article describes how transaction conflicts occur with complex read/write scenarios, and how you can solve them

## Send an EMAIL from Lua script

This article shows you how to send an email from a Lua Script