EXASOL v7 and GEOJSON. Need help :)

max
Contributor

Hi Community!

We are on EXASOL v7 and we are desperatly trying to import geojson-data in our database.
Unfortunatly the Solution described for the JSON_TABLE UDF (https://docs.exasol.com/sql_references/geospatialdata/import_geospatial_data_from_csv.htm) is not working for us.
The newest version of the script is download from github and placed in "EXA_TOOLBOX". I get the following - not very telling - error:

SELECT exa_toolbox.json_table('{ "name": "Bob", "age": 37, "address":{"street":"Example Street 5","city":"Berlin"},
"phone":[{"type":"home","number":"030555555"},{"type":"mobile","number":"017777777"}],
"email":["bob@example.com","bobberlin@example.com"]}','$.phone[*].number') EMITS (phone VARCHAR(50));

max_0-1607431701425.png

 

Since v7 supports native JSON-Functions we gave the JSON_EXTRACT a go. This generally works but we are not able to extract "arrays" (the coordinates of the
polyon in geojson) from the json? We get the following error for which i cannot find a solution 😞

import into geo_import from local CSV file 'custom.geo.json' column separator = '0x01' column delimiter = '0x02';

create or replace view geojson as
select JSON_EXTRACT(v, '$.features[*].properties.name', '$.features[*].geometry')
emits (name varchar(2000000), geojson varchar(2000000)) from geo_import;
select * from geojson limit 10;

max_1-1607431711263.png

 

I'm feeling a bit trapped..Is there somebody amoung you who has experience with this? Thankful for any help πŸ™‚

1 ACCEPTED SOLUTION

exa-Franz
Team Exasol
Team Exasol

Hi @max,

here the solution, are you looking for this?

create table tj(x varchar(2000000));
import into tj from local CSV file 'C:\Users\fs\Desktop\custom.geo.json' column separator = '0x01' column delimiter = '0x02';

select JSON_EXTRACT(x, '$.features[*]#.properties.name', '$.features[*]#.geometry.json()')
emits (name varchar(2000000), geojson varchar(2000000)) from tj;

 

exa-Franz_1-1607530639255.png


Please take a look at this:

https://docs.exasol.com/sql_references/functions/json_path_expressions.htm#CategoryJSONEXTRACTExtens... for the # path extension.

https://docs.exasol.com/sql_references/functions/json_path_expressions.htm#CategoryExasolExtensions for the json() extension.

 

View solution in original post

7 REPLIES 7

max
Contributor

Hey little koi, thanks for your help and insights πŸ™‚ The way you cast JSON to GEOMETRY is great and we will try to use it. But unfortunately we are struggling with the previous step. The attached GEOJSON-File consinst of a section for "features" basically beeing key-value-pairs and at second part with polyong-data called "geometry" which are present for each country in the file:

max_0-1607514630799.png

As shown in my original posting, the JSON_EXTRACT seems not to able to extract those GEOMETRY-Details and the JSON_TABLE function from github isn't working at all πŸ˜• Which is by the way very disappoiting because that is part of the offical documentation for exasol v7....
Do you have a hint for us how we can interpert/split the json-data? Or has anyone got this working?

Thanks πŸ™‚

exa-Franz
Team Exasol
Team Exasol

Hi Max,
your error message is part of this documentation:

https://docs.exasol.com/sql_references/functions/json_error_handling.htm

Maybe that gives you a hint how to improve the query for the json path expression?
I can reproduce it in my environment.

Best regards,
Franz

exa-Franz
Team Exasol
Team Exasol

Hi @max,

here the solution, are you looking for this?

create table tj(x varchar(2000000));
import into tj from local CSV file 'C:\Users\fs\Desktop\custom.geo.json' column separator = '0x01' column delimiter = '0x02';

select JSON_EXTRACT(x, '$.features[*]#.properties.name', '$.features[*]#.geometry.json()')
emits (name varchar(2000000), geojson varchar(2000000)) from tj;

 

exa-Franz_1-1607530639255.png


Please take a look at this:

https://docs.exasol.com/sql_references/functions/json_path_expressions.htm#CategoryJSONEXTRACTExtens... for the # path extension.

https://docs.exasol.com/sql_references/functions/json_path_expressions.htm#CategoryExasolExtensions for the json() extension.

 

View solution in original post

max
Contributor

Hi @exa-Franz ,

awesome, thanks πŸ™‚ That is excatly what i needed! Thanks for your help πŸ’―

exa-Chris
Community Manager
Community Manager

Hi Max,
let me also check with Dev and Doc Teams
Christian

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

borutrozac
Padawan

Hi max.

I tried to import you data in Exasol in R by converting it to R data.frame and it worked. Problem that I faced during import was when R with ODBC tried to automatically create table with all columns contained in .geojson file and some columns had all values set to null. Exasol driver in R tried to set that columns to Varchar(-1) which was rejected by DB. I suppose that this is the problem with script provided by Exasol that tries to import data from geojson. I haven't tried that script since we are using older version of Exasol, but things in R worked when i fixed null columns.

Here is the R code snippet, at the end I just converted everything to data.frame with WKT geometry, where null or NA in R values should be changed either to 0 or something:

library(spdplyr)
library(sp)
library(st)
library(rgdal)

rawData=rgdal::readOGR("custom.geo.json",encoding = "UTF8",use_iconv = TRUE)

class(rawData)

rawDataAsSt = rawData %>% st_as_sf()

rawDataAsSt = rawDataAsSt %>% mutate(geometry=st_as_text(geometry)) %>% as.data.frame()

 

littlekoi
Xpert

Try this UDF (adjust path to JAR):

 

CREATE OR REPLACE JAVA SCALAR SCRIPT json_to_geo (json VARCHAR(2000000))
RETURNS VARCHAR(2000000)
AS

/*
 * Convert the geometry to a GeoJSON representation
 * https://javadoc.io/doc/org.orbisgis/h2gis/latest/org/h2gis/functions/io/geojson/ST_GeomFromGeoJSON.html
 */

%jvmoption -Xms16m -Xmx64m -Xss512k;
%jar /buckets/bfsdefault/javalib/h2gis.jar;

import org.h2gis.functions.io.geojson.ST_GeomFromGeoJSON;
import org.h2gis.functions.spatial.convert.ST_AsWKT;


class JSON_TO_GEO {
    static ST_GeomFromGeoJSON parser = new ST_GeomFromGeoJSON();
    static ST_AsWKT builder          = new ST_AsWKT();

    static String run(ExaMetadata exa, ExaIterator ctx) throws Exception {
        String  geo_json  = ctx.getString(0);

        try {
            return builder.asWKT(parser.geomFromGeoJSON(geo_json));
        } catch (Exception ex) {
            return null;
        }
    }
}
/

 

 

Usage:

 

SELECT CAST(json_to_geo('{"type":"Point","coordinates":[-48.23456,20.12345]}') AS GEOMETRY(4326));

Response: POINT (-48.23456 20.12345)

 

 

H2GiS jar can be downloaded here: https://repo1.maven.org/maven2/org/orbisgis/h2gis/1.5.0/h2gis-1.5.0.jar

You may also want to add ST_FlipCoordinates, since GeoJSON is lon / lat, but srid 4326 is actually lat / lon.

But if you have h2gis, you'll be able to do pretty much anything natively.

Enjoy.